Parsing JSON with Oracle SQL without knowing incoming field names

CodingDuchess
September 18, 2024
143 views
0 votes
2 Answers

I need to parse JSON using Oracle SQL select. Here is my JSON:

{"result":[
  {
   "field_name1":{"display_value":"TEST1","link":"https://test1.com","value":"abcd"},
   "field_name2":{"display_value":"","link":"","value":""},
   "field_name3":{"display_value":"TEST3","link":"https://test3.com","value":"efgh"},
   "field_name4":{"display_value":"TEST4","link":"https://test3.com","value":"ijkl"}
  }
]}

I want to get results as follows:

field_name1:abcd
field_name3:efgh    
field_name4:ijkl

excluding those that have nulls in the value.

How an I accomplish that? I tried using JSON_TABLE but was not able to because I do not know the column names ahead of time.

Answers

You can use:

WITH FUNCTION get_key(
    pos  IN PLS_INTEGER,
    json IN CLOB
  ) RETURN VARCHAR2 
  AS
    doc_keys JSON_KEY_LIST;
  BEGIN
    doc_keys := JSON_OBJECT_T.PARSE ( json ).GET_KEYS;
    RETURN doc_keys( pos );
  END get_key;
SELECT j.result_no,
       GET_KEY(j.key_no, j.result) AS key,
       j.display_value,
       j.link,
       j.value
FROM   table_name t
       CROSS APPLY JSON_TABLE(
         t.data,
         '$.result[*]'
         COLUMNS
           result_no     FOR ORDINALITY,
           result        CLOB FORMAT JSON PATH '$',
           NESTED PATH '$.*' COLUMNS (
             key_no        FOR ORDINALITY,
             display_value VARCHAR2(20)  PATH '$.display_value',
             link          VARCHAR2(250) PATH '$.link',
             value         VARCHAR2(20)  PATH '$.value'
           )
       ) j

Which, for the sample data:

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

INSERT INTO table_name (data)
VALUES (
  '{"result":[
  {
   "field_name1":{"display_value":"TEST1","link":"https://test1.com","value":"abcd"},
   "field_name2":{"display_value":"","link":"","value":""},
   "field_name3":{"display_value":"TEST3","link":"https://test3.com","value":"efgh"},
   "field_name4":{"display_value":"TEST4","link":"https://test3.com","value":"ijkl"}
  }
]}'
)

Outputs:

RESULT_NO	KEY	DISPLAY_VALUE	LINK	VALUE
1	field_name1	TEST1	https://test1.com	abcd
1	field_name2	null	null	null
1	field_name3	TEST3	https://test3.com	efgh
1	field_name4	TEST4	https://test3.com	ijkl

fiddle

For small data set, the solution with the get_key function is fine, but be aware that it also means parsing the whole JSON once per possible key.
If you want to parse it a minimum of times , you would need to use json_dataguide to generate the paths:

select ord, replace(replace(path, '$.result.', ''),'.value','') as name, 
    replace(path, '$.result.', '$.') as pth
from json_table(
        (select json_dataguide(js) from table_name), 
        '$[*]?(@."o:path" eq_regex ".*\.value$")'
        columns(
            ord for ordinality,
            pth varchar2(128) path '$."o:path"'
        )
    )

returns:

1   field_name1 $.field_name1.value
2   field_name2 $.field_name2.value
3   field_name3 $.field_name3.value
4   field_name4 $.field_name4.value

Then use the results to generate a SQL that will use json_table, e.g. here to get the results as simple strings "key:value":

select 
    q'~select object || ':' || value as res from (
    select t.* from table_name d,
    json_table(d.js,
        '$.result[0]'
        columns (
    ~' || 
    listagg(n || ' pth ' || '''' || path || '''', ',') within group(order by ord) 
    ||
    q'~)
    ) t
)
unpivot (
    value for object in (~' || 
    listagg(name || ' as ''' || lower(name) || '''', ',') within group(order by ord) ||
    ')
)'
as sql  
from paths

Then eventually pass the SQL string to a table function that will fetch the data using a dynamic cursor and return the result in the format you want, that may need the definition of an OBJECT if you want columns.
Then the original JSON will be parsed only 2 times per "result[0]" to parse.
This way requires a little bit more work to implement, but may pay off on large data set (lot of "result" rows each one with lot of "fieldname").

Example to fetch results as simple string:

create or replace function fetch_strings(p_sql IN VARCHAR2)
    RETURN SYS.ODCIVARCHAR2LIST
IS
    v_strings SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST() ;
    v_curs sys_refcursor;
begin
    open v_curs for p_sql;
    fetch v_curs bulk collect into v_strings;
    CLOSE v_curs;
    RETURN v_strings;
end ;
/

Please signup or login to give your own answer.

Click here to cancel reply.