How to get the structure of json element?-CodePudding

does a function exist (or how to create such a function) that take a json element and return the structure.

For instance, I would like a function f that in this case :

SELECT f(json_Array (json_object ('a' VALUE 1,json_Array (b valuejson_object ('a' VALUE 1)))= FROM DUAL;

returns [a integer, b [ a integer]] or somethings equivalent

CodePudding user response：

From Oracle 12.2, You can use JSON_DATAGUIDE:

WITH table_name (value) AS (
  SELECT json_Array(
           json_object (
             'a' VALUE 1,
             'b' VALUE json_Array(
                         json_object ('a' VALUE 1),
                         'abcd',
                         123
                       )
           )
         )
  FROM   DUAL
)
SELECT j.path,
       j.type
FROM   table_name t
       CROSS JOIN LATERAL(
         SELECT JSON_DATAGUIDE(t.value) AS data
         FROM   DUAL
       ) d
       CROSS JOIN LATERAL(
         SELECT *
         FROM   JSON_TABLE(
                  d.data,
                  '$[*]'
                  COLUMNS(
                    path VARCHAR2(200) PATH '$."o:path"',
                    type VARCHAR2(200) PATH '$.type',
                    len  INTEGER       PATH '$."o:length"'
                  )
                )
       ) j;

Which outputs:

PATH TYPE

$ array

$.a number

$.b array

$.b[*] string

$.b.a number

PATH	TYPE
$	array
$.a	number
$.b	array
$.b[*]	string
$.b.a	number

If you want something more detailed then you are probably going to have to write your own JSON parser in PL/SQL (or Java and compile it in the database).

db<>fiddle here