SQL to JSON Converter

SQL to JSON Converter — Bridge Your Relational Data to the API World

Relational databases store data in rows and columns. Modern applications consume data as JSON. Between those two worlds lives a conversion step that developers face constantly: migrating seed data from SQL dumps into API payloads, transforming test fixtures from database exports into frontend-ready JSON, moving records from a legacy SQL database into a MongoDB collection, or building mock API servers from real production data. This tool automates that conversion — paste SQL INSERT statements with a CREATE TABLE definition and get back a properly typed JSON array.

The conversion isn't just wrapping each row in curly braces. The tool extracts column names from the CREATE TABLE statement or the INSERT column list, maps each value to its corresponding key, and applies type inference: strings stay quoted, integers and floats become unquoted numbers, NULL becomes null, and booleans are recognized. The result is an array of objects that a JavaScript JSON.parse() call handles without any post-processing.

That might sound straightforward, and for simple tables it is. But real-world SQL data is messy. Values contain escaped quotes, embedded newlines, binary blobs, and functions that haven't been evaluated yet. Date and timestamp formats vary wildly between databases. NULL handling has edge cases that trip up even experienced developers. And when you start dealing with PostgreSQL's JSON/JSONB columns or MySQL's JSON type, the conversion gets genuinely complex. This guide covers all of it.

Generating Nested JSON Structures from Flat Tables

Relational data is inherently flat — each table stores one entity type, and relationships are expressed through foreign keys. But JSON APIs often expect nested structures. A user object might contain an array of orders, each containing an array of line items. Getting from flat SQL to that nested shape requires a two-step process: convert each table independently, then merge them programmatically.

Here's a concrete example. You have a users table with columns id, name, email and an orders table with columns id, user_id, product, amount. First, convert each table's INSERT statements to JSON. You'll get two arrays: one of user objects and one of order objects. Then, in JavaScript, use a reduce operation to group orders by user_id and attach them to the corresponding user:

const nested = users.map(u => ({...u, orders: orders.filter(o => o.user_id === u.id)}));

This pattern scales to any depth. For a three-level hierarchy — customers, orders, line items — you'd convert three tables and nest them in two passes. The tool handles the flat conversion cleanly; the nesting logic lives in your application code where you have full control over the output shape.

An alternative approach for PostgreSQL users: if your database supports json_build_object and json_agg, you can generate nested JSON directly in SQL. But that requires a live database connection. This tool fills the gap when you're working with SQL dumps or migration files rather than connected databases.

Array Aggregation and Grouping Patterns

One of the most common conversion patterns is turning a one-to-many relationship into an array within each parent object. The SQL equivalent is GROUP BY with aggregation, but SQL itself can't produce JSON arrays natively (unless you're using PostgreSQL's json_agg or MySQL's JSON_ARRAYAGG). This tool gives you the raw material for that transformation.

Consider a tags join table with columns post_id, tag_name. Each post has multiple tags stored as separate rows. After converting to JSON, you get an array of {post_id, tag_name} objects. To group them by post and nest the tags, you'd write something like:

const grouped = tags.reduce((acc, row) => { (acc[row.post_id] ??= []).push(row.tag_name); return acc; }, {});

This grouping pattern appears everywhere: orders with line items, courses with enrolled students, albums with tracks. The flat conversion gives you the normalized data; your application code denormalizes it into the nested shape your API consumers expect.

For very large datasets, consider whether the nested approach is the right one. APIs that return deeply nested objects can force clients to load more data than they need. Sometimes a flat response with separate endpoints for related resources — /users/123 and /users/123/orders — is architecturally cleaner than a deeply nested blob. The conversion tool doesn't enforce any particular shape; it gives you clean JSON and lets you decide.

NULL Handling: The Details That Matter

SQL NULL and JSON null are conceptually similar — both represent the absence of a value — but they behave differently in practice. In SQL, NULL propagates through most operations (comparisons, arithmetic, aggregations). In JavaScript, null and undefined are distinct, and accessing properties on null throws a TypeError.

The converter outputs SQL NULL as JSON null, which is the standard mapping. But there are subtleties. A column declared as NOT NULL DEFAULT '' will never produce NULL — it'll produce an empty string. A column declared as INT NULL might contain NULL or might contain the integer 0; the converter faithfully outputs whichever value appears in the INSERT statement.

One edge case that catches people: NULL in numeric columns. When a column is typed as INT and the value is NULL, the output is "column_name": null — not "column_name": 0 and not the key being omitted. This is the correct behavior. Omitting the key entirely would change the object's shape inconsistently (some rows have the key, others don't). Outputting 0 would silently replace "unknown" with "zero," which are semantically different.

For boolean columns, NULL is particularly important. A three-valued boolean (TRUE, FALSE, NULL) maps to JSON true, false, or null. Code that consumes the JSON needs to check for null explicitly rather than relying on JavaScript's truthiness evaluation, because null is falsy in JavaScript but represents a genuinely different state than false in SQL.

Date and Timestamp Formatting in JSON Output

