JSON and JSONB in PostgreSQL

PostgreSQL can store and query semi-structured JSON data natively — bringing some of MongoDB's flexibility into a relational table.

JSON vs JSONB

• JSON stores an exact text copy of the input, including whitespace and key order — cheap to write, slower to query.
• JSONB stores a parsed, binary representation — slightly slower to write, but much faster to query and can be indexed.

Use JSONB unless you have a specific reason not to.

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

INSERT INTO products (name, attributes) VALUES
  ('Laptop', '{"brand": "Dell", "ram_gb": 16, "tags": ["electronics", "computers"]}');

Accessing Values: -> and ->>

• -> returns the value as JSON
• ->> returns the value as text

SELECT attributes -> 'brand' FROM products;    -- "Dell" (JSON string, with quotes)
SELECT attributes ->> 'brand' FROM products;   -- Dell (plain text)

SELECT attributes ->> 'ram_gb' FROM products WHERE name = 'Laptop';

Nested Paths with #> and #>>

SELECT attributes #>> '{specs, cpu}' FROM products;
-- reaches into a nested object at attributes.specs.cpu

Filtering by a JSON Field

SELECT * FROM products WHERE attributes ->> 'brand' = 'Dell';
SELECT * FROM products WHERE (attributes ->> 'ram_gb')::INT >= 16;

Containment: @>

Checks whether the JSONB value contains a given structure — the fastest way to filter JSONB, especially once indexed.

SELECT * FROM products WHERE attributes @> '{"brand": "Dell"}';

Array Elements Inside JSONB

SELECT * FROM products WHERE attributes -> 'tags' ? 'electronics';
-- ? checks if a string exists as a top-level array element or object key

Indexing JSONB with GIN

CREATE INDEX idx_attributes ON products USING GIN (attributes);

A GIN index dramatically speeds up @>, ?, and similar JSONB operators — without it, every query scans the whole table.

Common Errors & Troubleshooting

• ->> returning text you then can't compare numerically — attributes ->> 'ram_gb' always returns text, even if the underlying JSON value is a number; cast it explicitly ((attributes ->> 'ram_gb')::INT) before using it in a numeric comparison.
• @> returning no matches despite looking right — containment is type- and structure-sensitive; {"ram_gb": "16"} (a string) will not match against a stored {"ram_gb": 16} (a number) — the types must match exactly.
• Full table scans on every JSONB query — without a GIN index, every @>/? query on a JSONB column scans the whole table; add CREATE INDEX ... USING GIN (column) once you're querying it regularly.

Key Takeaways

• Prefer JSONB over JSON for anything you'll actually query — it's indexable and faster to read.
• -> gives back JSON, ->> gives back text — you'll reach for ->> most often when comparing values.
• The containment operator @>, backed by a GIN index, is the standard efficient way to filter on JSONB fields.