Building a Subquery Without USE KEYS

SQL++
1

Here are what my documents look like:

+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| parent1: {type: "parent", email: "parent1@email.com", childPk: "child::123"}                                                                                        |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| "child::123": {type: "child", name: "sampleChild1"}                                                                                                                 |
+----------------------------------------------------------------------------------+----------------------------------------------------------------------------------+
|grandchild1: {type: "grandchild", someKey1: "someValue1", parentPk: "child::123"} | grandchild2: {type: "grandchild", someKey2: "someValue2", parentPk: "child::123"}|
+----------------------------------------------------------------------------------+----------------------------------------------------------------------------------+

+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| parent2: {type: "parent", email: "parent2@email.com", childPk: "child::456"}                                                                                      |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| "child::456": {type: "child", name: "sampleChild2"}                                                                                                               |
+---------------------------------------------------------------------------------+---------------------------------------------------------------------------------+
|grandchild1: {type: "grandchild", someKey: "someValue1", parentPk: "child::456"} | grandchild2: {type: "grandchild", someKey: "someValue2"parentPk:   "child::456"}|
+---------------------------------------------------------------------------------+---------------------------------------------------------------------------------+

I want my result to look like this:

[
    {
        email: "parent1@email.com",
        name: "sampleChild1",
        children: [
            "someValue1",
            "someValue2"
        ]
    },
    {
        email: "parent2@email.com",
        name: "sampleChild2",
        children: [
            "someValue1",
            "someValue2"
        ]
    }
]

Here’s my query so far:

SELECT p.email,
       c.name,
       g.someKey
FROM app p
    JOIN app g ON p.childPk = g.parentPk
    AND g.type = 'grandchild' LEFT
    JOIN app c ON META(c).id = p.childPk
WHERE p.type ='parent'

which results in

[
    {
        email: "parent1@email.com",
        name: "sampleChild1",
        someKey: "someValue1"
    },
    {
        email: "parent1@email.com",
        name: "sampleChild1",
        someKey: "someValue2"
    },
    {
        email: "parent2@email.com",
        name: "sampleChild2",
        someKey: "someValue1"
    }
    {
        email: "parent2@email.com",
        name: "sampleChild2",
        someKey: "someValue2"
    }
]

When I try adding a subquery such as LET children = (SELECT g.someKey FROM app g WHERE g.type = "grandchild" AND g.childPk = p.childPk)
I get this error: “Unable to run subquery - cause: FROM in correlated subquery must have USE KEYS clause: FROM app.”

How can I keep the grandchildren values inside the child object?

2

It is restriction MB-30813. You need to wait 7.0.0 release. In mean time try the following

SELECT d.email, d.name, ARRAY_AGG(g.someKey) AS children
FROM (SELECT p.childPk AS id , p.email, c.name 
      FROM app AS p 
      LEFT JOIN app AS c  ON KEYS p.childPk
      WHERE p.type = "parent") AS d
LEFT JOIN app AS g ON d.id = g.parentPk AND g.type = "grandchild"
GROUP BY d;

OR

SELECT p.email, c.name, ARRAY_AGG(g.someKey) AS children
FROM app AS p
LEFT JOIN app AS c ON p.childPk = META(c).id AND c.type = "child"
LEFT JOIN app AS g ON p.childPk = g.parentPk AND g.type = "grandchild"
WHERE p.type = "parent"
GROUP BY p.email, c.name;