Convert JSON to PostgreSQL
CREATE TABLE — free & instant

From JSON to PostgreSQL DDL in seconds

No server. No risk. No nonsense.

Related JSON tools

Common questions answered

Converting JSON for PostgreSQL

PostgreSQL has first-class JSON support with its jsonb column type, operators (-> and ->>), and rich JSON functions. This tool generates PostgreSQL-specific SQL: CREATE TABLE with jsonb columns, INSERT with proper casting, and example queries using native JSON operators.

The converter produces two outputs: a relational mapping (each key becomes a column) and a jsonb mapping (entire document in one column). The relational approach suits frequently queried fields; jsonb preserves flexibility for semi-structured data. Output includes GIN indexing suggestions for fast JSON queries.

{
  "name": "Alice",
  "age": 30,
  "meta": {
    "roles": ["admin"],
    "joined": "2024-01-15"
  }
}

-- Relational
CREATE TABLE users (
  name TEXT NOT NULL,
  age INTEGER,
  meta JSONB
);

-- Query jsonb
SELECT meta->>'joined'
FROM users
WHERE meta->'roles' ? 'admin';

Get the most out of this tool

• Use jsonb over json in PostgreSQL — jsonb is binary, faster to query, and supports indexing.
• Create GIN indexes on jsonb: CREATE INDEX idx ON t USING GIN (data jsonb_path_ops) for fast queries.
• Use -> for JSON objects and ->> for text extraction: data->>'name' returns text for WHERE clauses.

PostgreSQL schema generation from JSON

PostgreSQL is the most feature-rich open-source relational database, and it offers exceptional JSON support through its JSON and JSONB column types. However, for most applications, mapping JSON data to proper relational tables with typed columns produces better query performance, clearer schemas, and more efficient storage than storing JSON blobs. This converter helps you design the relational schema from a JSON sample, generating CREATE TABLE statements with PostgreSQL-specific types.

PostgreSQL's type system is much richer than standard SQL. The converter maps JSON strings to TEXT or VARCHAR(n), JSON integers to INTEGER or BIGINT (depending on range), JSON floats to NUMERIC (for precision) or FLOAT8 (double precision), JSON booleans to BOOLEAN, and JSON null to NULL in any column type. PostgreSQL additionally supports UUID, TIMESTAMP WITH TIME ZONE, INET (IP addresses), JSONB (binary JSON), and ARRAY types — the converter infers these from field naming conventions when the pattern is recognizable.

The JSONB column type is PostgreSQL's most powerful feature for semi-structured data. Unlike TEXT storage of JSON, JSONB stores a parsed binary representation that supports GIN indexes for fast key and value lookups, containment operators (@> for "contains"), and path operators for querying nested values. If your JSON data has fields that are themselves JSON objects with variable structure, storing them as JSONB while keeping stable fields as regular typed columns is the best of both worlds.

Primary key generation is a critical schema design decision not determinable from a JSON sample alone. The converter adds a SERIAL or BIGSERIAL primary key column (PostgreSQL's auto-incrementing integer) as a default, or uses UUID as the primary key if the JSON sample contains a field named "id" with UUID-format values. Review the generated DDL and adjust the primary key type based on your application's identity requirements.

Foreign key relationships between tables are inferred from field naming patterns — a field named "user_id" in a JSON object that also has a "users" array is likely a foreign key reference. The converter includes comments suggesting potential foreign key relationships but does not generate them automatically, since referential integrity constraints require knowledge of the full schema that cannot be inferred from a single JSON sample.

PostgreSQL indexes are not generated by the converter but are critical for performance. Every column used in WHERE clauses, JOIN conditions, and ORDER BY should have an index. The generated DDL is a starting point — after reviewing it, add CREATE INDEX statements for the columns you will query most frequently. GIN indexes on JSONB columns, BRIN indexes on timestamp columns for time-series data, and partial indexes for common filter conditions are all PostgreSQL-specific optimizations worth considering.

When developers use this tool

Additional frequently asked questions