how can I get key name in json_query oracle-CodePudding

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]

CodePudding user response：

I want to get key name dynamically in JSON_QUERY

You cannot, the SQL JSON functions do not (currently) have any functionality to get the key names from a JSON object.

However, you can do it 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;
/

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}');

Then, 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]

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

db<>fiddle here

CodePudding user response：

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.