Generate Nested JSON oracle19c using JSON functions

Question

Hi I have below sample query.In real case scenario there are many more columns in each view. The A view has unique ids and is present in other views. I tried to join all the views and generate JSON using JSON_OBJECT and JSON_ARRAYAGG but the join is causing duplicates in my final JSON. I tried creating separate JSON array and was thinking of merging but could not make it work. Below is the sample CTE for the data. Basically the 3 views are JSON_OBJECT inside ARRAY. If there is no data in B,C,D views for the matching ID in A then there will be no JSON object for that view example 301 is not present in D view.

WITH A AS(SELECT 101 AS ID ,DATE '2024-01-03' AS dt FROM dual UNION
          SELECT 201 AS ID ,DATE '2024-03-13' AS dt FROM dual UNION 
          SELECT 301 AS ID ,DATE '2024-05-23' AS dt FROM dual ),
          
    B AS (SELECT 101 AS ID, 'ABC' AS typ, 10 AS price FROM dual UNION
          SELECT 101 AS ID, 'XYZ' AS typ, 20 AS price FROM dual UNION
          SELECT 101 AS ID, 'LMY' AS typ, 40 AS price FROM dual UNION
          SELECT 201 AS ID, 'PQR' AS typ, 30 AS price FROM dual UNION
          SELECT 301 AS ID, 'MNP' AS typ, 10 AS price FROM dual ),
          
    C AS (SELECT 101 AS ID, 'NY' AS place FROM dual UNION
          SELECT 101 AS ID, 'NJ' AS Place FROM dual UNION
          SELECT 201 AS ID, 'PA' AS Place FROM dual UNION
          SELECT 301 AS ID, 'VT' AS Place FROM dual UNION
          SELECT 301 AS ID, 'MT' AS Place FROM dual),
          
    D AS (SELECT 101 AS ID, 'BLACK' AS color FROM dual UNION
          SELECT 101 AS ID, 'WHITE' AS color FROM dual UNION
          SELECT 201 AS ID, 'PINK' AS color FROM dual UNION
          SELECT 201 AS ID, 'GREEN' AS color FROM dual

expected output:

[
    {
        "id": "101",
        "dt": "2024-01-03 12:00:00",
        "types": [
            {
                "typ": "ABC",
                "price": 10
            },
            {
                "typ": "XYZ",
                "price": 20
            },
            {
                "typ": "LMY",
                "price": 40
            }
        ],
        "events": [
            {
                "place": "NY"
            },
            {
                "place": "NJ"
            }
        ],
        "pattern": [
            {
                "color": "BLACK"
            },
            {
                "color": "WHITE"
            }
        ]
    },
    {
        "id": "201",
        "dt": "2024-03-13 12:00:00",
        "types": [
            {
                "typ": "PQR",
                "price": 30
            }
        ],
        "events": [
            {
                "place": "PA"
            }
        ],
        "pattern": [
            {
                "color": "PINK"
            },
            {
                "color": "GREEN"
            }
        ]
    },
    {
        "id": "301",
        "dt": "2024-05-23 12:00:00",
        "types": [
            {
                "typ": "MNP",
                "price": 10
            }
        ],
        "events": [
            {
                "place": "VT"
            },
            {
                "place": "MT"
            }
        ]
    }
]

p3consulting · Accepted Answer · 2024-06-08 11:15:50Z

Try this one:

select 
    json_arrayagg(
        json_object( 
            key 'id' is to_char(a.id),
            key 'dt' is a.dt+0.5,
            key 'types' is
            (select json_arrayagg(
                    json_object( 
                        key 'typ' is b.typ,
                        key 'price' is b.price
                    )
                )
                from b where b.id = a.id
            ) ,
            key 'events' is
            (select json_arrayagg(
                    json_object( 
                        key 'place' is c.place
                    )
                )
                from c where c.id = a.id
            ),
            key 'pattern' is
            (select json_arrayagg(
                    json_object( 
                        key 'color' is d.color
                    )
                )
                from d where d.id = a.id
            ) absent on null
        )
    ) as js
from a
;



 [
  {
    "id": "101",
    "dt": "2024-01-03T12:00:00",
    "types": [
      {
        "typ": "ABC",
        "price": 10
      },
      {
        "typ": "LMY",
        "price": 40
      },
      {
        "typ": "XYZ",
        "price": 20
      }
    ],
    "events": [
      {
        "place": "NJ"
      },
      {
        "place": "NY"
      }
    ],
    "pattern": [
      {
        "color": "BLACK"
      },
      {
        "color": "WHITE"
      }
    ]
  },
  {
    "id": "201",
    "dt": "2024-03-13T12:00:00",
    "types": [
      {
        "typ": "PQR",
        "price": 30
      }
    ],
    "events": [
      {
        "place": "PA"
      }
    ],
    "pattern": [
      {
        "color": "GREEN"
      },
      {
        "color": "PINK"
      }
    ]
  },
  {
    "id": "301",
    "dt": "2024-05-23T12:00:00",
    "types": [
      {
        "typ": "MNP",
        "price": 10
      }
    ],
    "events": [
      {
        "place": "MT"
      },
      {
        "place": "VT"
      }
    ]
  }
]

Thank you so much, it worked great! Sorry for the late reply.

Collectives™ on Stack Overflow

Generate Nested JSON oracle19c using JSON functions

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related