Query json dictionary data in SQL

ghz 1years ago ⋅ 3916 views

Question

My CLOB field in a table contains JSON and looks as following:

{"a":"value1", "b":"value2", "c":"value3"}

And I'm trying to write an SQL query to return a table with key and value fields like following:

key|value
---|------
a  |value1
b  |value2
c  |value3

Any help would be hugely appreciated!

Answer

Use JSON_TABLE and then UNPIVOT if you want the values in rows instead of columns:

SELECT *
FROM   (
  SELECT p.*
  FROM   table_name t
         CROSS JOIN
         JSON_TABLE(
           t.value,
           '$'
           COLUMNS (
             a PATH '$.a',
             b PATH '$.b',
             c PATH '$.c'
           )
         ) p
)
UNPIVOT ( value FOR key IN ( a, b, c ) );

So for some sample data:

CREATE TABLE table_name (
  value CLOB CONSTRAINT ensure_json CHECK (value IS JSON)
);

INSERT INTO table_name ( value ) VALUES ( '{"a":"value1", "b":"value2", "c":"value3"}' );

This outputs:

KEY | VALUE
:-- | :-----
A   | value1
B   | value2
C   | value3

db <>fiddle here

If you want to do it dynamically then you can parse the JSON in PL/SQL and use GET_KEYS to get a collection of key names and then access the correct one by its position and correlate that to the value using FOR ORDINALITY:

CREATE 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;
/

Then:

SELECT get_key( j.pos, t.value ) AS key,
       j.value
FROM   table_name t
       CROSS APPLY JSON_TABLE(
         t.value,
         '$.*'
         COLUMNS (
           pos   FOR ORDINALITY,
           value PATH '$'
         )
       ) j;

Outputs:

KEY | VALUE
:-- | :-----
a   | value1
b   | value2
c   | value3

db <>fiddle here

#oracle