How can i combine 2 query's and still access my dockey

SQL++
1

Wondering if there is a way to get rid of an extra query. In my case i have a gui which allows the user to drag rows which i capture. The col definitions are stored in a doc of type grid_info which have columns array of object and each object has a unique id. So for now i do this, get the grid_info key which has this column object

SELECT META(c).id,
       c.name,
       col
FROM Contacts c
UNNEST c.columns AS col
WHERE c._type = 'grid_info'
    AND col.colId = 'b8ea0173-10a0-410a-a753-3f008ba64baa'

Then i get all column’s from the grid_info doc based on dockey returned

SELECT col
FROM Contacts c USE KEYS "grid_info::1b048ec2-130f-44a6-88b8-d7b54a690325"
UNNEST c.columns AS col

The query i came up with that works somewhat is below, except i still will need the meta()id / dockey but its since it is in a subquery not sure how to access it.

SELECT RAW col
FROM Contacts c USE KEYS (
    SELECT RAW META(c).id
    FROM Contacts c
    UNNEST c.columns AS col
    WHERE c._type = 'grid_info'
        AND col.colId = 'b8ea0173-10a0-410a-a753-3f008ba64baa')
UNNEST c.columns AS col
ORDER BY col.position

if i use SELECT meta(c).id, col i get an array of objects of col and each has the meta(c).id in it , i want the Dockey and then an Array of Objects. Now if i use the below query i get the desired output but can no longer sort the column based on position

SELECT META(c).id,columns
FROM Contacts c USE KEYS (
    SELECT RAW META(c).id
    FROM Contacts c
    UNNEST c.columns AS col
    WHERE c._type = 'grid_info'
        AND col.colId = 'b8ea0173-10a0-410a-a753-3f008ba64baa')
2 
SELECT META(c).id,
    (SELECT RAW col FROM c.columns AS col ORDER BY col.position) AS columns
FROM Contacts AS c
WHERE _type = "grid_info"
      AND ANY v IN columns SATISFIES v.colId = "b8ea0173-10a0-410a-a753-3f008ba64baa" END;

OR

WITH dockey AS (SELECT RAW META(c1).id
                FROM Contacts AS c1
                WHERE c1._type = "grid_info"
                AND ANY v IN c1.columns SATISFIES v.colId = "b8ea0173-10a0-410a-a753-3f008ba64baa" END)
SELECT META(c).id,
       (SELECT RAW col FROM c.columns AS col ORDER BY col.position) AS columns
FROM Contacts c USE KEYS dockey;

OR

SELECT META(c).id,
    (SELECT RAW col FROM c.columns AS col ORDER BY col.position) AS columns
FROM Contacts AS c1
JOIN Contacts c ON KEYS META(c1).id
WHERE c1._type = "grid_info"
      AND ANY v IN c1.columns SATISFIES v.colId = "b8ea0173-10a0-410a-a753-3f008ba64baa" END;
3

Thanks, example 1 and 3 work, 2 does not return any data. In any case they all seem to be speed wise around the same, is there a benefit using one vs the other ?

4

Fixed issue with 2, Use Option 1 that is simple one document get