SQL JSON Functions

JSON usually reaches a database as text from somewhere else: an API, an event log, an export. DuckDB reads fields straight out of that text with two operators, -> and ->>, and converts whole documents into its own lists and structs when you need them.

with payments(payload) as (
  values ('{"order_id": 1, "status": "paid", "amount": "54.00", "card": {"brand": "visa"}}'),
         ('{"order_id": 3, "status": "paid", "amount": "45.50", "wallet": "paypal"}')
)
select (payload->>'order_id')::integer          as order_id,
       payload->>'status'                       as status,
       (payload->>'amount')::decimal(10, 2)     as amount,
       payload->>'$.card.brand'                 as card_brand
from payments

The PayPal payment has no card, so its card_brand is NULL. A missing key is never an error.

Operators and paths

json_extract(j, path) and json_extract_string(j, path) are the function forms of -> and ->>. Keys are case-sensitive: 'A' doesn't find "a".

Functions

Arrays into rows

from_json converts a JSON array of objects into a list of structs, using a shape written as JSON with a type for each field. unnest with recursive := true then makes a row per element and a column per field:

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

json_structure writes a starting shape for you: run it on one document, then change the types you want. A value that doesn't fit its type comes back as NULL without an error, so from_json('{"a": "x"}', '{"a": "INTEGER"}') is {'a': NULL}. Count the NULLs after converting.

Building JSON

select c.category_name,
       to_json(list({'id': p.product_id, 'name': p.product_name}
                    order by p.product_id)) as products
from products as p
join categories as c on c.category_id = p.category_id
where c.category_id in (3, 6)
group by c.category_name
order by c.category_name

What can go wrong

• A single arrow next to = fails. DuckDB uses -> for lambdas too, and gives it a lower precedence than =. payload->'status' = 'paid' is read as payload->('status' = 'paid') and fails with No function matches ... 'json_extract(VARCHAR, BOOLEAN)'. ->> doesn't have this problem.
• JSON doesn't compare to text. Even in parentheses, (payload->'status') = 'paid' fails with Malformed JSON, because DuckDB converts 'paid' to JSON, and a bare word isn't JSON. Compare text with text: use ->>.
• :: binds before the arrow. payload->>'order_id'::integer casts the key, and fails with Could not convert string 'order_id' to INT32. Put the extraction in parentheses, then cast.
• ->> returns text, even for numbers. payload->>'amount' > '100' compares letter by letter, so '54.00' counts as greater. Cast before comparing, as with any number stored as text.
• Duplicate keys keep the first. '{"a": 1, "a": 2}'->>'a' is 1; other engines may keep the last.

with payments(payload) as (
  values ('{"order_id": 1, "status": "paid"}')
)
select *
from payments
where payload->'status' = 'paid'

select '{"order_id": 1}'->>'order_id'::integer as order_id

How other databases differ