IntermediateAdvanced PostgreSQL

JSONB: store and query JSON data

The query

SQL

-- Create table with JSONB column
CREATE TABLE events (
  id      SERIAL PRIMARY KEY,
  data    JSONB,
  created_at TIMESTAMP DEFAULT NOW()
);

-- Query JSON fields
SELECT
  id,
  data->>'event_type' AS event_type,
  data->'user'->>'name' AS user_name,
  (data->>'amount')::NUMERIC AS amount,
  data @? '$.items[*] ? (@.qty > 5)' AS has_large_items
FROM events
WHERE data->>'event_type' = 'purchase';

Tested against PostgreSQL 16

Note

“-> returns JSONB, ->> returns TEXT. @? uses jsonpath. Index with: CREATE INDEX ON events USING gin(data).