How can I select a nested json object with a group by clause in oracle sql from a table?

Let’s say I have the following statement:

WITH t AS
(
  SELECT 'A' AS level_0, 'A1' AS level_1_1, 'object_1' AS level_1_2, 'A11' AS level_2_1, 'B11' AS level_2_2 FROM dual
  UNION ALL
  SELECT 'A' AS level_0, 'A1' AS level_1_1, 'object_1' AS level_1_2, 'A12' AS level_2_1, 'B12' AS level_2_2 FROM dual
  UNION ALL
  SELECT 'A' AS level_0, 'A1' AS level_1_1, 'object_1' AS level_1_2, 'A13' AS level_2_1, 'B13' AS level_2_2 FROM dual
  UNION ALL
  SELECT 'A' AS level_0, 'A1' AS level_1_1, 'object_1' AS level_1_2, 'A14' AS level_2_1, 'B14' AS level_2_2 FROM dual
  UNION ALL
  SELECT 'B' AS level_0, 'A1' AS level_1_1, 'object_2' AS level_1_2, 'A11' AS level_2_1, 'B15' AS level_2_2 FROM dual
  UNION ALL
  SELECT 'B' AS level_0, 'A1' AS level_1_1, 'object_2' AS level_1_2, 'A12' AS level_2_1, 'B16' AS level_2_2 FROM dual
  UNION ALL
  SELECT 'B' AS level_0, 'A1' AS level_1_1, 'object_2' AS level_1_2, 'A13' AS level_2_1, 'B17' AS level_2_2 FROM dual
)
SELECT * FROM t

My desired output looks like this:

+---------+---------------------------------------------------------------------------------+
| Level 0 |                                      JSON                                       |
+---------+---------------------------------------------------------------------------------+
| A       | {"level_1_1":"A1","object_1":{"A11":"B11","A12":"B12","A13":"B13","A14":"B14"}} |
| B       | {"level_1_1":"A1","object_2":{"A11":"B15","A12":"B16","A13":"B17"}}             |
+---------+---------------------------------------------------------------------------------+

How do I get this output with a select statement?

Thank you very much for your help!

Answer

JSON_OBJECT() and JSON_OBJECTAGG() functions might be used with proper grouping such as

SELECT level_0,
       JSON_OBJECT(
                   'level_1_1' VALUE level_1_1,
                    level_1_2  VALUE JSON_OBJECTAGG(level_2_1 VALUE level_2_2)
        )
    AS "Result JSON"                           
  FROM t
 GROUP BY level_1_2, level_1_1, level_0

Demo

Advertisement

Answer