3 ms·
These days there are other tricks you can use to turn several SQL queries into a single round-trip too, with things like JSON aggregates. Here's an example Pos
by simonw 2y ago
These days there are other tricks you can use to turn several SQL queries into a single round-trip too, with things like JSON aggregates.
Here's an example PostgreSQL query that returns 10 rows from one table and 20 rows from another table in a single network round-trip, using JSON serialization to return the different shaped rows in one go: https://simonwillison.net/dashboard/union-json-demo/ https://simonwillison.net/dashboard/union-json-demo/
Related trick: https://til.simonwillison.net/sqlite/related-rows-single-query https://til.simonwillison.net/sqlite/related-rows-single-que...
- Nathanba 2y agoit is very interesting, I have a similar blog post in my bookmarks as well: https://www.crunchydata.com/blog/generating-json-directly-from-postgres https://www.crunchydata.com/blog/generating-json-directly-fr... the problem is that it only works with Postgres, not mysql or sqlite or pretty much anything else (at least not as conveniently) and the bigger problem is that the queries become more complex
- simonw 2y agoIt works with SQLite too - the functions have different names (json_group_array() and json_object() rather than PostgreSQL's json_agg() and json_build_object()) but they work pretty much exactly the same. The one JSON PostgeSQL feature that SQLite doesn't have yet and I miss is row_to_json(record) which serializes an entire row without you needing to specifically list the column names. I've not tried this stuff in MySQL myself yet but it looks like JSON_ARRAYAGG() might be the equivalent there: https://dev.mysql.com/doc/refman/8.4/en/aggregate-functions.html#function_json-arrayagg https://dev.mysql.com/doc/refman/8.4/en/aggregate-functions....
- Nathanba 2y agoyes unfortunately only postgres has that convenient thing where you don't have to go out of your way to specify all the column names. SQLServer has it too by a different name but not MySQL