5 ms·
> Can you write an SQL query to return arbitrary JSON? Returning queried data in a nested form is a must nowadays. Yes. https://www.sqlite.org/json1.html http
by ElectricalUnion 4y ago
> Can you write an SQL query to return arbitrary JSON? Returning queried data in a nested form is a must nowadays.
Yes. https://www.sqlite.org/json1.html https://www.sqlite.org/json1.html
- TheaomBen 4y agoAlso https://www.postgresql.org/docs/current/functions-json.html https://www.postgresql.org/docs/current/functions-json.html
- tomnipotent 4y agoWith just a splash of row_to_json and json_agg, you can JSON encode your entire query in PG. SELECT json_agg(row_to_json(t)) FROM information_schema.tables as t;
- eurasiantiger 4y agoThe dealbreaker here is having to do this dance for every nested field on every entity, and having to write a separate backend query for each separate front-end use-case for a single entity. It just isn’t feasible to write everything out when the schema has >30 entities, some with N:M relations, and when queries routinely go several levels deep to fetch dozens of joined fields at every level. The boilerplate overhead is too much. A natively GraphQL database makes such queries a magnitude less verbose to write out, and all the queries can stay in the frontend (or can become persisted queries on a GraphQL ”middle-end” server).
- tomnipotent 4y agoHasura, a GraphQL server, uses exactly this technique to transform final query results into JSON in PG before bringing back into local memory.