N1QL查询返回嵌套数组，如何展平为一维数组用于IN子句？

我有agent-activities文档结构如下，包含emailInteractions和voiceInteractions两个字符串数组字段：

{
  "agentId": "agent2",
  "date": "2022-08-30",
  "metaData": {
    "documentCreationDate": "2022-08-30T15:49:21Z",
    "documentVersion": "1.0",
    "expiry": 1662479361
  },
  "emailInteractions": [
    "0c99ea2a-c235-4c5a-a0bd-aeffba559bca",
    "12846a9d-7cc1-4755-b527-cd8aee9d2de4"
  ],
  "voiceInteractions": [
    "1c99ea2a-c235-4c5a-a0bd-aeffba559bca",
    "22846a9d-7cc1-4755-b527-cd8aee9d2de4"
  ]
}

尝试用以下N1QL查询两个数组的并集，结果返回嵌套数组（数组的数组），无法直接在主查询的IN子句中使用：

SELECT ARRAY_UNION(IFMISSINGORNULL(emailInteractions, []), IFMISSINGORNULL(voiceInteractions, [])) AS ids
FROM `agent-activities`
WHERE agentId = "agent2"

返回结果：

[
  [
    "12846a9d-7cc1-4755-b527-cd8aee9d2de4",
    "22846a9d-7cc1-4755-b527-cd8aee9d2de4",
    "0c99ea2a-c235-4c5a-a0bd-aeffba559bca",
    "1c99ea2a-c235-4c5a-a0bd-aeffba559bca"
  ]
]

我的主查询是合并voice-interactions和email-interactions的数据，需要根据指定agentId过滤对应的interactionId，当前写法因子查询返回嵌套数组导致IN子句无法正确匹配，需要将嵌套数组展平为一维数组，或优化WHERE IN子句写法：

SELECT lst.*
FROM (
    SELECT iv.id AS interventionId,
           vi.direction,
           vi.channel,
           vi.startDate AS startDate,
           vi.id AS interactionId,
           vi.customerProfileId
    FROM `voice-interactions` AS vi
    UNNEST vi.distributions AS dv
    UNNEST dv.interventions AS iv
    UNION
    SELECT ie.id AS interventionId,
           ei.direction,
           ei.channel,
           ei.startDate AS startDate,
           ei.id AS interactionId,
           ei.customerProfileId
    FROM `email-interactions` AS ei
    UNNEST ei.distributions AS de
    UNNEST de.interventions AS ie) AS lst
WHERE lst.interactionId IN (
SELECT raw ARRAY_UNION(IFMISSINGORNULL(emailInteractions, []), IFMISSINGORNULL(voiceInteractions, [])) AS ids
FROM `agent-activities`
WHERE agentId = $agentId)
ORDER BY startDate ASC
LIMIT $limit
OFFSET $offset

解决方案 #

方法一：展平嵌套数组，让子查询返回一维数组 #

在子查询中用UNNEST展开ARRAY_UNION生成的数组，配合RAW返回单个值而非数组，这样IN子句就能正确匹配：

SELECT RAW id
FROM `agent-activities`
UNNEST ARRAY_UNION(IFMISSINGORNULL(emailInteractions, []), IFMISSINGORNULL(voiceInteractions, [])) AS id
WHERE agentId = $agentId

将其代入主查询后，完整语句如下：

SELECT lst.*
FROM (
    SELECT iv.id AS interventionId,
           vi.direction,
           vi.channel,
           vi.startDate AS startDate,
           vi.id AS interactionId,
           vi.customerProfileId
    FROM `voice-interactions` AS vi
    UNNEST vi.distributions AS dv
    UNNEST dv.interventions AS iv
    UNION
    SELECT ie.id AS interventionId,
           ei.direction,
           ei.channel,
           ei.startDate AS startDate,
           ei.id AS interactionId,
           ei.customerProfileId
    FROM `email-interactions` AS ei
    UNNEST ei.distributions AS de
    UNNEST de.interventions AS ie) AS lst
WHERE lst.interactionId IN (
    SELECT RAW id
    FROM `agent-activities`
    UNNEST ARRAY_UNION(IFMISSINGORNULL(emailInteractions, []), IFMISSINGORNULL(voiceInteractions, [])) AS id
    WHERE agentId = $agentId)
ORDER BY startDate ASC
LIMIT $limit
OFFSET $offset

方法二：使用JOIN代替IN子句，优化性能 #

如果数据量较大，JOIN通常比IN子句性能更优。可以将agent-activities的数组展开后与主查询结果进行匹配：

SELECT lst.*
FROM (
    SELECT iv.id AS interventionId,
           vi.direction,
           vi.channel,
           vi.startDate AS startDate,
           vi.id AS interactionId,
           vi.customerProfileId
    FROM `voice-interactions` AS vi
    UNNEST vi.distributions AS dv
    UNNEST dv.interventions AS iv
    UNION
    SELECT ie.id AS interventionId,
           ei.direction,
           ei.channel,
           ei.startDate AS startDate,
           ei.id AS interactionId,
           ei.customerProfileId
    FROM `email-interactions` AS ei
    UNNEST ei.distributions AS de
    UNNEST de.interventions AS ie) AS lst
JOIN `agent-activities` aa
UNNEST ARRAY_UNION(IFMISSINGORNULL(aa.emailInteractions, []), IFMISSINGORNULL(aa.voiceInteractions, [])) AS interactionId
WHERE aa.agentId = $agentId AND lst.interactionId = interactionId
ORDER BY startDate ASC
LIMIT $limit
OFFSET $offset

也可以直接通过数组包含关系做JOIN，逻辑更简洁：

SELECT lst.*
FROM (
    SELECT iv.id AS interventionId,
           vi.direction,
           vi.channel,
           vi.startDate AS startDate,
           vi.id AS interactionId,
           vi.customerProfileId
    FROM `voice-interactions` AS vi
    UNNEST vi.distributions AS dv
    UNNEST dv.interventions AS iv
    UNION
    SELECT ie.id AS interventionId,
           ei.direction,
           ei.channel,
           ei.startDate AS startDate,
           ei.id AS interactionId,
           ei.customerProfileId
    FROM `email-interactions` AS ei
    UNNEST ei.distributions AS de
    UNNEST de.interventions AS ie) AS lst
JOIN `agent-activities` aa
ON lst.interactionId IN ARRAY_UNION(IFMISSINGORNULL(aa.emailInteractions, []), IFMISSINGORNULL(aa.voiceInteractions, []))
WHERE aa.agentId = $agentId
ORDER BY startDate ASC
LIMIT $limit
OFFSET $offset

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