JSON | PostgreSQL Tutorial

What is JSON and JSONB in PostgreSQL?

JSON (JavaScript Object Notation) is a lightweight format for representing structured data as key-value objects and ordered arrays. PostgreSQL supports two types for storing JSON:

JSON — stores an exact copy of the input text, preserving whitespace, key order, and duplicate keys.
JSONB — parses and stores JSON in binary format. Smaller on disk, faster to query, supports GIN indexes. Does not preserve whitespace, key order, or duplicate keys.

In practice, use JSONB for almost all use cases. Reserve JSON only when you must preserve the exact original formatting.

Storing JSON objects

CREATE TABLE products (
  id         SERIAL PRIMARY KEY,
  name       VARCHAR(255) NOT NULL,
  properties JSONB
);

INSERT INTO products (name, properties) VALUES
  ('Ink Fusion T-Shirt',    '{"color": "white", "size": ["S","M","L","XL"]}'),
  ('ThreadVerse T-Shirt',   '{"color": "black", "size": ["S","M","L","XL"]}'),
  ('Design Dynamo T-Shirt', '{"color": "blue",  "size": ["S","M","L","XL"]}')
RETURNING *;

Extracting fields with -> and ->>

-- -> returns a JSON value (with quotes for strings)
SELECT id, name, properties -> 'color' AS color
FROM products;
-- "white", "black", "blue"

-- ->> returns a text value (no quotes)
SELECT id, name, properties ->> 'color' AS color
FROM products;
-- white, black, blue

Filtering with JSONB in WHERE

-- Exact match using ->>
SELECT id, name, properties ->> 'color' AS color
FROM products
WHERE properties ->> 'color' IN ('black', 'white');

-- Containment query using @>
SELECT id, name
FROM products
WHERE properties @> '{"color": "blue"}';

Storing and querying JSON arrays

CREATE TABLE contacts (
  id     SERIAL PRIMARY KEY,
  name   VARCHAR(255) NOT NULL,
  phones JSONB
);

INSERT INTO contacts (name, phones) VALUES
  ('John Doe', '["408-111-2222", "408-111-2223"]'),
  ('Jane Doe', '["212-111-2222", "212-111-2223"]');

-- Get the first phone number (index 0)
SELECT name, phones ->> 0 AS work_phone
FROM contacts;

-- Output:
-- name     | work_phone
-- ---------+--------------
-- John Doe | 408-111-2222
-- Jane Doe | 212-111-2222

Indexing JSONB for performance

-- GIN index for containment and key-existence queries
CREATE INDEX idx_products_properties ON products USING GIN (properties);

-- Expression index for queries on a specific key
CREATE INDEX idx_products_color ON products ((properties ->> 'color'));

Production tips

Always choose JSONB over JSON unless you have a specific need to preserve exact input formatting.
Create a GIN index on JSONB columns that are frequently searched with @>, ?, ?&, or ?| operators.
For frequently queried specific fields, use expression indexes on extracted values ((data ->> 'key')) — they are more selective than full GIN indexes.
Avoid storing very large JSON documents (megabytes) in JSONB columns — large values trigger PostgreSQL's TOAST compression/storage, which adds overhead.

Reference: PostgreSQL documentation — JSON types.

Frequently Asked Questions

What is the difference between JSON and JSONB in PostgreSQL?

JSON stores the verbatim text of the JSON input, preserving whitespace, key order, and duplicate keys. JSONB parses the JSON and stores it in a binary format: it is typically smaller, strips whitespace, removes duplicate keys (keeping the last value), and does not preserve key order. JSONB supports GIN indexes and is generally faster for querying. Use JSON only when you need to preserve exact input formatting.

How do you extract a field from a JSONB object in PostgreSQL?

Use the -> operator to extract a field as a JSON value: properties -> 'color' returns "white" (with quotes). Use the ->> operator to extract a field as text: properties ->> 'color' returns white (without quotes). For nested paths use #> (returns JSON) or #>> (returns text): data #>> '{address,city}' extracts a nested city field as text.

How do you filter rows by a JSONB field value?

Use the ->> operator in the WHERE clause: SELECT * FROM products WHERE properties ->> 'color' = 'blue'; For containment queries, use the @> operator: SELECT * FROM products WHERE properties @> '{"color": "blue"}'; The @> operator can use a GIN index for efficient lookups on large tables.

How do you index JSONB data in PostgreSQL?

Create a GIN index on the JSONB column to speed up containment (@>) and key-existence (?, ?&, ?|) queries: CREATE INDEX idx_products_properties ON products USING GIN (properties); For queries that always extract a specific key, an expression index is more efficient: CREATE INDEX idx_products_color ON products ((properties ->> 'color'));

How do you store arrays in a JSONB column?

Insert a JSON array as a string: INSERT INTO contacts (name, phones) VALUES ('John', '["408-111-2222", "408-111-2223"]'); Access array elements by index using ->> with an integer: phones ->> 0 returns the first element as text. Use jsonb_array_elements() to expand array elements into rows for further filtering or aggregation.