JSON Data Type in PostgreSQL

JSON (JavaScript Object Notation) is a lightweight format for structured key-value data. PostgreSQL provides two types for storing JSON — JSON and JSONB — and choosing between them has significant performance implications for querying and indexing.

JSON vs. JSONB Storage

For the JSON vs. JSONB storage internals, see the JSON/JSONB glossary entry.

Practical Example

Store variable product attributes in a JSONB column:

CREATE TABLE products (
  id          SERIAL PRIMARY KEY,
  sku         VARCHAR(50) NOT NULL UNIQUE,
  name        VARCHAR(200) NOT NULL,
  attributes  JSONB
);

INSERT INTO products (sku, name, attributes)
VALUES
  ('BOLT-TI-M6', 'Titanium M6 Bolt',
   '{"material": "titanium", "size": "M6", "lengths_mm": [10, 16, 20, 25], "grade": "5"}'),
  ('PANEL-CF-A4', 'Carbon Fiber Panel A4',
   '{"material": "carbon_fiber", "dimensions_mm": {"w": 210, "h": 297, "t": 2}, "finish": "matte"}'),
  ('BOLT-ST-M8', 'Steel M8 Bolt',
   '{"material": "steel", "size": "M8", "lengths_mm": [20, 30, 40], "grade": "8.8"}');

Extracting Fields

-- -> returns a JSON value (with quotes for strings)
SELECT sku, attributes -> 'material' AS material_json
FROM products;
-- "titanium", "carbon_fiber", "steel"

-- ->> returns text (no quotes)
SELECT sku, attributes ->> 'material' AS material_text
FROM products;
-- titanium, carbon_fiber, steel

-- #>> extracts a nested path as text
SELECT sku, attributes #>> '{dimensions_mm,w}' AS width_mm
FROM products;

Filtering by JSONB Content

-- Exact text match
SELECT sku, name
FROM products
WHERE attributes ->> 'material' = 'titanium';

-- Containment query (can use GIN index)
SELECT sku, name
FROM products
WHERE attributes @> '{"material": "steel"}';

-- Check if a key exists
SELECT sku FROM products WHERE attributes ? 'grade';

-- Check if any of several keys exist
SELECT sku FROM products WHERE attributes ?| ARRAY['finish', 'grade'];

Array Elements in JSONB

-- Expand a JSONB array to rows
SELECT sku, jsonb_array_elements_text(attributes -> 'lengths_mm') AS length_mm
FROM products
WHERE attributes ? 'lengths_mm';

GIN Indexing for Performance

Create a GIN index for containment and key-existence queries:

-- Full GIN index for @>, ?, ?|, ?& operators
CREATE INDEX CONCURRENTLY idx_products_attrs
ON products USING GIN (attributes);

-- Expression index for a specific key (more selective)
CREATE INDEX CONCURRENTLY idx_products_material
ON products ((attributes ->> 'material'));

Verify the index is used:

EXPLAIN ANALYZE
SELECT sku FROM products
WHERE attributes @> '{"material": "titanium"}';

Testing with Vela

JSONB schema evolution — adding keys, removing keys, changing nested structures — is invisible to PostgreSQL’s type system and easy to break silently. Use Vela’s database branching to test JSONB query migrations on a production-data clone: verify that your @> and ->> queries return the expected rows after a data migration script changes the JSON structure, before running the migration on the live database.

Production Tips

• Always choose JSONB over JSON unless you have a specific need to preserve exact input text and formatting.
• Create a GIN index on JSONB columns that are frequently searched with @>, ?, ?&, or ?| operators.
• For frequently queried specific keys, use expression indexes on (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 mechanism and add memory pressure.
• Use jsonb_set() for atomic key updates and jsonb_strip_nulls() to remove null-valued keys before storing.