0

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"}
Improve this question

2 Answers 2

Reset to default
1

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

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

The thing is that i get multiple strings in this format 'something' AS [key.name] and with this, i need to dynamically build the select statement. I can change the string itself to be eg. KEY 'key.name' IS 'something' but it will still be in one string so i cannot have parts of the string in different parts of the query if that makes sense.
Where do you get those string from? It might be helpful to edit the question to show your raw data and how you get it - whether that is user input somewhere, or data from a table, etc.
Tnx. I've updated it with the input file. The thing is I cannot hardcode this "left join y on y.id = x.id group by x.id, x.j_key, x.j_value" as the key names are always different. But this "'x' value json_object(x.j_key value x.j_value)," is something i can do.
If the input file is a text file with lines like "'abc' AS [x.test1], 'cde' AS [y.test2], 'fgh' AS [y.test3]" then it means the whole process is designed to work with SQLServer and nothing else, so you should think about a more universal syntax to describe the target if you want to support different RDBMS. ORACLE JSON functions are less automatic than SQLServer ones but let you do things in another way and mainly you have he possibility for the key to be defined by column values and sometimes this allows to avoid going to dynamic SQL, so take advantage of it.
If i build the input file to be like this do you know if its possible to group the output by key? KEY 'x' IS json_object(KEY 'test1' IS 'abc'), KEY 'y' IS json_object(KEY 'test2' IS 'cde'), KEY 'y' IS json_object(KEY 'test3' IS 'fgh')
0 
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"}}
Improve this answer

5 Comments

The CTE are just to convert the input "file" "lines" into rows we can use for the JSON generation, once we got that the conversion into JSON is trivial.
That looks great, tnx! Do you think it's possible to output one-liner from your code like this {"x":{"test1":"abc"},"y":{"test2":"cde","test3":"fgh"}} ?
Change the latest query by SELECT LISTAGG( js, ',') WITHIN GROUP(ORDER BY pitem) FROM ( SELECT s.pitem, 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 ) ;
Your query works ok if you have static values like this 'abc' AS [x.test1]. If you put here real statements instead of 'abc' it does not work. For example WITH data (expr) AS ( SELECT q'~COUNT * AS [x.test1]~' FROM test_table )
The "expr" should follow the syntax given in your original post : ("single-quoted string" "AS" "[" "path" "]" [,])+, if you change the rules of the game you have to change the parsing... the objective is to reach the output of the "spaths" CTE, (mainly triples val, lvl, pitem) then the latest query give the solution.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.