A table has a bunch of fields, none of them unique, most of them repeat many times, some fields need to be extracted individually and some in groups, and no more than three elements in any JSON Array. Let's say we have company Tuna Hunters, with boats Mary and Celeste, and it goes on trips in several fishing zones, with varying number of crew and guests, and catches different fish. The table looks like this:
https://i.imgur.com/uoWF92A.jpg
I want to extract no more than 3 distinct boats, no more than 3 distinct zone/crew/guests triplets, and no more than 3 distinct fish species. My JSON should look like this:
{"Tuna Hunters":
{"vessel":["Mary","Celeste"],
"trip":[
{"zone":1,"crew":2,"guests":2},
{"zone":2,"crew":2,"guests":1},
{"zone":1,"crew":3,"guests":1}
],
"species":["Tuna","Swordfish","Sawfish"]
}
}
Getting individual elements of the JSON is easy; an array can be acquired like this:
SELECT json_object ('vessel' VALUE json_arrayagg(boat_name) returning clob )
from (select boat_name
from (select boat_name from trips
WHERE company = 'Tuna Hunters'
group by boat_name )
where rownum < 4
);
and an array of triplets can be acquired like this:
SELECT json_object ('trip' VALUE json_arrayagg(
json_object ('zone' VALUE zone,
'crew' VALUE crew,
'guests' VALUE guests)
) returning clob )
from (select zone, crew, guests
from (select zone, crew, guests
from trips WHERE company = 'Tuna Hunters'
group by zone, crew, guests )
where rownum < 4);
However I am at a loss how to generate the entire desired JSON without elements repeating. When I try something like this:
SELECT json_object ('Tuna Hunter' VALUE
json_object ('vessel' VALUE
json_arrayagg( boat_name ),
'trip' VALUE
json_arrayagg(
json_object ('zone'
VALUE zone,'crew'
VALUE crew,'guests'
VALUE guests)
)
returning clob )
FROM (select boat_name, zone, crew, guests from
(select boat_name, zone, crew, guests from trips
WHERE company = 'Tuna Hunters'
group by boat_name,zone, crew, guests ) where rownum < 4);
I end up with this:
{"Tuna Hunters":
{"vessel":["Mary","Mary","Celeste"],
"trip":[
{"zone":1,"crew":2,"guests":2},
{"zone":2,"crew":2,"guests":1},
{"zone":1,"crew":3,"guests":1}
]
}
}
because GROUP BY clause generates Mary-1-2-2, Mary-2-2-1 and Celeste-1-3-1. Even worse if first three distinct trips are all on the same boat: then first JSON Array becomes "vessel":["Mary","Mary","Mary"]
Any suggestions how to construct the JSON, short of concatenating individual strings?