How To Convert Sql To Json

The basic row-to-object mapping

A table row becomes a JSON object; a result set becomes an array of objects:

NULL vs missing keys

Column names become keys. Column values become typed JSON scalars. Easy for flat tables.

Type fidelity

SQL has types JSON doesn’t:

Joins — flat vs nested

A join returns a flat row. JSON often wants nested:

Postgres — json_agg, row_to_json, jsonb_build_object

Flat is fine for rows that will render as a table. Nested is what APIs usually want. To produce nested, either:

MySQL 5.7+ / MariaDB — JSON_OBJECT, JSON_ARRAYAGG

Postgres has excellent JSON support:

SQLite 3.38+ — json_object, json_group_array

Same shape as Postgres. MySQL 8+ is the sweet spot — 5.7 JSON functions exist but are slower and have more quirks.

SQL Server — FOR JSON

SQLite’s functions are a bit newer but fully functional. Perfect for local tools and embedded apps.

Streaming large result sets

Building JSON in memory fails at scale. For a 10M-row dump:

Quoting and escaping

If you handroll SQL-to-JSON in application code, make sure the serializer handles:

Schema evolution

Don’t build JSON with string concatenation. Use your language’s JSON library or the DB’s JSON functions.

Common mistakes

JSON outputs are part of your API contract. Adding columns is safe (extra keys don’t break well-written consumers). Renaming and dropping aren’t safe — version the output or add a compatibility layer.

Run the numbers