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"
}
]
}
]
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"
}
]
}
]