Flatten arrays of string

Question

I have the following document

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

And I'm trying to retrieve a list of ids found in emailInteraction and/or voiceInteraction, I'm currently doing the following

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

My issue is that it returns this :

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

and it bothers me because I need a plain array of strings and not an array of array of strings, because I need to use this list of ids in a where clause in another query with something along the lines of :

WHERE lst.interactionId IN (SELECT raw ARRAY_UNION(IFMISSINGORNULL(emailInteractions, []), IFMISSINGORNULL(voiceInteractions, [])) AS ids
FROM `agent-activities`
WHERE agentId = $agentId)

FYI my main query is this :

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

I could use some help to either flatten my array of array into an array somehow or find a better solution than this where clause with the 'property in subquery'

Answer 1

WITH interactionIds AS ( SELECT DISTINCT RAW u
                         FROM `agent-activities` AS a
                         UNNEST ARRAY_CONCAT(IFMISSINGORNULL(a.emailInteractions, []), IFMISSINGORNULL(a.voiceInteractions, [])) AS u
                         WHERE a.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 interactionIds
ORDER BY startDate ASC
LIMIT $limit
OFFSET $offset;

Also you can use ARRAY_DISTINCT(ARRAY_FLATTEN((subquery),1))

NOTE: In function argument you need () around subquery treat it as expression