SQL Tip: JSONB_AGG in PostgreSQL for simple one-to-many joins

#1

Post by geekuillaume » Fri, Aug 03, 2018, 12:49 PM UTC

SQL Tip: JSONB_AGG in PostgreSQL for simple one-to-many joins
medium.com

Re: SQL Tip: JSONB_AGG in PostgreSQL for simple one-to-many joins

#2

Post by zykran » Fri, Aug 03, 2018, 12:56 PM UTC

Nice !

Re: SQL Tip: JSONB_AGG in PostgreSQL for simple one-to-many joins

#3

Post by sven_n » Fri, Aug 03, 2018, 8:19 PM UTC

I'm doing this even more extreme... querying a whole complex object graph based on an entity framework core model :) https://github.com/MUnique/OpenMU/blob/master/src/Persistenc...

Re: SQL Tip: JSONB_AGG in PostgreSQL for simple one-to-many joins

#4

Post by tbrugz » Sat, Aug 04, 2018, 3:17 PM UTC

Oracle has cursor expressions that returns a resultset inside a cell from the parent resultset. The whole resultset could then be serialized to json, xml or whatever

I wish there was an equivalent function in PostgreSQL. I've even asked a question about that on stackoverflow:

https://stackoverflow.com/questions/8637050/cursor-inside-sq...

Re: SQL Tip: JSONB_AGG in PostgreSQL for simple one-to-many joins

#5

Post by havermeyer » Sat, Aug 04, 2018, 4:13 PM UTC

BigQuery provides a TO_JSON_STRING function (https://cloud.google.com/bigquery/docs/reference/standard-sq...) that serves a similar purpose, e.g.:

    SELECT
      id,
      TO_JSON_STRING(ARRAY_AGG(item)) AS json
    FROM T
    GROUP BY id

The `item` can be a scalar type expression or a struct containing multiple fields, for example, so you can build JSON from structured data.

As a disclosure, I work on the BigQuery team.