JSON to PostgreSQL — Paste It Into psql

What this tool does

A JSON sample becomes a CREATE TABLE.

{ "id": 1, "firstName": "Ada", "score": 9.5, "address": { "city": "Paris" } }

CREATE TABLE "root" (
  "id" BIGINT NOT NULL,
  "first_name" TEXT NOT NULL,
  "score" DOUBLE PRECISION NOT NULL,
  "address" JSONB NOT NULL
);

Names are snake_case and quoted, which keeps them exact and lets a reserved word like "order" be a column without ceremony.

TEXT has no length to invent

PostgreSQL stores TEXT and VARCHAR(n) the same way; the only difference is the constraint. Since a sample cannot tell you the real limit — only the longest value it happens to contain — no limit is written. Adding VARCHAR(40) later is a decision about your domain, and it costs nothing in storage.

That is also why this page has no equivalent of the MySQL note about VARCHAR(255): there is no length to be wrong about.

All the rows, not just the first

[{ "a": 1 }, { "b": "x" }]

CREATE TABLE "root_item" (
  "a" BIGINT NULL,
  "b" TEXT NULL
);

The table used to be built from the first object alone, so the second row of your own sample had nowhere to go. The columns are now the union of every object, and a key that some rows lack is nullable — because it is.

Types are merged the same way. When one key is a number in one object and a string in another, no scalar column takes both, so the column is JSONB. An integer next to a decimal is a different story: DOUBLE PRECISION covers the two, and that is what it gets.

JSONB for what SQL has no column for

Nested objects and arrays become JSONB. No related table, no foreign key: a sample shows a shape, never a relation, and inventing a join would mean guessing a table, a key and a direction at once.

JSONB is the queryable form — ->, ->>, @>, and a GIN index when you need one. The plain json type keeps the original text instead, which only matters if you plan to return the document unchanged.

Getting the rows in

PostgreSQL has no bq load. COPY is built around row-per-line text and CSV, not an array of JSON objects, so the table above is only half the job.

The obvious route is jsonb_populate_recordset, which matches JSON keys to column names — exactly, character for character. That is the catch: the table above renamed firstName to first_name, so that route would leave the column NULL without a word about it.

The mapping that fits the table above is explicit, and says which key feeds which column:

INSERT INTO "root" ("id", "first_name", "score", "address")
SELECT (e->>'id')::bigint,
       e->>'firstName',
       (e->>'score')::double precision,
       e->'address'
FROM jsonb_array_elements(:'doc'::jsonb) AS e;

:'doc' is a psql variable holding the document — psql -v doc="$(cat data.json)" -f load.sql while the file still fits in an argument. Past that, copy the text into a one-column staging table and select from there; the shape of the query does not change.

What a sample cannot say

• No primary key. An id that looks unique in one document is not a promise about the next.
• No index, no default, no CHECK. All of them are statements about the data rather than readings of it.
• NOT NULL where a value was present, NULL for a null and for a key missing from some of the objects.

Private by design

Everything runs locally in your browser with JavaScript. Your data is never uploaded, which makes the tool safe for sensitive content, and it keeps working offline.