Get values from selected JsonArray in PL SQL

Question

I've got the following Query in PL SQL

SELECT  
    JSON_ARRAY(
    '[
    {
        "role_id": "TEST1",
        "role_name": "Для тестів 1"
    },
    {
        "role_id": "TEST3",
        "role_name": "Для тестів 3"
    }
]') l
FROM dual

The output of this Query :

["[\n {\n "role_id": "TEST1",\n "role_name": "Для тестів 1"\n },\n {\n "role_id": "TEST3",\n
"role_name": "Для тестів 3"\n }\n]"]

My concern is how to get all values from the current JsonArray in the Upper Query by applying external query? Need to get all "role_id" in an external query.

Thanks in advance.

Answer 1

You want a relational view of the data in a json object. That can be achieved using the function JSON_TABLE, it doesn't matter if the json object is an array or not.

with json_tab AS
(
SELECT
'[
    {
        "role_id": "TEST1",
        "role_name": "Для тестів 1"
    },
    {
        "role_id": "TEST3",
        "role_name": "Для тестів 3"
    }
]' as data FROM DUAL
)
SELECT role_id, role_name
  FROM json_tab t,
       JSON_TABLE(
       data,
       '$[*]'
         columns (
           role_id VARCHAR2(100) PATH '$.role_id',
           role_name VARCHAR2(100) PATH '$.role_name'
         )
       ) jt; 

ROLE_ID                        ROLE_NAME            
------------------------------ ---------------------
TEST1                          Для тестів 1         
TEST3                          Для тестів 3