能否在Couchbase单查询中使用多个UNNEST？查询未达预期求排查

问题描述 #

尝试编写Couchbase查询从给定JSON数据中获取结果，使用UNNEST操作未得到预期输出，需确认单个查询是否支持多个UNNEST，并排查现有查询的问题。

输入JSON #

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

预期输出 #

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

原查询语句 #

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"

错误分析 #

1. 独立UNNEST导致笛卡尔积：分别对OBJECT_NAMES和OBJECT_INNER_VALUES执行UNNEST，会生成两组数据的笛卡尔积。若schedules包含多个条目，会产生错误的键值组合，而非一一对应的schedule名称和内容。
2. 遗漏部分月份数据：原查询仅从sched.flightConfigs中提取月份名称，但输入JSON中2M是直接挂在schedule对象（default）下的属性，未被纳入查询范围，导致结果缺失该条目。

正确查询语句 #

SELECT data.id AS id, sched_pair.name AS schedule, month AS month
FROM AIR_CONFIG.SCHEDULE.Flight_Schedule data
UNNEST OBJECT_PAIRS(data.`value`.schedules) AS sched_pair
UNNEST ARRAY_CONCAT(
    -- 提取flightConfigs中的月份
    OBJECT_NAMES(sched_pair.value.flightConfigs),
    -- 提取schedule对象下直接挂载的月份（排除非月份属性）
    ARRAY k FOR k IN OBJECT_NAMES(sched_pair.value) WHERE k NOT IN ["startTime", "flightConfigs"] END
) AS month
WHERE data.id = "1111"

说明 #

• 使用OBJECT_PAIRS一次性获取schedules的键（schedule名称）和对应值，确保键值一一对应，避免笛卡尔积。
• 通过ARRAY_CONCAT合并两部分月份数据：flightConfigs下的1M，以及schedule对象下直接存在的2M，再UNNEST合并后的数组得到所有目标月份。
• 用WHERE k NOT IN ["startTime", "flightConfigs"]过滤掉schedule对象中的非月份属性，只保留目标月份条目。

内容的提问来源于stack exchange，提问作者Bhushan Phalak