Join + Selection

SQL++
1

I’m having trouble getting the output below based on the following documents that I have. I would like to join the Queries document against several types of documents (workspace, folder, collection) and include the name from each. Any help would be appreciated.

Queries document:

{
      "queries": [
        {
          "filters": [
            {
              "type": "collection",
              "value": "1641c9ec-50f6-4f47-a35a-a31c51dd23a2"
            },
            {
              "type": "metadata",
              "name": "CreatorTool:Capture One 7 Macintosh",
              "value": "CreatorTool:Capture One 7 Macintosh"
            },
            {
              "type": "workspace",
              "value": "3865"
            },
            {
              "type": "folder",
              "value": "5bf72f0a-9034-4901-be3c-ac586155d985"
            }
          ]
        }
      ]
    }

Workspace document:

    {
        "type": "workspace",
        "id": "3865",
        "name": "name"
    }

Collection document

{
    "type": "collection",
    "id": "1641c9ec-50f6-4f47-a35a-a31c51dd23a2",
    "name": "name"

}

Folder document:

{
    "type": "folder",
    "id": "5bf72f0a-9034-4901-be3c-ac586155d985",
    "name": "name"
}

I would like the output to be:

{
“queries”: [
{
“filters”: [
{
“type”: “collection”,
“value”: “1641c9ec-50f6-4f47-a35a-a31c51dd23a2”,
“name”: “name”
},
{
“type”: “metadata”,
“name”: “CreatorTool:Capture One 7 Macintosh”,
“value”: “CreatorTool:Capture One 7 Macintosh”
},
{
“type”: “workspace”,
“value”: “3865”,
“name”: “name”
},
{
“type”: “folder”,
“value”: “5bf72f0a-9034-4901-be3c-ac586155d985”,
“name”: “name”
}
]
}
]
}

2 
INSERT INTO default VALUES ("f01", { "queries": [ { "filters": [ { "type": "collection", "value": "1641c9ec-50f6-4f47-a35a-a31c51dd23a2" }, { "type": "metadata", "name": "CreatorTool:Capture One 7 Macintosh", "value": "CreatorTool:Capture One 7 Macintosh" }, { "type": "workspace", "value": "3865" }, { "type": "folder", "value": "5bf72f0a-9034-4901-be3c-ac586155d985" } ] } ] });
INSERT INTO default VALUES ("3865", { "type": "workspace", "id": "3865", "name": "name" });
INSERT INTO default VALUES ("1641c9ec-50f6-4f47-a35a-a31c51dd23a2", { "type": "collection", "id": "1641c9ec-50f6-4f47-a35a-a31c51dd23a2", "name": "name" });
INSERT INTO default VALUES ("5bf72f0a-9034-4901-be3c-ac586155d985", { "type": "folder", "id": "5bf72f0a-9034-4901-be3c-ac586155d985", "name": "name" });

SELECT d.*,
       (SELECT q.*,
               (SELECT f.*, v.*
                FROM q.filters AS f
                LET v = (SELECT d1.* FROM default AS d1 USE KEYS f.`value`)[0]
                ) AS filters
        FROM d.queries AS q
       ) AS queries
FROM default AS d;
3

Thank you! that works great