Oracle JSON output group by key-CodePudding

I generate SQL statements dynamically from the input file and I want to have the output in JSON format grouped by a key which I provide in an alias in the select statement.

The input file comes from another system and it looks like this:

'abc' AS [x.test1],
'cde' AS [y.test2],
'fgh' AS [y.test3]

In SQL Server I have a working query like this:

SELECT
    (SELECT 
    'abc' AS [x.test1],
    'cde' AS [y.test2],
    'fgh' AS [y.test3]
    FROM "dbo"."TEST"
    FOR JSON PATH,
    WITHOUT_ARRAY_WRAPPER
) AS RESULT

It returns this output which is grouped by key and this is working perfectly:

{"x":{"test1":"abc"},"y":{"test2":"cde","test3":"fgh"}}

I want to achieve the same output with oracle.

Currently, I got to here:

SELECT 
( 
    SELECT json_object(
            KEY '[x.test1]' IS 'abc',
            KEY '[y.test2]' IS 'cde',
            KEY '[y.test3]' IS 'fgh'
        )
    FROM test
) 
AS RESULT from DUAL;

Problem is that this doesn't group my output by the key:

{"[x.test1]":"abc","[y.test2]":"cde","[y.test3]":"fgh"}

CodePudding user response：

You could nest json_object() calls:

SELECT json_object(
  KEY 'x' IS json_object(
    KEY 'test1' IS 'abc'
  ),
  KEY 'y' IS json_object(
    KEY 'test2' IS 'cde',
    KEY 'test3' IS 'fgh'
    )
) 
AS RESULT from DUAL;

RESULT
{"x":{"test1":"abc"},"y":{"test2":"cde","test3":"fgh"}}

fiddle

Or as you refer to grouping, if your data is really coming from tables, you could use json_objectagg() and the table data, with something like:

select json_object(
  'x' value json_object(x.j_key value x.j_value),
  'y' value json_objectagg(y.j_key, y.j_value)
) as result
from x
left join y on y.id = x.id
group by x.id, x.j_key, x.j_value

RESULT
{"x":{"test1":"abc"},"y":{"test2":"cde","test3":"fgh"}}

fiddle

CodePudding user response：

WITH data (expr) AS (
    SELECT q'~'abc' AS [x.test1],'cde' AS [y.test2],'fgh' AS [y.test3]~' FROM DUAL 
),
rdata(expr) AS (
    SELECT regexp_substr(expr,'[^,] ',1,LEVEL) AS expr
    FROM data
    CONNECT BY regexp_substr(expr,'[^,] ',1,LEVEL) IS NOT NULL
), 
exprs AS (
    SELECT expr, regexp_substr(expr, q'~'(.*)'~', 1, 1, 'i', 1) as val, 
        regexp_substr(expr, q'~\[(.*)\]~', 1, 1, 'i', 1) as path 
    FROM rdata 
),
spaths AS (
    SELECT e.*, LEVEL as lvl, regexp_substr(path,'[^\.] ',1,LEVEL) as pitem
    FROM exprs e
    CONNECT BY regexp_substr(path,'[^\.] ',1,LEVEL) IS NOT NULL AND prior val = val AND PRIOR sys_guid() IS NOT NULL
)
SELECT json_object(
    s.pitem VALUE json_objectagg(
        p.pitem VALUE p.val
    )
) AS js
FROM spaths s
    JOIN spaths p ON s.val = p.val AND p.lvl = 2
WHERE s.lvl = 1
GROUP BY s.pitem
;


JS
{"x":{"test1":"abc"}}
{"y":{"test2":"cde","test3":"fgh"}}