Parsing JSON with Oracle SQL without knowing incoming field names

Question

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.

Answer 1

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