4 ms·
it 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.
by Nathanba 2y ago
it 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