Querying JSON in SQL

Garden Shop's payment processor reports each payment as a JSON document. It arrives as one text column, payload, and the fields you need (the order, the status, the amount) are inside it. The arrow operators read them out:

with payments(payload) as (
  values ('{"order_id": 1, "status": "paid", "amount": "54.00", "card": {"brand": "visa", "last4": "4242"}}'),
         ('{"order_id": 3, "status": "paid", "amount": "45.50", "wallet": "paypal"}'),
         ('{"order_id": 17, "status": "refunded", "amount": "22.00", "card": {"brand": "visa", "last4": "1881"}}')
)
select payload->>'order_id' as order_id,
       payload->>'status'   as status,
       payload->'card'      as card
from payments

The PayPal payment has no card, so its card is NULL. A missing key isn't an error, which suits JSON: documents from the same source often leave out the fields that don't apply.

Two arrows, two types

->> returns the field as plain text. -> returns it as JSON, so a string keeps its double quotes:

with payments(payload) as (
  values ('{"order_id": 1, "status": "paid", "amount": "54.00", "card": {"brand": "visa", "last4": "4242"}}'),
         ('{"order_id": 3, "status": "paid", "amount": "45.50", "wallet": "paypal"}'),
         ('{"order_id": 17, "status": "refunded", "amount": "22.00", "card": {"brand": "visa", "last4": "1881"}}')
)
select payload->'status'  as json_value,
       payload->>'status' as text_value
from payments

Use ->> for anything you compare, display, or cast. Use -> when the result is more JSON you'll read into, like card above.

In DuckDB there's a second reason to prefer ->> in a filter. A single arrow binds more loosely than =, so payload->'status' = 'paid' is read as payload->('status' = 'paid') and fails with No function matches ... json_extract(VARCHAR, BOOLEAN). payload->>'status' = 'paid' works as written.

Nested fields

Chain the arrows to go deeper, or give one arrow a path. A path starts at $, the whole document, and names each step with a dot:

with payments(payload) as (
  values ('{"order_id": 1, "status": "paid", "amount": "54.00", "card": {"brand": "visa", "last4": "4242"}}'),
         ('{"order_id": 3, "status": "paid", "amount": "45.50", "wallet": "paypal"}'),
         ('{"order_id": 17, "status": "refunded", "amount": "22.00", "card": {"brand": "visa", "last4": "1881"}}')
)
select payload->'card'->>'brand'  as chained,
       payload->>'$.card.brand'   as by_path,
       payload->>'wallet'         as wallet
from payments

Both return visa for the card payments and NULL for PayPal. Only the last arrow decides the result's type, so -> then ->> gives text.

Text that looks like a number

->> returns text even when the value is a number, and text compares letter by letter, the way numbers stored as text sort wrong:

with payments(payload) as (
  values ('{"order_id": 1, "status": "paid", "amount": "54.00", "card": {"brand": "visa", "last4": "4242"}}'),
         ('{"order_id": 3, "status": "paid", "amount": "45.50", "wallet": "paypal"}'),
         ('{"order_id": 17, "status": "refunded", "amount": "22.00", "card": {"brand": "visa", "last4": "1881"}}')
)
select count(*) filter (where payload->>'amount' > '100')           as as_text,
       count(*) filter (where (payload->>'amount')::decimal(10, 2) > 100) as as_number
from payments

As text, all three amounts are "greater" than '100', because 5, 4, and 2 all come after 1. As numbers, none is. Cast first, and put the extraction in parentheses so the cast applies to the field and not to the key.

JSON arrays

An order's items usually arrive as a JSON array of objects. json_array_length counts them. from_json converts the array into a list of structs, with the types you name, and unnest makes one row per item:

with orders_in(payload) as (
  values ('{"order_id": 9, "items": [{"sku": 3, "qty": 2}, {"sku": 10, "qty": 1}]}')
)
select json_array_length(payload, '$.items') as item_count,
       unnest(from_json(payload->'items', '[{"sku": "INTEGER", "qty": "INTEGER"}]'),
              recursive := true)
from orders_in

recursive := true unnests the list and then each struct, so sku and qty become columns. This is the whole chapter in one query: JSON becomes a list of structs, and the list becomes rows.

Building JSON

Going the other way, to_json turns a struct (or a list, or any value) into JSON text, ready to send to another system:

select to_json({'order_id': order_id, 'status': status}) as payload
from orders
order by order_id
limit 3

In other databases

PostgreSQL has the same -> and ->>, on its json and jsonb types, and they bind more tightly than = there. MySQL accepts payload->>'$.card.brand', always with a path. SQL Server uses JSON_VALUE(payload, '$.card.brand'), and Snowflake payload:card.brand on a VARIANT column.

Where to go from here

That is the last lesson. The habits carry on without it:

• Scale up. The practice datasets include tables with hundreds of thousands of rows, and each dataset's page has practice questions written against it.
• Use your own data. The playground loads a CSV or Parquet file without sending it anywhere.
• Rehearse the interview. The interview questions ask for the same patterns under time pressure.
• Change engines. Download a dataset's SQL, load it into PostgreSQL or SQLite, and rebuild one report there. That is also where to learn what this workshop leaves out: indexes and query plans.