Question 108742 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

How to get special grouped by, nested json back from a sql oracle query?

The code looks like this:

SELECT 
JSON_OBJECT(
ROOM_LIGHT VALUE
JSON_OBJECT
(
'START' VALUE START,
'ENDE' VALUE ENDE,
'RUN' VALUE RUNNING
)
) 
FROM runtime

WHERE 1 = 1
GROUP BY ROOM_LIGHT;

I want this JSON back:

{
"ROOM_LIGHT": "01": [
{
"START" :"",
"ENDE"  :"",
"RUNNING": ""
},
{
"START" :"",
"ENDE"  :"", 
"RUNNING": ""
}
],
"ROOM_LIGHT": "02": [
{
"START" :"",
"ENDE"  :"",
"RUNNING": ""
},
{
"START" :"",
"ENDE"  :"", 
"RUNNING": ""
}
]
}

My problem is the group by doesn’t work. I get this kind of JSON back:

{ "ROOM_LIGHT 01": {"START": "", "ENDE":"", "RUNNING":""}},
{ "ROOM_LIGHT 01": {"START": "", "ENDE":"", "RUNNING":""}},
{ "ROOM_LIGHT 01": {"START": "", "ENDE":"", "RUNNING":""}}
Tags:

2

Answers


  1. 0

    Your expected output is invalid JSON as you have multiple keys in the same object with the same ROOM_LIGHT key.

    Use JSON_ARRAYAGG:

    SELECT JSON_OBJECT(
         KEY 'ROOM_LIGHT ' || ROOM_LIGHT VALUE JSON_ARRAYAGG(
           JSON_OBJECT(
             KEY 'START' VALUE "START",
             KEY 'ENDE'  VALUE ENDE,
             KEY 'RUN'   VALUE RUNNING
           )
         )
       ) As json
FROM   runtime
GROUP BY ROOM_LIGHT;

    Which, for the sample data:

    CREATE TABLE runtime (
  room_light VARCHAR2(20),
  "START"    VARCHAR2(20),
  ende       VARCHAR2(20),
  running    VARCHAR2(20)
);

INSERT INTO runtime (room_light, "START", ende, running)
SELECT '01', 'abc', 'def', 'Y' FROM DUAL UNION ALL
SELECT '01', 'ghi', 'jkl', 'Y' FROM DUAL UNION ALL
SELECT '01', 'mno', 'pqr', 'N' FROM DUAL UNION ALL
SELECT '02', 'stu', 'vwx', 'Y' FROM DUAL;

    Note: START is a reserved word and cannot be used as an identifier unless you use a quoted identifier.

    Outputs:

    JSON
    {"ROOM_LIGHT 01":[{"START":"abc","ENDE":"def","RUN":"Y"},{"START":"mno","ENDE":"pqr","RUN":"N"},{"START":"ghi","ENDE":"jkl","RUN":"Y"}]}
    {"ROOM_LIGHT 02":[{"START":"stu","ENDE":"vwx","RUN":"Y"}]}

    fiddle

    Update

    If you are having problems with the size of the string then use RETURNING CLOB to return a CLOB, instead of a VARCHAR2:

    SELECT JSON_OBJECT(
         KEY 'ROOM_LIGHT ' || ROOM_LIGHT VALUE JSON_ARRAYAGG(
           JSON_OBJECT(
             KEY 'START' VALUE "START",
             KEY 'ENDE'  VALUE ENDE,
             KEY 'RUN'   VALUE RUNNING
             RETURNING CLOB
           )
           RETURNING CLOB
         )
         RETURNING CLOB
       ) As json
FROM   runtime
GROUP BY ROOM_LIGHT;

    fiddle

    Login or Signup to reply.
  2. 0

    You will have to use JSON_ARRAYAGG to group elements into one array :

    SELECT ROOM_LIGHT, 
        JSON_OBJECT(
          'ROOM_LIGHT : ' || ROOM_LIGHT value
          JSON_ARRAYAGG(
           JSON_OBJECT (
             'START' VALUE STARTE,
             'ENDE' VALUE ENDE,
             'RUNE' VALUE RUNNING
           )
          )
         ) as json
FROM runtime
GROUP BY ROOM_LIGHT;

    Result :

    ROOM_LIGHT  JSON
01          {"ROOM_LIGHT : 01":[{"START":10,"ENDE":4,"RUNE":5},{"START":10,"ENDE":14,"RUNE":5}]}
02          {"ROOM_LIGHT : 02":[{"START":10,"ENDE":4,"RUNE":5}]}

    Demo here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search