6 ms·
SQL Tip: JSONB_AGG in PostgreSQL for simple one-to-many joins
- zykran 8y agoNice !
- sven_n 8y agoI'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/Persistence/EntityFramework/Json/JsonQueryBuilder.cs https://github.com/MUnique/OpenMU/blob/master/src/Persistenc...
- tbrugz 8y agoOracle 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-sql-query https://stackoverflow.com/questions/8637050/cursor-inside-sq...
- havermeyer 8y agoBigQuery provides a TO_JSON_STRING function (https://cloud.google.com/bigquery/docs/reference/standard-sql/json_functions#to_json_string 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.