JSON has no native date type. Dates are represented as strings, and the format varies between applications. ISO 8601 (2024-01-15T14:30:00Z) is the most portable format, but SQL databases store dates in their own representations: MySQL's DATETIME uses 2024-01-15 14:30:00 (no T, no timezone), PostgreSQL's TIMESTAMP includes microsecond precision, and Oracle's default format is completely different.

The converter preserves the date string exactly as it appears in the INSERT statement. If your SQL contains '2024-01-15', the JSON output will be "2024-01-15". If it contains '2024-01-15 14:30:00.123456-05', that full string carries over. This fidelity is deliberate — the converter doesn't attempt to parse or reformat dates because it doesn't know what format your consumer expects.

If you need ISO 8601 formatting in the JSON output, you'll need to either format the dates in your SQL before conversion (using functions like DATE_FORMAT in MySQL or TO_CHAR in PostgreSQL) or post-process the JSON after conversion. For PostgreSQL, the ::timestamptz cast or TO_CHAR(date_column, 'YYYY-MM-DD"T"HH24:MI:SSZ') produces ISO strings that convert cleanly to JSON date representations.

A common mistake is assuming that JavaScript's Date constructor will automatically parse the SQL date format. It usually does for ISO-like formats, but the space-separated MySQL format (2024-01-15 14:30:00) can behave inconsistently across browsers. Safest bet: either ensure your SQL dates are already ISO-formatted, or parse them explicitly in your application code.

PostgreSQL-Specific JSON Functions and Capabilities

PostgreSQL has the richest JSON support of any relational database. The json and jsonb types store JSON natively, with jsonb supporting indexing and efficient querying. When your INSERT statements contain JSON column values — for example, '{"name": "Alice", "prefs": {"theme": "dark"}}' — the converter preserves them as nested JSON objects in the output rather than treating them as plain strings.

PostgreSQL's json_build_object, json_agg, and jsonb_build_object functions let you construct JSON directly in SQL queries. If you have access to a live database, you can generate properly nested JSON without this tool. But when you're working with SQL dumps — say, exporting data from one PostgreSQL instance to seed another — this tool bridges the gap.

The json_array_elements and jsonb_array_elements functions in PostgreSQL can expand JSON arrays into rows, which is useful when converting in the opposite direction (JSON to SQL). For the forward direction (SQL to JSON), this tool handles the conversion and produces arrays that PostgreSQL's JSON functions can consume.

Watch out for PostgreSQL-specific escape syntax. In standard PostgreSQL, single quotes within string values are escaped by doubling ('O''Brien'), but within dollar-quoted strings ($$O'Brien$$), no escaping is needed. The converter handles both forms correctly, producing valid JSON output regardless of the escape mechanism used in the source SQL.

MySQL-Specific JSON Considerations

MySQL's JSON type (available since 5.7) stores JSON documents internally as a binary representation that allows efficient querying with ->> and JSON_EXTRACT. When your INSERT statements include JSON column values, the converter treats them the same way as PostgreSQL JSON values — preserving the structure as nested objects.

One MySQL-specific quirk: the JSON_QUOTE function wraps a string in quotes and escapes special characters, producing a valid JSON string. If your INSERT statements contain the output of JSON_QUOTE(), the values might be double-quoted in the SQL. The converter normalizes this to produce clean JSON output without extra quoting layers.

MySQL's JSON_ARRAY() and JSON_OBJECT() functions generate JSON values directly in SQL. Similar to PostgreSQL, these are useful when you have a live database connection, but for SQL dump conversion, this tool handles the translation. Be aware that MySQL stores JSON as a binary type internally, so when you dump it back to INSERT statements, the values might include escaped characters that the converter needs to unescape before producing clean JSON.

Another MySQL gotcha: the GROUP_CONCAT function is sometimes used to build JSON-like arrays in SQL (CONCAT('[', GROUP_CONCAT(JSON_QUOTE(col)), ']')). This produces a string that looks like JSON but isn't typed as JSON in MySQL. The converter will output it as a string value rather than a parsed array. If you need it as an actual array, preprocess the SQL to use JSON_ARRAYAGG instead.

Real-World Use Cases

Seeding a frontend from database dumps: You've got a SQL dump of reference data — countries, currencies, product categories — and your frontend needs it as JSON for a dropdown. Paste the INSERT statements, convert, and drop the output into your codebase. No database connection required. Building mock APIs: Tools like json-server, Mockoon, and MSW expect JSON data files. Export your test data as SQL, convert it to JSON, and feed it to the mock server. Migrating between databases: Moving from PostgreSQL to MongoDB? Export as SQL, convert to JSON, and use mongoimport. The column-to-field mapping is automatic. Test fixtures: Many testing frameworks (Jest, Mocha, Pytest, PHPUnit) accept JSON fixture files. If your existing test data lives in SQL seed files, this conversion bridges the gap without requiring a running database. Data analysis: Some analysis tools and notebooks prefer JSON input over raw SQL. Convert your dataset and load it into pandas, Observable, or a Jupyter notebook. API documentation: When writing API docs with tools like Swagger or Redoc, you need example payloads. Pull real data from your SQL fixtures, convert to JSON, and use the output as realistic examples.

Frequently Asked Questions