3 ms·
Missing from the list: --- 1. json functions: json_build_object, json_agg, etc. Ever want to product a hierarchical result set? { "groups": [
by paulddraper 8y ago
Missing from the list:
---
1. json functions: json_build_object, json_agg, etc.
Ever want to product a hierarchical result set?
{
"groups": [
{
"name": "Beatles",
"users": [
{
"name": "John"
},
{
"name": "Paul"
}
]
},
{
"name": "Stooges",
"users": [
{
"name": "Moe"
}
]
}
]
}
Then
SELECT json_build_object(
'groups', json_agg(
json_build_object(
'name', g.name
'users', coleasce(u.users, '[]'::json)
)
) value
FROM group g
LATERAL LEFT JOIN (
SELECT json_agg(
json_build_object('name', u.name)
) users
FROM "user" u
WHERE g.id = u.group_id
) u ON true
Produce your entire JSON result in SQL with full power of aggregations, joins, etc., extensible to any nesting structure.
At the extreme, you could use the PostgREST extension make an entire REST JSON API with just PostgreSQL.
---
2. With PostgreSQL's record-level security, end users can get their own connections to the database.
You can even have an instant GraphQL server via Postgraphile. [1]
[1] https://github.com/PostgREST/postgrest https://github.com/PostgREST/postgrest
[2] https://www.graphile.org/postgraphile/ https://www.graphile.org/postgraphile/
- sixbrx 8y agoI love this way of getting data out of dbs. Before the json functions existed, I was doing a similar thing with the SQL/XML functions on both Postgres and Oracle (which had practically the same set of functions thanks to the SQL/XML ISO standard). Single round trips to grab all the data needed is awesome.