Is it possible to use multiple UNNEST in one query?

Question

I'm trying to write a Couchbase query to get result from my JSON given below. I am using UNNEST operation to but somehow not getting expected result.

Can we use multiple UNNEST in one query? If so, then what is problem with my query?

Input JSON like below,

{
 "country": "India",
 "id": "1111",
 "value": {
 "isDefault": false,
 "schedules": {
 "default": {
 "startTime": null,
 "flightConfigs": {
 "1M": {
 "1": {
 "airlineid": "airline_5201",
 "destinationairport": "SFO",
 "name": "United Airlines",
 "sourceairport": "ABQ"
 },
 "2": {
 "airlineid": "airline_5202",
 "destinationairport": "SFO",
 "name": "United Airlines",
 "sourceairport": "ACV"
 }
 }
 },
 "2M": {
 "1": {
 "airlineid": "airline_5203",
 "destinationairport": "SFO",
 "name": "United Airlines",
 "sourceairport": "ABQ"
 },
 "2": {
 "airlineid": "airline_5204",
 "destinationairport": "SFO",
 "name": "United Airlines",
 "sourceairport": "ACV"
 }
 }
 }
 }
 }
}

I am expected output as below:

[
 {
 "id": "1111",
 "schedule": "default",
 "month": "1M" 
 },
 {
 "id": "1111",
 "schedule": "default",
 "month": "2M"
 }
]

This is the quest I'm trying to use, but I'm not getting th expected result:

select data.id as id, sched_n as schedule, month as month
from AIR_CONFIG.SCHEDULE.Flight_Schedule data
unnest OBJECT_NAMES(data.`value`.schedules) as sched_n
unnest OBJECT_INNER_VALUES(data.`value`.schedules) as sched
unnest OBJECT_NAMES(sched.flightConfigs) as month
where data.id like "1111"

Can someone please help on same.

Thanks

• Matthew Groves: The JSON you've pasted isn't valid, so it's difficult to work out the query without guessing. Could you please update with a valid JSON value?

Answer 1

Instead of getting Names and Value in separate UNNSET we can read using UNNEST OBJECT_PAIRS

select data.id as id, sched_n.name as schedule, month.name as month from
AIR_CONFIG.SCHEDULE.Flight_Schedule data
UNNEST OBJECT_PAIRS(data.value.schedules) as sched_n
UNNEST OBJECT_PAIRS(sched.flightConfigs) as month 
where data.id like "1111"

Answer 2

SELECT d.id AS id, u.*
FROM AIR_CONFIG.SCHEDULE.Flight_Schedule AS d
UNNEST ARRAY_FLATTENS((ARRAY (ARRAY {"schedule":sn, "month":fcn}
 FOR fcn:fcv IN sv.flightConfigs END)
 FOR sn:sv IN d.`value`.schedules END),1) AS u
WHERE d.id LIKE "1111";

OR

Different representation and more efficient

SELECT d.id AS id,
 ARRAY_FLATTENS((ARRAY (ARRAY {"schedule":sn, "month":fcn}
 FOR fcn:fcv IN sv.flightConfigs END)
 FOR sn:sv IN d.`value`.schedules END),1) AS schedules
FROM AIR_CONFIG.SCHEDULE.Flight_Schedule AS d
WHERE d.id LIKE "1111";