Thanks for the quick response, the details are below:
Also, worst case scenario, I can pass along kcolumns.cards (the original array) and then just have golang order them if it’s too difficult. Thanks again!
CB 4.6.2
Query:
SELECT s._id, s.name, s.share, s.members, s.created_at, s.updated_at, ARRAY_AGG(s.cols) AS columns
FROM (
SELECT k._id, k.name, k.share, k.members, k.created_at, k.updated_at,
{ kcolumns._id, kcolumns.name, "cards": IFNULL(ARRAY_AGG({c._id, c.title, c.assignees, c.color, "comments": ARRAY_LENGTH(c.comments)}), []) } AS cols
FROM `timelinr` k USE KEYS "board:13c76911-a346-4e2d-945f-cc58e91f3dae"
LEFT UNNEST k.columns kcolumns
LEFT JOIN timelinr c ON KEYS ARRAY "card:" || cardID FOR cardID IN kcolumns.cards END
GROUP BY k._id, k.name, k.share, k.members, k.created_at, k.updated_at, kcolumns
) AS s
GROUP BY s._id, s.name, s.share, s.members, s.created_at, s.updated_at
Explain (I have no written indexes for this query yet):
[
{
"plan": {
"#operator": "Sequence",
"~children": [
{
"#operator": "KeyScan",
"keys": "\"board:13c76911-a346-4e2d-945f-cc58e91f3dae\""
},
{
"#operator": "Fetch",
"as": "k",
"keyspace": "timelinr",
"namespace": "default"
},
{
"#operator": "Parallel",
"maxParallelism": 1,
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Unnest",
"as": "kcolumns",
"expr": "(`k`.`columns`)",
"outer": true
}
]
}
},
{
"#operator": "Join",
"as": "c",
"keyspace": "timelinr",
"namespace": "default",
"on_keys": "array (\"card:\" || `cardID`) for `cardID` in (`kcolumns`.`cards`) end",
"outer": true
},
{
"#operator": "Parallel",
"maxParallelism": 1,
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "InitialGroup",
"aggregates": [
"array_agg({\"_id\": (`c`.`_id`), \"assignees\": (`c`.`assignees`), \"color\": (`c`.`color`), \"comments\": array_length((`c`.`comments`)), \"title\": (`c`.`title`)})"
],
"group_keys": [
"(`k`.`_id`)",
"(`k`.`name`)",
"(`k`.`share`)",
"(`k`.`members`)",
"(`k`.`created_at`)",
"(`k`.`updated_at`)",
"`kcolumns`"
]
}
]
}
},
{
"#operator": "IntermediateGroup",
"aggregates": [
"array_agg({\"_id\": (`c`.`_id`), \"assignees\": (`c`.`assignees`), \"color\": (`c`.`color`), \"comments\": array_length((`c`.`comments`)), \"title\": (`c`.`title`)})"
],
"group_keys": [
"(`k`.`_id`)",
"(`k`.`name`)",
"(`k`.`share`)",
"(`k`.`members`)",
"(`k`.`created_at`)",
"(`k`.`updated_at`)",
"`kcolumns`"
]
},
{
"#operator": "FinalGroup",
"aggregates": [
"array_agg({\"_id\": (`c`.`_id`), \"assignees\": (`c`.`assignees`), \"color\": (`c`.`color`), \"comments\": array_length((`c`.`comments`)), \"title\": (`c`.`title`)})"
],
"group_keys": [
"(`k`.`_id`)",
"(`k`.`name`)",
"(`k`.`share`)",
"(`k`.`members`)",
"(`k`.`created_at`)",
"(`k`.`updated_at`)",
"`kcolumns`"
]
},
{
"#operator": "Parallel",
"maxParallelism": 1,
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "InitialProject",
"result_terms": [
{
"expr": "(`k`.`_id`)"
},
{
"expr": "(`k`.`name`)"
},
{
"expr": "(`k`.`share`)"
},
{
"expr": "(`k`.`members`)"
},
{
"expr": "(`k`.`created_at`)"
},
{
"expr": "(`k`.`updated_at`)"
},
{
"as": "cols",
"expr": "{\"_id\": (`kcolumns`.`_id`), \"cards\": ifnull(array_agg({\"_id\": (`c`.`_id`), \"assignees\": (`c`.`assignees`), \"color\": (`c`.`color`), \"comments\": array_length((`c`.`comments`)), \"title\": (`c`.`title`)}), []), \"name\": (`kcolumns`.`name`)}"
}
]
},
{
"#operator": "FinalProject"
}
]
}
}
]
},
"text": "SELECT k._id, k.name, k.share, k.members, k.created_at, k.updated_at,\n { kcolumns._id, kcolumns.name, \"cards\": IFNULL(ARRAY_AGG({c._id, c.title, c.assignees, c.color, \"comments\": ARRAY_LENGTH(c.comments)}), []) } AS cols\n FROM `timelinr` k USE KEYS \"board:13c76911-a346-4e2d-945f-cc58e91f3dae\"\n LEFT UNNEST k.columns kcolumns\n LEFT JOIN timelinr c ON KEYS ARRAY \"card:\" || cardID FOR cardID IN kcolumns.cards END\n GROUP BY k._id, k.name, k.share, k.members, k.created_at, k.updated_at, kcolumns"
}
]