How to Convert an SQL Query Result or CSV to JSON (and a PHP Array for Seeders)

· 5 minutes read

How to Convert an SQL Query Result or CSV to JSON (and a PHP Array for Seeders)

A typical situation: you run a query in the MySQL console, get a nice table with a few rows and want to use exactly those rows somewhere else – as test data in a Laravel seeder, as a JSON fixture for a frontend, or as a payload for an API call. Retyping them by hand is slow and error-prone. Here are the fastest ways to convert the result.

The fastest way: paste the table and convert it

Our free Table to JSON converter understands whatever you copy from the usual places:

  1. Copy the result – the bordered table from the mysql client, the output of psql, a CSV file, or cells from Excel / Google Sheets.
  2. Paste it into the tool. The format is detected automatically (you can override it).
  3. Pick the output: JSON array of objects, JSON array of arrays, PHP array or JavaScript object.
  4. Keep Convert numbers, booleans and null on if you want 1 instead of "1" and NULL as null.
  5. Copy the result or download it.

For example, this MySQL output:

+----+------------+-----------------+
| id | name       | email           |
+----+------------+-----------------+
|  1 | Ján Novák  | jan@example.com |
|  2 | Anna Smith | NULL            |
+----+------------+-----------------+
2 rows in set (0.00 sec)

becomes this PHP array, ready to paste into a seeder:

DB::table('users')->insert([
    [
        'id' => 1,
        'name' => 'Ján Novák',
        'email' => 'jan@example.com',
    ],
    [
        'id' => 2,
        'name' => 'Anna Smith',
        'email' => null,
    ],
]);

The prompt line and the "2 rows in set" footer are ignored automatically. The conversion runs in your browser, so production data never leaves your computer.

Let the database produce JSON directly

If you can change the query, MySQL (5.7.22 and newer) and PostgreSQL can return JSON themselves.

MySQL:

SELECT JSON_ARRAYAGG(JSON_OBJECT('id', id, 'name', name, 'email', email))
FROM users;

PostgreSQL:

SELECT json_agg(u) FROM (SELECT id, name, email FROM users) AS u;

Both return a single value with the whole array. In the console the result can be very long and get cut or wrapped; run it with mysql -N -B -e "…" or psql -At -c "…" to get the raw value without the table borders.

Get clean, tab-separated output from the console

When you need the data as a table rather than JSON, ask the client for machine-friendly output:

  • mysql -B -e "SELECT id, name FROM users" mydb prints tab-separated values with a header row (-N removes the header).
  • psql --csv -c "SELECT id, name FROM users" mydb prints CSV (psql 12+).

Both outputs can be pasted into the converter as well – tab-separated text is detected as TSV. Keep in mind that mysql -B prints NULL for null values and escapes tabs and newlines inside values as \t and \n.

In Laravel: let Eloquent do it

If the data is already in your app's database, you don't need to copy anything:

// JSON
User::select('id', 'name', 'email')->get()->toJson(JSON_PRETTY_PRINT);

// PHP array code for a seeder (run in php artisan tinker)
var_export(DB::table('users')->select('id', 'name')->get()->map(fn ($row) => (array) $row)->all());

var_export produces the old array (...) syntax with numeric keys. It works, but the short [ ] syntax from the converter is easier to read in a seeder.

CSV to JSON with Python

For large CSV files (hundreds of megabytes) a script is the better choice. This one-liner reads a CSV with a header row and prints JSON:

python3 -c "import csv, json, sys; print(json.dumps(list(csv.DictReader(sys.stdin)), indent=2, ensure_ascii=False))" < data.csv > data.json

Note that every value stays a string – csv does not guess types.

Excel and Google Sheets to JSON

Select the range including the header row and copy it (Ctrl+C / Cmd+C). The clipboard contains tab-separated text, so it can go straight into the converter. If your Excel saves CSV files with semicolons – common with European regional settings – the converter detects that as well.

Common pitfalls

  • Leading zeros. ZIP codes, phone numbers and product codes like 007 must stay strings. The converter keeps values with leading zeros as text; spreadsheet apps often silently remove them, so check the source.
  • Decimal commas. 1,5 is not a valid JSON number. Convert decimal commas to dots first if you need numbers.
  • Very large IDs. JavaScript (and therefore JSON in most tools) cannot represent integers above 9,007,199,254,740,991 exactly. Such values are kept as strings.
  • Empty vs NULL. MySQL shows NULL explicitly, psql shows an empty cell. The converter turns NULL into null but keeps empty cells as empty strings, because it cannot know which one you meant.
  • Pipes inside values. In bordered SQL tables, a | character inside a value is indistinguishable from a column border. Use tab-separated output for such data.

FAQ

How do I convert an SQL result to JSON without writing code?

Copy the result table from your SQL client and paste it into the Table to JSON converter. It recognizes MySQL bordered tables, psql output, Markdown tables and vertical \G output.

How do I make a Laravel seeder from existing data?

Copy the query result, choose PHP array in the converter and wrap the output in DB::table('your_table')->insert(...) inside the seeder's run() method.

Is it safe to paste production data into an online converter?

With our converter, yes – the conversion runs locally in your browser and nothing is sent to a server. Still, avoid committing real personal data into seeders that end up in your repository.

How do I check what changed between two exports?

Convert both to the same format and compare them with the Text compare tool, which highlights added, removed and changed lines.