PostgreSQL JSON and JSONB

PostgreSQL supports storing and querying JSON (JavaScript Object Notation) data natively. This bridges the gap between relational and document-style storage — structured table data and flexible, schema-free JSON can live in the same database. PostgreSQL provides two JSON data types: json and jsonb.

JSON vs JSONB

Use jsonb in nearly all cases. It is faster to query, supports indexing, and is the recommended choice for production workloads. Use json only when preserving exact input format (including key order and whitespace) is a strict requirement.

Creating a Table with JSONB

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    attributes JSONB
);

INSERT INTO products (name, attributes) VALUES
    ('Laptop', '{"brand": "Dell", "ram": 16, "storage": 512, "colors": ["black", "silver"]}'),
    ('Phone',  '{"brand": "Apple", "ram": 6, "storage": 128, "colors": ["white", "black", "blue"]}'),
    ('Tablet', '{"brand": "Samsung", "ram": 8, "storage": 256, "colors": ["gray"]}');

Accessing JSON Values

The -> Operator (Returns JSON)

-- Get the "brand" key as a JSON value
SELECT name, attributes -> 'brand' AS brand
FROM products;

The result for brand will be "Dell" (with quotes) because the value is returned as a JSON type.

The ->> Operator (Returns Text)

-- Get the "brand" key as plain text
SELECT name, attributes ->> 'brand' AS brand
FROM products;

The result is Dell (without quotes) as a plain text string. Use ->> when the value needs to be compared, filtered, or displayed as text.

Accessing Nested Keys

-- Chain -> for nested objects
SELECT attributes -> 'specs' -> 'cpu' AS cpu
FROM products;

-- Or use the path operator #>
SELECT attributes #> '{specs, cpu}' AS cpu
FROM products;

-- As text with #>>
SELECT attributes #>> '{specs, cpu}' AS cpu
FROM products;

Accessing Array Elements

-- Get the first color (0-indexed)
SELECT name, attributes -> 'colors' -> 0 AS first_color
FROM products;

-- As text
SELECT name, attributes -> 'colors' ->> 0 AS first_color
FROM products;

Filtering Rows by JSON Values

-- Find products made by Dell
SELECT name FROM products
WHERE attributes ->> 'brand' = 'Dell';

-- Find products with 16 GB RAM or more
SELECT name FROM products
WHERE (attributes ->> 'ram')::INT >= 16;

Note the cast ::INT — JSON values are text when extracted with ->>, so numeric comparisons require an explicit type cast.

The @> Containment Operator

The @> operator checks whether the left JSONB value contains the right JSONB value. This is one of the most powerful JSONB operators.

-- Find products with brand = Apple
SELECT name FROM products
WHERE attributes @> '{"brand": "Apple"}';

-- Find products that have 'black' in their colors array
SELECT name FROM products
WHERE attributes @> '{"colors": ["black"]}';

The ? Existence Operator

The ? operator checks whether a key exists in a JSONB object:

-- Find products that have a "warranty" key
SELECT name FROM products
WHERE attributes ? 'warranty';

-- Check if any of several keys exist
SELECT name FROM products
WHERE attributes ?| ARRAY['warranty', 'refurbished'];

-- Check if all specified keys exist
SELECT name FROM products
WHERE attributes ?& ARRAY['brand', 'ram', 'storage'];

Modifying JSONB Data

Adding or Updating a Key with ||

-- Add a new key to all products
UPDATE products
SET attributes = attributes || '{"in_stock": true}'
WHERE id = 1;

-- Update an existing key
UPDATE products
SET attributes = attributes || '{"ram": 32}'
WHERE name = 'Laptop';

Removing a Key with -

UPDATE products
SET attributes = attributes - 'in_stock'
WHERE id = 1;

Using jsonb_set() for Nested Updates

UPDATE products
SET attributes = jsonb_set(attributes, '{brand}', '"HP"', false)
WHERE id = 1;

jsonb_set(target, path, new_value, create_missing) sets the value at the given path. The fourth argument false means do not create the key if it does not exist.

Useful JSON Functions

-- Expand all keys of attributes into rows
SELECT name, key, value
FROM products, jsonb_each(attributes);

-- Expand the colors array into individual rows
SELECT name, color
FROM products, jsonb_array_elements_text(attributes -> 'colors') AS color;

-- Count the number of colors per product
SELECT name, jsonb_array_length(attributes -> 'colors') AS color_count
FROM products;

Indexing JSONB with GIN

A GIN (Generalized Inverted Index) index on a JSONB column makes containment (@>) and existence (?) queries very fast:

CREATE INDEX idx_product_attributes
ON products USING GIN (attributes);

-- This query now uses the GIN index
SELECT name FROM products
WHERE attributes @> '{"brand": "Apple"}';

For queries on a specific key (like filtering by brand frequently), a more targeted expression index is even more efficient:

CREATE INDEX idx_product_brand
ON products ((attributes ->> 'brand'));

Building JSON from Relational Data

-- Build a JSON object from column values
SELECT jsonb_build_object(
    'id', id,
    'name', name,
    'brand', attributes ->> 'brand'
) AS product_json
FROM products;

-- Aggregate multiple rows into a JSON array
SELECT jsonb_agg(
    jsonb_build_object('name', name, 'brand', attributes ->> 'brand')
) AS product_list
FROM products;

Key Points

• PostgreSQL supports two JSON types: json (text storage) and jsonb (binary storage). Use jsonb for almost all use cases.
• The -> operator returns a JSON value; ->> returns a plain text string.
• The @> containment operator checks if a JSONB value contains another — ideal for filtering nested data.
• The ? operator checks for key existence in a JSONB object.
• JSONB values can be updated using the || merge operator, the - delete operator, or jsonb_set().
• A GIN index on a JSONB column dramatically speeds up containment and existence queries.
• Functions like jsonb_each, jsonb_array_elements, and jsonb_agg allow rich manipulation of JSON data.