5 ms·

It’s pretty cool how Hasura uses/abuses Postgres JSON support to be able to run many queries in the same “command”

• tpetry 5y ago
  Can you elaborate on what they are doing?
  • simonw 5y ago
    I can take a guess. PostgreSQL's JSON tooling makes it much easier to build SQL queries that return different shaped data from different tables in a single query. The row_to_json() function for example turns an entire PostgreSQL row into a JSON object. Here's a query I built that uses that to return results from two different tables, via some CTEs and a UNION ALL: https://simonwillison.net/dashboard/row-to-json/ https://simonwillison.net/dashboard/row-to-json/ with quotations as ( select 'quotation' as type, created, row_to_json(blog_quotation) as row from blog_quotation ), blogmarks as ( select 'blogmark' as type, created, row_to_json(blog_blogmark) as row from blog_blogmark ), combined as ( select * from quotations union all select * from blogmarks ) select * from combined order by created desc limit 100 Even more interesting is what you can do with json_agg - it lets you combine results from other tables. Here's a demo that solves the classic problem of needing to include data from a table at the end of a many-to-many relationship (in this case the tags on entries on my blog): https://simonwillison.net/dashboard/json-agg-demo/ https://simonwillison.net/dashboard/json-agg-demo/ select blog_entry.id, title, slug, created, json_agg(json_build_object(blog_tag.id, blog_tag.tag)) from blog_entry join blog_entry_tags on blog_entry.id = blog_entry_tags.entry_id join blog_tag on blog_entry_tags.tag_id = blog_tag.id group by blog_entry.id order by blog_entry.created desc limit 10 (Both these demos use https://django-sql-dashboard.datasette.io/ https://django-sql-dashboard.datasette.io/ )
    • tehlike 5y ago
      this. JSON Agg fixes the cartesian product problem. Imagine having a table with large rows, and another table to join with small data but many rows. Normally you'd do a inner join of some sort, and the data from "large rows" would be duplicated many many times - json_agg simply fixes this. you can actually do the full table without json_build_object, too you can do something like select json_agg(t.) from ( select from table ) as t this makes joining multiple tables together very very easy, and performant.
    • jamil7 5y ago
      I rewrote a really slow geojson query using this technique recently, it’s very powerful and performant but would say that it trades off some readability.
  • deleted 5y ago
    [deleted]
• dmitryminkovsky 5y ago
  Also lateral joins https://stackoverflow.com/questions/28550679/what-is-the-difference-between-lateral-join-and-a-subquery-in-postgresql https://stackoverflow.com/questions/28550679/what-is-the-dif...
• dkubb 5y ago
  I think the next generation of ORMs will be built on top of this approach. Being able to query and receive an entire object graph in a single round trip has changed how we develop apps at my company.
  • 1st1 5y ago
    Shameless plug: this is what we do in EdgeDB.
• tehlike 5y ago
  json_agg approach to prevent cartesian-product problem is AMAZING.