Sure, the SQL generated for that query is below
As a side-note, it seems like "multisetQuery()" is close to being able to
do this
but it doesn't preserve the column names =(
SELECT
coalesce(json_agg("root"), '[]') AS "root"
FROM
(
SELECT
row_to_json(
(
SELECT
"_5_e"
FROM
(
SELECT
"_0_root.base"."id" AS "id",
"_0_root.base"."email" AS "email",
"_4_root.ar.root.todos"."todos" AS "todos"
) AS "_5_e"
)
) AS "root"
FROM
(
SELECT
*
FROM
"public"."user"
WHERE
('true')
) AS "_0_root.base"
LEFT OUTER JOIN LATERAL (
SELECT
coalesce(json_agg("todos"), '[]') AS "todos"
FROM
(
SELECT
row_to_json(
(
SELECT
"_2_e"
FROM
(
SELECT
"_1_root.ar.root.todos.base"."description" AS
"description"
) AS "_2_e"
)
) AS "todos"
FROM
(
SELECT
*
FROM
"public"."todo"
WHERE
(("_0_root.base"."id") = ("user_id"))
) AS "_1_root.ar.root.todos.base"
) AS "_3_root.ar.root.todos"
) AS "_4_root.ar.root.todos" ON ('true')
) AS "_6_root"
On Tue, Feb 8, 2022 at 5:01 PM James Starr <[email protected]> wrote:
> Could you translate it to SQL, I am not familiar with expression queries
> like that.
>
> On Tue, Feb 8, 2022 at 1:46 PM Gavin Ray <[email protected]> wrote:
>
> > I am trying to work out the RelBuilder calls to make to generate
> > expressions for nested queries
> >
> > Given some query like this:
> >
> > query {
> > user(where: { id: { _gt: 5 } }) {
> > name
> > todos {
> > text
> > }
> > }
> > }
> >
> > I need to return a single row, which is an array of JSON objects, like:
> > [{ name: "Person1", todos: [{ text: "P1 Todo 1"}, { text: "P1 Todo 2" }]
> }]
> >
> > Hasura uses COALESCE(json_agg("alias"), '[]') and row_to_json() functions
> > FWIW:
> > https://pastebin.com/zb77zK5S
> >
> > I see that Calcite has JSON_OBJECT/JSON_ARRAY functions
> > Are these what I want to use?
> >
> > Maybe it would be easier to make individual parallel queries and generate
> > the object in-memory?
> > Would that be slower? (I would guess so)
> >
> > Any advice greatly appreciated. Hurting my head trying to sort out the
> > RelBuilder calls here, ha.
> >
>
