Help build nested queries

[patdx]

Similar to this Postgres example: https://www.pgcasts.com/episodes/generating-json-from-sql

We can build a nested JSON response in sqlite something like this:

select json_object(
  'users',
  (
    select json_group_array(
      json_object('id', id, 'name', name, 'email', email, 'bookmarks', bookmarks)
    )
    from (
      select
        id,
        name,
        email,
        (
          select
            json_group_array(
              json_object(
                'id',
                id,
                'user_id',
                user_id,
                'name',
                name,
                'url',
                url
              )
            )
          from
            (
              select
                id,
                user_id,
                name,
                url
              from
                bookmarks
              where
                user_id = users.id
            )
        ) as bookmarks
      from
        users
    )
  )
) as json_result

The result will look like:

{
  "users": [
    {
      "id": 1,
      "name": "John",
      "email": "[email protected]",
      "bookmarks": [
        {
          "id": null,
          "user_id": 1,
          "name": "Hashrocket",
          "url": "https://www.hashrocket.com"
        },
        {
          "id": null,
          "user_id": 1,
          "name": "PostgreSQL Docs",
          "url": "http://www.postgresql.org/docs/current/static/index.html"
        }
      ]
    },
    {
      "id": 2,
      "name": "Jane",
      "email": "[email protected]",
      "bookmarks": [
        {
          "id": null,
          "user_id": 2,
          "name": "Google",
          "url": "https://www.google.com"
        },
        {
          "id": null,
          "user_id": 2,
          "name": "Stack Overflow",
          "url": "http://stackoverflow.com/"
        },
        {
          "id": null,
          "user_id": 2,
          "name": "YouTube",
          "url": "https://www.youtube.com"
        }
      ]
    }
  ]
}

1. How can I facilitate building this query in the UI?
2. Is this efficient/correct?

[patdx]

I've tried moving the subquery into a CTE but that seems to cause the "json" to turn into a regular string.
Interestingly it seems that CTEs can be defined at any level or moved around, even if the "variables" are not exactly in scope at that level.

So it may be better to approach in a non-cte way.

Other ideas.

https://www.reddit.com/r/graphql/comments/gni473/checkout_the_postgres_sql_that_super_graph/
https://github.com/danielrearden/sqlmancer

Also the Vlcn project had an interesting nested json query experiment in the source code.