How to Query JSON Columns in Postgres and DuckDB

Every third-party integration I've worked with stores at least some of its data as JSON. Stripe webhook payloads, HubSpot contact properties, Segment event metadata, API request/response logs. The data lands in a jsonb column, and then someone needs to actually query it — filter by a nested field, extract a value for a dashboard, unnest an array of line items into rows. That's where most people reach for Python or a Pandas script, which is fine, but unnecessary. Both Postgres and DuckDB have solid JSON support baked into SQL. You just need to know which operator does what.

Postgres JSONB operators: the cheat sheet

Postgres has six operators you'll actually use. Here they are against a real table:

-- Table: webhook_events(id serial, event_type text, payload jsonb, created_at timestamptz)
 
-- -> returns a JSON object (still jsonb)
SELECT payload->'data' FROM webhook_events;
 
-- ->> returns text (the value extracted as a string)
SELECT payload->>'type' FROM webhook_events;
 
-- Chain them: -> for intermediate objects, ->> for the leaf value
SELECT payload->'data'->'object'->>'amount' FROM webhook_events;
 
-- #> navigates a path, returns jsonb
SELECT payload #> '{data,object,customer}' FROM webhook_events;
 
-- #>> navigates a path, returns text
SELECT payload #>> '{data,object,customer}' FROM webhook_events;
 
-- @> containment: does the left side contain the right side?
SELECT * FROM webhook_events
WHERE payload @> '{"type": "invoice.payment_succeeded"}'::jsonb;
 
-- ? key existence: does the top-level object have this key?
SELECT * FROM webhook_events
WHERE payload->'data'->'object' ? 'discount';

The distinction that trips people up: -> returns jsonb, ->> returns text. If you try to compare payload->'amount' to an integer, it won't work — you're comparing a JSON value to a number. Use ->> and cast.

Filtering on JSON fields (and making it fast)

A WHERE clause on a JSONB column works fine syntactically, but without an index you're doing a sequential scan on every row. For containment checks (@>) and key existence (?), a GIN index is what you want:

CREATE INDEX idx_webhook_payload ON webhook_events USING GIN (payload);
 
-- This query now uses the GIN index
SELECT id, event_type, created_at
FROM webhook_events
WHERE payload @> '{"type": "charge.succeeded"}'::jsonb;
 
-- But this does NOT use the GIN index — it's a text extraction, not containment
SELECT id, event_type
FROM webhook_events
WHERE payload->>'type' = 'charge.succeeded';

That second query needs a functional index if you want it indexed: CREATE INDEX idx_event_type ON webhook_events ((payload->>'type')). I see teams create the GIN index and then wonder why their ->> filter queries are still slow. Different operator, different index type.

Unnesting JSON arrays

Stripe line items, HubSpot associations, any API that returns arrays inside objects. jsonb_array_elements turns a JSON array into rows:

-- webhook_events stores Stripe invoice payloads
-- Each invoice has payload->'data'->'object'->'lines'->'data' (an array of line items)
 
SELECT
    we.id AS event_id,
    we.created_at,
    we.payload #>> '{data,object,customer}' AS customer_id,
    item->>'description' AS line_description,
    (item->>'amount')::int / 100.0 AS line_amount,
    item->>'currency' AS currency
FROM webhook_events we,
    jsonb_array_elements(we.payload #> '{data,object,lines,data}') AS item
WHERE we.event_type = 'invoice.payment_succeeded'
  AND we.created_at >= '2026-07-01';

One thing to watch: jsonb_array_elements will error if the path resolves to NULL or a non-array. Wrap it in a LATERAL join with a CASE check or filter upstream if your data isn't guaranteed to have that path.

For turning a JSON object into typed columns, jsonb_to_record and jsonb_to_recordset are underused. They're useful when you have a well-known structure and want to query it like a normal table:

-- api_logs(id serial, request_body jsonb, response_body jsonb, status int, created_at timestamptz)
 
SELECT
    al.id,
    al.status,
    req.email,
    req.plan_name,
    (req.quantity)::int AS quantity
FROM api_logs al,
    jsonb_to_record(al.request_body) AS req(email text, plan_name text, quantity text)
WHERE al.created_at >= now() - interval '7 days'
  AND al.status >= 400;

You define the column names and types in the AS clause. Postgres extracts matching keys and casts them. Missing keys come back as NULL — no error.

DuckDB: same idea, different syntax

DuckDB supports -> and ->> with the same semantics as Postgres (JSON object vs. text extraction), but it also has explicit function names that I find clearer in complex queries:

• json_extract(col, '$.path.to.key') returns JSON (like ->)
• json_extract_string(col, '$.path.to.key') returns VARCHAR (like ->>)

The path syntax uses JSONPath ($.data.object.amount) instead of Postgres's chained operators or array syntax. Both work. Pick one and be consistent.

Where DuckDB really pulls ahead is querying JSON files directly, without loading them into a table first. read_json_auto infers schema and types from the file:

-- Query a HubSpot contacts JSON export sitting on disk
SELECT
    json_extract_string(properties, '$.email') AS email,
    json_extract_string(properties, '$.company') AS company,
    json_extract_string(properties, '$.lifecyclestage') AS lifecycle_stage,
    json_extract_string(properties, '$.hs_lead_status') AS lead_status,
    createdAt
FROM read_json_auto('hubspot_contacts_export.json')
WHERE json_extract_string(properties, '$.lifecyclestage') = 'customer'
ORDER BY createdAt DESC;

No CREATE TABLE, no import step, no schema definition. The file is the table. For one-off analysis — "marketing exported 12,000 contacts from HubSpot, tell me how many are customers vs. leads" — this is hard to beat. You can also point read_json_auto at a glob pattern ('exports/*.json') or an S3 path, and DuckDB will read them all as a single table.

Gotchas that will cost you an hour

json vs. jsonb. Postgres has both types. Always use jsonb. The json type stores the raw text and re-parses it on every query. jsonb is decomposed binary — it's indexed, it supports containment operators, and it's faster for reads. There is no situation where json is the better choice for a column you'll query.

NULL vs. JSON null. These are different values. A SQL NULL means the key is absent. A JSON null ('null'::jsonb) means the key exists with an explicit null value. payload->>'discount' returns SQL NULL in both cases, which makes them indistinguishable with ->>. If you need to tell the difference, use payload ? 'discount' to check key existence, then payload->'discount' to check if the value is JSON null (= 'null'::jsonb).

Casting. ->> always returns text. If you're doing arithmetic or comparisons, you need to cast: (payload->>'amount')::int, (payload->>'created')::timestamptz. Skip the cast and your WHERE payload->>'amount' > 1000 is doing a string comparison — "9" is greater than "1000" lexicographically. I've debugged this exact issue in production.

DuckDB's stricter typing. DuckDB's json_extract_string returns VARCHAR, which behaves the same way — you need CAST(json_extract_string(col, '$.amount') AS INTEGER) for numeric operations. DuckDB won't silently coerce types the way some people expect.

Cross-source JSON queries

If you're pulling Stripe webhooks from Postgres and a HubSpot export from a JSON file, you're usually writing two separate queries and joining in a spreadsheet. Fastero's cross-source DuckDB store handles JSON natively across both sources — you can join a jsonb column from your Postgres webhook table against a JSON file from HubSpot in a single SQL query, with the extraction and casting working the same way in both. No intermediate CSV, no Python glue.

Try Fastero free — connect Postgres, query JSON files with DuckDB, and join across both in one SQL editor. No credit card required.