0

I want to get key name dynamically in JSON_QUERY

SELECT JSON_QUERY('{a:100, b:200, c:300}', '$.*' WITH WRAPPER) AS value
  FROM DUAL;

VALUE
--------------------------------------------------------------------------------
[100,200,300]

Expected key list like:

[a,b,c]
MT0
  • 143,790
  • 11
  • 59
  • 117
samurai-jack07
  • 29
  • 1
  • 9

2 Answers2

2

I want to get key name dynamically in JSON_QUERY

As pointed out by @mathguy, from Oracle 12.2, you can use JSON_DATAGUIDE. To get it in your format, you can use:

SELECT t.value,
       '['
       || LISTAGG(SUBSTR(path, 3), ',') WITHIN GROUP(ORDER BY ROWNUM) 
       || ']'
         AS keys
FROM   table_name t
       CROSS JOIN LATERAL(
         SELECT JSON_DATAGUIDE(t.value) AS data
         FROM   DUAL
       ) d
       CROSS JOIN LATERAL(
         SELECT path
         FROM   JSON_TABLE(
                  d.data,
                  '$[*]'
                  COLUMNS(
                    path VARCHAR2(20) PATH '$."o:path"'
                  )
                )
       )
GROUP BY t.value;

Which, for the sample data:

CREATE TABLE table_name ( value VARCHAR2(4000) CHECK (value IS JSON) );
INSERT INTO table_name (value) VALUES ('{a:100, b:200, c:300}');

Outputs:

VALUE KEYS
{a:100, b:200, c:300} [a,b,c]

However, you can do a bit simpler using PL/SQL JSON objects in a function (from my previous answer):

CREATE FUNCTION get_keys(
  value IN CLOB
) RETURN SYS.ODCIVARCHAR2LIST PIPELINED
IS
  js   JSON_OBJECT_T := JSON_OBJECT_T( value );
  keys JSON_KEY_LIST;
BEGIN
  keys := js.get_keys();
  FOR i in 1 .. keys.COUNT LOOP
    PIPE ROW ( keys(i) );
  END LOOP;
END;
/

For the sample data above, you can use the function in the query:

SELECT t.value,
       k.keys
FROM   table_name t
       CROSS JOIN LATERAL (
         SELECT '['
                || LISTAGG(COLUMN_VALUE, ',') WITHIN GROUP ( ORDER BY ROWNUM)
                || ']'
                  AS keys
         FROM   TABLE(get_keys(t.value))
       ) k;

Which outputs:

VALUE KEYS
{a:100, b:200, c:300} [a,b,c]

db<>fiddle here

MT0
  • 143,790
  • 11
  • 59
  • 117
0

Why do you want to get key names dynamically in JSON_QUERY? That makes no sense.

You want to get key names dynamically. That is the problem statement. "In JSON_QUERY" is your idea for how to get the keys. That idea is wrong.

But you can get the key names dynamically, and much more, with the JSON_DATAGUIDE function. Then you can process the output further, if needed; just JSON_DATAGUIDE itself gives you much more information: key names, but also data types and lengths - and it works for complex JSON strings too (with nested objects and arrays).

select json_dataguide('{a:100, b:200, c:300}')
from   dual;

JSON_DATAGUIDE('{A:100,B:200,C:300}')                                           
--------------------------------------------------------------------------------
[{"o:path":"$.a","type":"number","o:length":4},{"o:path":"$.b","type":"number","
o:length":4},{"o:path":"$.c","type":"number","o:length":4}]

Of course, if you just want an array of the top-level keys, you can further process this JSON array; I am not showing how, since you may be able to do that yourself (trivial manipulations), but do ask for help if needed.

Note that the above works in Oracle 12.2 and higher. Since Oracle 18 you can use additional options to format the output, if needed.