Select records based on keys from field in another specific document

I need to select a number of users based on a specific project where these users are “field assistants”. I know the specific key of the project (Project:12) and the field that contains the user ids (array fieldassistants) like this snippet:

  "projecttype": "1",
  "fieldassistants": [
    "587CE5200641ABD9C1257E500051DDCD",
    "BA171123846CEBF1C1257CB2002DA330",
    "3174791F0DDBA0BDC125863B0048C083"
  ],
  "key": "12",

How can I specify that I want to find the users based on the values in that field?

I have tried something like this:

SELECT t1.name, t1.`key`,t1.email,
FROM data AS t1
WHERE t1.type='User' 
AND t1.`key` in 
(select p.fieldassistants from data p where p.type='Project' and p.`key` = '12')

This doesn’t work - and also seems over complicated. The thing is that the key to the user doc. is built like this: User: 3174791F0DDBA0BDC125863B0048C083 (i.e. using the key from the “fieldassistants” field).

I feel this could be done with very fast lookups using the direct keys but have failed to find a way…

EDIT:
Just tried to use RAW to get a “real” array, but it seems to be nested one level too much and I don’t seem to be able to “flatten” it:

select raw p.fieldassistants from data p where p.type='Project' and p.`key` = '12'

Result:

[
  [
    "587CE5200641ABD9C1257E500051DDCD",
    "BA171123846CEBF1C1257CB2002DA330",
    "3174791F0DDBA0BDC125863B0048C083"
  ]
]

If I try:

select raw array_flatten(p.fieldassistants,1) from data p where p.type='Project' and p.`key` = '12'

it gives the same result…

EDIT 2:
Ok, so following the idea above I found a workaround:

WHERE t1.type='User' AND t1.`key` in (select raw p.fieldassistants from data p where p.type='Project' and p.`key` = '12')[0]

I found this by manually putting the result with the nested arrays above into the original query. Not elegant - but it seems to work :slight_smile: :innocent:

Comments for improvements are appreciated!


WITH fassistants AS (ARRAY_FLATTEN((SELECT RAW p.fieldassistants
                                    FROM data AS p
                                    WHERE p.type='Project' AND p.`key` = '12'),1))
SELECT t1.name, t1.`key`,t1.email
FROM data AS t1
WHERE t1.type='User'
AND t1.`key` IN  fassistants;

OR

WITH fassistants AS (SELECT RAW uf
                     FROM data AS p
                     UNNEST p.fieldassistants AS uf
                     WHERE p.type='Project' AND p.`key` = '12')
SELECT t1.name, t1.`key`,t1.email
FROM data AS t1
WHERE t1.type='User'
AND t1.`key` IN  fassistants;
1 Like

Clever way of using WITH… Thanks!!