Went to test this query out and found that it can't be performed:
SELECT
JSON_OBJECT(
KEY 'users'
VALUE JSON_ARRAYAGG(
JSON_OBJECT(
KEY 'name' VALUE "users"."name",
KEY 'todos' VALUE JSON_ARRAYAGG(
JSON_OBJECT(
KEY 'description' VALUE "todos"."description"
)
)
)
)
)
FROM
"users"
LEFT OUTER JOIN
"todos" ON "users"."id" = "todos"."user_id";
Checking the source, seems this is a blanket policy, not a
datasource-specific thing.
>From a functional perspective, it doesn't feel like it's much different
from JOINs
But I don't understand relational theory or DB functionality in the least,
so I'm not fit to judge.
Just curious why Calcite doesn't allow this
