JSON Value - Oracle PL/SQL : Multiple Fields

Question

I have a HCLOB with below sample entry

"relist":[{"name":"XYZ","action":["Manager","Specific User List"],"flag":false}]

When I try to get name or flag using JSON_VALUE I am able to get it as it has single field , but I want to get the value for action . I read that JSON_VALUE only supports 1 entry .

Is there any workaround to get both values of action ?

Regards.

Answer 1

Use JSON_TABLE with a nested path:

SELECT j.*
FROM   table_name t
       CROSS APPLY JSON_TABLE(
         t.data,
         '$.relist[*]'
         COLUMNS (
           name VARCHAR2(20) PATH '$.name',
           flag VARCHAR2(5)  PATH '$.flag',
           NESTED PATH '$.action[*]' COLUMNS(
             action VARCHAR2(20) PATH '$'
           )
         )
       ) j

Which, for the sample data:

CREATE TABLE table_name ( data CLOB CHECK ( data IS JSON ) );

INSERT INTO table_name ( data ) VALUES (
  '{"relist":[{"name":"XYZ","action":["Manager","Specific User List"],"flag":false}]}'
);

Outputs:

NAME | FLAG  | ACTION            
:--- | :---- | :-----------------
XYZ  | false | Manager           
XYZ  | false | Specific User List

Or use the indices of the array to get the actions:

SELECT j.*
FROM   table_name t
       CROSS APPLY JSON_TABLE(
         t.data,
         '$.relist[*]'
         COLUMNS (
           name    VARCHAR2(20) PATH '$.name',
           flag    VARCHAR2(5)  PATH '$.flag',
           action1 VARCHAR2(20) PATH '$.action[0]',
           action2 VARCHAR2(20) PATH '$.action[1]'
         )
       ) j

Which outputs:

NAME | FLAG  | ACTION1 | ACTION2           
:--- | :---- | :------ | :-----------------
XYZ  | false | Manager | Specific User List

db<>fiddle here

Answer 2

The value for "action" is a JSON array. You accepted an answer that returns the scalar values from that array, rather than the array itself (as a single, compound value). Whether that was really your requirement or not (in other words: whether you misstated your question or not), here is how you could answer the problem exactly as you stated it.

As you understood correctly, JSON_VALUE() only returns scalar values from a JSON document. To return JSON fragments, you can use JSON_QUERY(), essentially with the same syntax.

To return the JSON array which is the value of "action" in your document (using MT0's table):

select json_query(data, '$.relist.action') as action
from   table_name
;

ACTION                              
------------------------------------
["Manager","Specific User List"]