Hacker News Reader

Comment by mjrpes | Hacker News Reader

One thing that would be nice is if SQL provided first class support for sub records.

So instead of "SELECT name, (SELECT GROUP_CONCAT(CONCAT_WS(',', post_id, post) SEPARATOR ';') FROM posts p WHERE p.user_id = u.user_id) AS 'posts' FROM users u WHERE u.user_id = 1",

you could do "SELECT name, (SELECT post_id, post FROM posts p WHERE p.user_id = u.user_id) AS 'posts' FROM users u WHERE u.user_id = 1".

and the query result would be { name : 'Todd', posts : [ { post_id : 1, post : 'My Comment' } ] }.

Obviously this is a simple example and could have been rewritten as a query on the posts table, inner joined on the user table, and duplicating the user's name in the result. But it becomes much nicer to have as queries get more complex.

A query that supports sub records would gives you flexibility to structure data like a JSON object and simplify the server end of REST apis.

View on HN

With Postgres:

    SELECT u.name, json_agg(p) AS posts
        FROM users u, posts p
        WHERE p.user_id = u.user_id
        GROUP BY u.user_id;

What if you only want the id and markup of each post?

Postgres 9.4 gave us json_build_object:

    SELECT 
      u.name, 
      array_agg(
        json_build_object(
          'id', p.id,
          'markup', p.markup
        )
      ) posts
    FROM users u, posts p
    WHERE p.user_id = u.user_id
    GROUP BY u.user_id;

Thank you so much for this! I spent a good chunk of today trying to work out how to do this, and had given up concluding that it wasn't possible.

I was playing around with postgres and was able to get a query that puts everything in JSON:

select json_agg(sub) from (select u.username, (select array_agg(p) from posts p where u.id = p.user_id) posts from users u) sub;

Using json_build_object (Postgres 9.4) to map each username to an array of posts:

    SELECT 
      json_build_object(
        u.username,
        (SELECT json_agg(p) FROM posts p WHERE u.id = p.user_id)
      )
    FROM users u

Output:

    [
      {"chuck": null},
      {"blair": [
        {"id": 1, "markup": "hello"},
        {"id": 4, "markup": "world"}
      ]},
      {"serena": [{"id": 5, "markup": "testing"}]}
    ]

At least I think you were trying to do that.

EDIT: Nevermind my last post. Your query makes sense now and would indeed work well.

Glad it helped! :) It honestly took me a while to boil it down to that – the docs for those JSON functions are poorly explained and illustrated, IMO.

If you're just trying to get JSON out (for a simple query to REST api, or in a node.js environment), have you considered converting the record/recordset to json in the subquery?

The json functions in 9.3+ are pretty handy for that sort of thing. Andrew (core developer who wrote most of that functionality) and I decided to keep the api pretty lightweight, as its easy to also add your own functions to suit your needs.

JSON functions do look pretty helpful and go a long way, even if not first class. As you can tell from syntax I'm stuck in the MySQL/MariaDB world, so not up with everything in PostgreSQL. This and the features in the slideshare are an eye opener to me. Thanks!

Postgres/Oracle supports such constructs.

And Revenj has been using it for years: https://github.com/ngs-doo/revenj

And yes, Revenj now comes with an offline compiler ;)

how can you do that with postgres ? a simple example / documentation link ?

https://github.com/ngs-doo/revenj/blob/master/tutorials/reve...

SELECT p, array_agg(SELECT c FROM "MasterDetail"."Child_entity" c WHERE c."parentID" = p."ID") as c FROM "MasterDetail"."Parent_entity" p

Note, just replace array_agg with json_agg to get JSON.

And doing this the query iterates only once over the MasterDetail table ?

This particular query does a nested loop on the detail table, but basically yes. Only one iteration.

Reply on news.ycombinator.com