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
Comments for improvements are appreciated!