How JSON values map to SQL
| JSON value | Column type (PostgreSQL) | Inserted as |
|---|---|---|
42 | INTEGER (BIGINT if large) | 42 |
19.90 | NUMERIC(4,2) | 19.90 |
true | BOOLEAN | TRUE (1 in SQLite, SQL Server, Oracle) |
"2026-01-15" | DATE | '2026-01-15' |
"Ada" | TEXT | 'Ada' |
null or a missing key | (column allows NULL) | NULL |
["a", "b"], {"k": 1} | JSONB | '["a","b"]' |
Importing JSON in the database itself
INSERT INTO users (id, name, email)
SELECT id, name, email
FROM jsonb_to_recordset('[{"id":1,"name":"Ada","email":"ada@example.com"}]'::jsonb)
AS t(id int, name text, email text);INSERT INTO users (id, name, email)
SELECT * FROM JSON_TABLE(
'[{"id":1,"name":"Ada","email":"ada@example.com"}]', '$[*]'
COLUMNS (id INT PATH '$.id', name VARCHAR(100) PATH '$.name', email VARCHAR(255) PATH '$.email')
) AS t;Starting from a spreadsheet export instead? Use CSV to SQL. Need typed models for the same data in code? Try JSON to TypeScript or JSON to Go.
Frequently asked questions
What JSON shapes are supported?
An array of objects, where each object is a row and keys become columns ([{"id": 1, "name": "Ada"}, …]). Objects may have different keys: every key that appears anywhere becomes a column, and missing values are NULL. An array of arrays with a header row works too, and so does an object that wraps the array, like {"users": [...]}. Array rows with more values than the header are rejected; shorter rows are padded with NULL.
What happens to nested objects and arrays?
They are stored as JSON text in a JSON-typed column: JSONB in PostgreSQL, JSON in MySQL, and text in SQLite, SQL Server and Oracle. You can then query them with the database’s JSON functions, such as tags->>0 in PostgreSQL or JSON_EXTRACT(tags, '$[0]') in MySQL. To get one column per nested field instead, flatten the JSON first, for example with JSON to CSV.
Are large numbers preserved?
Yes. Numbers are copied into the SQL exactly as written in the JSON, so an ID like 9007199254740993 or a price like 19.90 stays unchanged. Most tools pass numbers through JavaScript, which rounds integers above 2^53 and drops trailing zeros.
How are dates handled?
Valid ISO calendar dates are detected: 2026-01-15 becomes a DATE column and 2026-01-15T09:30:00 a TIMESTAMP. Values with Z or an offset up to ±14:00 become TIMESTAMPTZ in PostgreSQL and DATETIMEOFFSET in SQL Server. Fractional-second precision is declared explicitly. Values exceeding the target database’s time precision stay text to avoid rounding. Invalid dates such as February 30 and invalid offsets remain text. If any value in the column doesn’t match, the entire column is text.
Can I load JSON into PostgreSQL without converting it?
Yes, PostgreSQL can expand a JSON array itself, which is handy for one-off imports. See the example below using jsonb_to_recordset. The converter is more convenient when you also need the CREATE TABLE, or the target is another database.