Have the database return JSON directly(?)

[patdx]

Probably we can do something like this:

https://www.freecodecamp.org/news/stop-calling-postgrest-magic-8f3e1d5e5dd1/amp/

[patdx]

Here is a more complicated example of json query + nesting in postgres: https://twitter.com/dosco/status/1250886122974130182?s=20

Also here is my attempt at multiple unrelated subqueries with one json query:

https://sqlime.org/#deta:y08kvtwlu1h2

select
  json_object(
    'expenses',
    (
      select
        json_group_array(json_object('year', year, 'month', month, 'income', income))
      from
        (
          select
            *
          from
            expenses
          limit
            3
        )
    ),
    'employees',
    (
      select
        json_group_array(json_object('name', name, 'city', city))
      from
        (
          select
            *
          from
            employees
          limit
            2
        )
    )
  ) as json_result

[patdx]

I created a mini online tool to help generate/experiment with these kinds of queries:

https://github.com/patdx/sqlite-query-tool
https://sqlite-query-tool.pages.dev/