Question 206180 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

Oracle 19C Query with hierarchical JSON

I have 2 Oracle tables

table_1: 
ID   target_id   name   url
1    A           John   example.org
2    B           Jane   helloworld.com

table_2:
id1   target_id   col1   col2
X     A           aaa    bbb
Y     A           ccc    ddd

I am trying to run query joining table_1 and table_2 with target_id, and get JSON similar to followings (using Java)

[{"id": 1, "name"="John", "url": "example.org", 
"target": [{"id1":"X", "col1": ...}, {"id1":"Y", "col1": ...}]
}, 
{"id": 2, "name"="Jane", "url": "helloworld.com"}]

What we are doing right now is to iterate table_1, for each row, select from table_2, and add object into a list, and at the end serilize it to JSON.

Is that a better way instead of making nested sql in the loop in Oracle?

Tags:

2

Answers


  1. 0

    table1:table2 = 1:N ?

    SELECT AA.*, BB.* FROM table_1 AA
LEFT JOIN table_2 BB ON AA.target_id=BB.target_id
ORDER BY AA.id
    Login or Signup to reply.
  2. 0

    Use JSON_ARRAYAGG and JSON_OBJECT:

    SELECT JSON_ARRAYAGG(
         JSON_OBJECT(
           KEY 'id'     VALUE t1.id,
           KEY 'name'   VALUE t1.name,
           KEY 'url'    VALUE t1.url,
           KEY 'target' VALUE t2.target ABSENT ON NULL
         )
       ) AS data
FROM   table_1 t1
       LEFT OUTER JOIN (
         SELECT target_id,
                JSON_ARRAYAGG(
                  JSON_OBJECT(
                    KEY 'id1' VALUE id1,
                    KEY 'col1' VALUE col1,
                    KEY 'col2' VALUE col2
                  )
                ) AS target
         FROM   table_2
         GROUP BY target_id
       ) t2
       ON t1.target_id = t2.target_id

    Which, for the sample data:

    CREATE  TABLE table_1 (ID, target_id, name, url) AS
SELECT 1, 'A', 'John', 'example.org' FROM DUAL UNION ALL
SELECT 2, 'B', 'Jane', 'helloworld.com' FROM DUAL;

CREATE  TABLE table_2 (id1, target_id, col1, col2) AS
SELECT 'X', 'A', 'aaa', 'bbb' FROM DUAL UNION ALL
SELECT 'Y', 'A', 'ccc', 'ddd' FROM DUAL;

    Outputs:

    DATA
    [{"id":1,"name":"John","url":"example.org","target":[{"id1":"X","col1":"aaa","col2":"bbb"},{"id1":"Y","col1":"ccc","col2":"ddd"}]},{"id":2,"name":"Jane","url":"helloworld.com"}]

    fiddle

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

Back To Top
Search