SQL JSON — Basics of Extraction, Array Expansion, Containment

QUESTION 1

json and jsonb — Match two documents that mean the same thing

jsonbType castNormalization

Background ▼

PostgreSQL has two JSON types. json keeps the text exactly as it was inserted, while jsonb keeps a parsed binary form. The moment a value becomes jsonb, whitespace is squeezed out, keys are reordered into the internal order, and for duplicate keys only the last value survives.

SELECT * FROM table_name
WHERE  json_col::jsonb = '{"key": "value"}'::jsonb;  -- normalize both sides, then compare

The json type has no equality operator: json_col = '...'::json raises an "operator does not exist" error. Comparing as text with ::text fails differently — it misses rows whose whitespace or key order differs.

Problem ▼

From the api_logs table, retrieve the rows whose payload is semantically the same as {"status":"ok","code":200}. payload is a json column, and neither the formatting nor the key order is consistent. Return log_id, payload, sorted by log_id ascending.

Tables used

▸ api_logs

log_id	payload (json)
1	{"status": "ok", "code": 200}
2	{"code":200,"status":"ok"}
3	{"status":"ng","code":200,"status":"ok"}
4	{"status": "error", "code": 500}
5	{"status": "ok"}

Expected Output ▼

log_id	payload
1	{"status": "ok", "code": 200}
2	{"code":200,"status":"ok"}
3	{"status":"ng","code":200,"status":"ok"}

Model Answer ▼

SELECT
  log_id, payload
FROM   api_logs
WHERE  payload::jsonb = '{"code": 200, "status": "ok"}'::jsonb  -- normalize both sides
ORDER BY log_id;

/*
  Execution order (logical evaluation order):
  1. FROM api_logs                → Read 5 rows
  2. WHERE payload::jsonb = ...   → Normalize and match, narrow to 3 rows
  3. SELECT log_id, payload       → Select 2 columns (payload stays the original text)
  4. ORDER BY log_id              → Ascending by log_id
  */

Explanation (table transitions & key points) ▼

SELECT
  log_id, payload
FROM   api_logs
WHERE  payload::jsonb = '{"code": 200, "status": "ok"}'::jsonb
ORDER BY log_id;

LEGEND ▼

Rows read / loaded

① FROM api_logs ▼

FROM api_logsRead all 5 rows of api_logs. Because payload is a json column, the inserted text is kept verbatim. Note that log 3 carries the status key twice.

1 / 4

log_id	payload (json)
1	{"status": "ok", "code": 200}
2	{"code":200,"status":"ok"}
3	{"status":"ng","code":200,"status":"ok"}
4	{"status": "error", "code": 500}
5	{"status": "ok"}

All 5 rows read

LEARNING POINTS

Use jsonb when you compare or search: equality, the containment operator @> and GIN indexes are all features of jsonb. All json offers is storing the input text without changing a single character, which you choose when keeping the original matters, as in an audit log.

Normalization does three things at once: converting to jsonb absorbs whitespace differences, key-order differences and duplicate keys together. Put the other way round, do not use jsonb if you need to tell those three apart.

The last duplicate key wins: the status of log 3 becomes ok, not ng. The JSON specification leaves duplicate keys undefined, so extracting from a json value with a function also returns the last value.

ANTI-PATTERNS

Comparing as text: payload::text = '{"status":"ok","code":200}' silently drops formatting differences such as log 2. The result then depends on whether the application emitted spaces, which is a bug that is hard to reproduce.

Casting on every query: while the column stays json, no index can serve payload::jsonb. Store a column you search on as jsonb from the start, or build an expression index.

Field Notes: keep it in JSON, or promote it to a column?

Attributes whose schema is not settled, and the raw responses of an external API, are worth keeping as JSON. Items that always exist and are always searched on — status, tenant id, amount — belong in ordinary columns, where constraints, types, indexes and statistics all work. "Put everything in JSON and we will never be stuck" is a choice you notice months later, when the reporting queries turn slow. When the call is close, decide by asking whether it appears in the WHERE clause.

QUESTION 2

Arrow operators — Take a value as JSON or as text

jsonbType castText extraction

Background ▼

Two arrow operators extract a key. -> returns a JSON value (jsonb) and ->> returns text. For a string value, the former keeps the quotes and the latter drops them.

SELECT jsonb_col ->  'key'            AS as_json,  -- "value" (JSON value)
       jsonb_col ->> 'key'            AS as_text,  -- value   (text)
       (jsonb_col ->> 'num_key')::int AS as_num -- treat it as a number
FROM   table_name;

Numeric comparison needs ->> and a cast: jsonb_col -> 'num_key' >= 30 compares a jsonb value against an integer, which is not the numeric comparison you meant. Go through ->> to get text, then cast with ::int.

Problem ▼

From the users table, retrieve the members who are 30 years old or older. profile is a jsonb column. Return user_id, name, age, sorted by user_id ascending. Return name as text without quotes and age as a number.

Tables used

▸ users

user_id	profile (jsonb)
101	{"name": "Sato", "age": 34, "city": "Tokyo"}
102	{"name": "Suzuki", "age": 28, "city": "Osaka"}
103	{"name": "Takahashi", "age": 41, "city": "Tokyo"}
104	{"name": "Tanaka", "age": 30, "city": "Fukuoka"}

Expected Output ▼

user_id	name	age
101	Sato	34
103	Takahashi	41
104	Tanaka	30

Model Answer ▼

SELECT
  user_id,
  profile ->> 'name'       AS name,  -- text without quotes
  (profile ->> 'age')::int AS age   -- to a number by way of text
FROM   users
WHERE  (profile ->> 'age')::int >= 30
ORDER BY user_id;

/*
  Execution order (logical evaluation order):
  1. FROM users                     → Read 4 rows
  2. WHERE (profile->>'age')::int   → Line values up as numbers, keep 30 and over
  3. SELECT ->> to text and number  → Select 3 columns
  4. ORDER BY user_id               → Ascending by user_id
  */

Explanation (table transitions & key points) ▼

SELECT
  user_id,
  profile ->> 'name'       AS name,
  (profile ->> 'age')::int AS age
FROM   users
WHERE  (profile ->> 'age')::int >= 30
ORDER BY user_id;

LEGEND ▼

Rows read / loaded

① FROM users ▼

FROM usersRead all 4 rows of users. profile is a jsonb column holding the three keys name, age and city.

1 / 4

user_id	profile (jsonb)
101	{"name": "Sato", "age": 34, "city": "Tokyo"}
102	{"name": "Suzuki", "age": 28, "city": "Osaka"}
103	{"name": "Takahashi", "age": 41, "city": "Tokyo"}
104	{"name": "Tanaka", "age": 30, "city": "Fukuoka"}

All 4 rows read

LEARNING POINTS

The number of arrows is the returned type: the single arrow -> returns jsonb and the double arrow ->> returns text. Use -> to dig deeper and ->> to use the value.

Cast after ->>: (profile -> 'age')::int also works, but a direct cast from jsonb errors out when the value is not a number. Making the ->> form your default keeps the failure modes uniform.

A missing key gives NULL: neither operator raises an error when the key is absent — both return NULL. Used in a WHERE clause, that row is dropped silently.

ANTI-PATTERNS

Comparing magnitudes as text: profile ->> 'age' >= '30' is a string comparison, which orders values so that '9' > '30'. Rows still come back, so nothing looks wrong until you check the values.

Displaying the result of ->: putting profile -> 'name' straight on a screen shows the quoted "Sato". Display, concatenation and comparison all belong to ->>.

Field Notes: values inside JSON carry no type guarantee

An ordinary column rejects a value that does not fit int at insert time, but the inside of a JSON document happily stores "a string that should have been a number". (profile ->> 'age')::int fails the entire query the moment one such row appears. To fail safe, select rows with jsonb_typeof(profile -> 'age') = 'number' instead of casting blindly, or place a CHECK constraint on the writing side.

QUESTION 3

Path extraction — Point at a nested object and an array element

NestingPath syntaxNULL-safe

Background ▼

Arrow operators chain. Chaining walks down one level at a time, while the path operator #>> takes the whole route as an array. Array subscripts start at 0.

SELECT jsonb_col -> 'parent' ->> 'child'     AS chained,     -- walk down level by level
       jsonb_col #>> '{parent, child}'      AS by_path,     -- give the whole route
       jsonb_col #>> '{array_key, 0, name}' AS first_name   -- first array element
FROM   table_name;

A broken route still gives NULL: when a key along the route does not exist, the result is NULL rather than an error. As with the arrow operators, #> returns a JSON value and #>> returns text.

Problem ▼

From the orders table, extract the shipping city and the name of the first item. The city sits at customer.address.city, and the first item name is the name of element 0 of the items array. Return order_id, city, first_item, sorted by order_id ascending.

Tables used

▸ orders

order_id	detail (jsonb)
1	{"customer": {"id": 101, "address": {"city": "Tokyo"}}, "items": [{"name": "Mouse", "qty": 2}, {"name": "Keyboard", "qty": 1}]}
2	{"customer": {"id": 102, "address": {"city": "Osaka"}}, "items": [{"name": "Monitor", "qty": 4}]}
3	{"customer": {"id": 103}, "items": [{"name": "Mouse", "qty": 3}]}

Expected Output ▼

order_id	city	first_item
1	Tokyo	Mouse
2	Osaka	Monitor
3	NULL	Mouse

Model Answer ▼

SELECT
  order_id,
  detail #>> '{customer, address, city}' AS city,       -- NULL when the route breaks
  detail #>> '{items, 0, name}'           AS first_item  -- arrays start at 0
FROM   orders
ORDER BY order_id;

/*
  Execution order (logical evaluation order):
  1. FROM orders                  → Read 3 rows
  2. SELECT #>> follows the path  → Select 3 columns (NULL where the route is missing)
  3. ORDER BY order_id            → Ascending by order_id
  */

Explanation (table transitions & key points) ▼

SELECT
  order_id,
  detail #>> '{customer, address, city}' AS city,
  detail #>> '{items, 0, name}'          AS first_item
FROM   orders
ORDER BY order_id;

LEGEND ▼

Rows read / loaded

① FROM orders ▼

FROM ordersRead all 3 rows of orders. detail nests an object inside an object and also holds an array. Only order 3 has no address under customer.

1 / 3

order_id	detail (jsonb)
1	{"customer": {"id": 101, "address": {"city": "Tokyo"}}, "items": [ … ]}
2	{"customer": {"id": 102, "address": {"city": "Osaka"}}, "items": [ … ]}
3	{"customer": {"id": 103}, "items": [ … ]}

All 3 rows read

LEARNING POINTS

Chaining and paths are equivalent: detail -> 'customer' -> 'address' ->> 'city' and detail #>> '{customer, address, city}' give the same result. From three levels down, the path operator reads better.

An array subscript is part of the route: the 0 inside the path is read as an array subscript. Negative subscripts work too, and -1 points at the last element.

Returning NULL is the safe side: a broken route does not stop the query. But it also means you cannot tell "no value" from "no route", so use jsonb_path_exists or ? when you want to detect a missing required field.

ANTI-PATTERNS

Leaving the last step as ->: detail -> 'customer' -> 'address' -> 'city' returns the quoted JSON value "Tokyo". Making only the final step ->> or #>> is the standard shape.

Counting arrays from 1: SQL arrays start at 1, but JSON array subscripts start at 0. '{items, 1, name}' points at the second element, not the first, and gives NULL on order 3, which holds a single element.

Field Notes: do not give the first element a meaning

A design where "element 0 is the representative value" depends on the order things were written. The moment a change reorders the array, or an element is added or removed, the meaning of existing queries shifts silently. If you want to mark a representative, put a flag such as {"primary": true} on the element and select it by condition rather than by subscript. Keep subscripts for the case they suit: taking a first look at external data that arrives as JSON.

QUESTION 4

jsonb_array_elements — Expand a JSON array into rows and aggregate

Array expansionGROUP BYLATERAL

Background ▼

jsonb_array_elements is a set-returning function that gives back each element of a JSON array as one row. Placed in the FROM clause and joined sideways to the original row, it lets you aggregate the contents of an array like ordinary rows.

SELECT elem ->> 'key'
FROM   table_name t
CROSS JOIN LATERAL jsonb_array_elements(t.jsonb_col -> 'array_key') AS elem;
-- a 3-element array turns one original row into 3 rows

LATERAL declares the reference to the left: because the function argument uses a column of the table on the left, LATERAL is required. The comma join FROM t, jsonb_array_elements(...) means the same thing, but make the former your default since the intent reads more clearly. To treat the elements as an array of text, use jsonb_array_elements_text.

Problem ▼

Expand the items array inside detail of the orders table and compute the total quantity per product. Return item_name, total_qty, sorted by total_qty descending.

Tables used

▸ orders

order_id	detail (jsonb)
1	{"items": [{"name": "Mouse", "qty": 2}, {"name": "Keyboard", "qty": 1}]}
2	{"items": [{"name": "Monitor", "qty": 4}]}
3	{"items": [{"name": "Mouse", "qty": 3}]}

Expected Output ▼

item_name	total_qty
Mouse	5
Monitor	4
Keyboard	1

Model Answer ▼

SELECT
  item ->> 'name'              AS item_name,
  SUM((item ->> 'qty')::int) AS total_qty
FROM   orders o
CROSS JOIN LATERAL jsonb_array_elements(o.detail -> 'items') AS item  -- array into rows
GROUP BY item ->> 'name'
ORDER BY total_qty DESC;

/*
  Execution order (logical evaluation order):
  1. FROM orders o                      → Read 3 rows
  2. CROSS JOIN LATERAL jsonb_array_...  → Expand items into 4 rows
  3. GROUP BY item->>'name'             → 3 groups by product name
  4. SELECT SUM((item->>'qty')::int)    → Sum the quantity per group
  5. ORDER BY total_qty DESC            → Descending by total quantity
  */

Explanation (table transitions & key points) ▼

SELECT
  item ->> 'name'              AS item_name,
  SUM((item ->> 'qty')::int) AS total_qty
FROM   orders o
CROSS JOIN LATERAL jsonb_array_elements(o.detail -> 'items') AS item
GROUP BY item ->> 'name'
ORDER BY total_qty DESC;

LEGEND ▼

Rows read / loaded

① FROM orders ▼

FROM orders oRead all 3 rows of orders. The items arrays have lengths 2, 1 and 1, four elements in total.

1 / 4

order_id	detail -> 'items'
1	[{"name": "Mouse", "qty": 2}, {"name": "Keyboard", "qty": 1}]
2	[{"name": "Monitor", "qty": 4}]
3	[{"name": "Mouse", "qty": 3}]

All 3 rows read

LEARNING POINTS

Expansion is a join that adds rows: a set-returning function is "a join that turns one row into as many rows as the array is long". After the expansion these are ordinary rows, so WHERE, GROUP BY and JOIN all apply as usual.

Repeat the expression in GROUP BY: item ->> 'name' is not aggregated, so it must appear in GROUP BY. Grouping by the alias item_name also works in PostgreSQL, but writing the expression itself is more portable.

An empty array is not a missing key: a row whose items is the empty array [], and a row with no items key at all, both vanish from the result because this is a CROSS JOIN. To keep the original row, write LEFT JOIN LATERAL … ON true.

ANTI-PATTERNS

Expanding in the SELECT list: SELECT jsonb_array_elements(detail -> 'items') … is accepted, but the row count stops matching intuition once other set-returning functions or aggregates are mixed in. Expand in the FROM clause so that the growth in rows is visible in the syntax.

Counting original rows after expanding: the expansion turns order 1 into two rows, so COUNT(*) inflates the number of orders where COUNT(DISTINCT o.order_id) is meant. Stay aware that the meaning of a row changes across the expansion.

Field Notes: the cost of expanding on every run

Array expansion adds rows before it aggregates, so it grows heavier the more rows it touches. Only conditions that apply before the expansion can be served by an index, which is why the WHERE clause belongs on the table side. If you aggregate line items daily, it is more natural to normalize them into a child table, or to land the aggregate in a materialized view. JSON arrays suit data that comes along for the ride and is rarely aggregated.

QUESTION 5

Existence and containment — Filter JSON by a key and by a partial structure

Existence operatorContainment operatorGIN index

Background ▼

JSON filtering has operators of its own. ? tests whether a key exists at the top level, and @> tests whether the left side contains the structure on the right. Both are jsonb only.

SELECT * FROM table_name
WHERE  jsonb_col ? 'key'                          -- the key exists
  AND  jsonb_col @> '{"array_key": ["value"]}';  -- the array contains value

@> is a subset test: it is true as long as everything written on the right is satisfied. For arrays neither the order nor the element count matters, only that the value is contained. Objects behave the same way — keys you did not write on the right are ignored.

Problem ▼

From the products table, retrieve the products that have a color key and whose tags contain "sale". attrs is a jsonb column. Return product_id, color, sorted by product_id ascending.

Tables used

▸ products

product_id	attrs (jsonb)
1	{"color": "red", "size": "M", "tags": ["sale", "new"]}
2	{"color": "blue", "size": "L"}
3	{"size": "M", "tags": ["sale"]}
4	{"color": "red", "size": "S", "tags": ["new"]}
5	{"color": "green", "size": "M", "tags": ["limited", "sale"]}

Expected Output ▼

product_id	color
1	red
5	green

Model Answer ▼

SELECT
  product_id,
  attrs ->> 'color' AS color
FROM   products
WHERE  attrs ?  'color'                -- a color key at the top level
  AND  attrs @> '{"tags": ["sale"]}'  -- tags contains sale (order irrelevant)
ORDER BY product_id;

/*
  Execution order (logical evaluation order):
  1. FROM products                → Read 5 rows
  2. WHERE ? AND @>               → Narrow to 2 rows
  3. SELECT attrs->>'color'       → Select 2 columns
  4. ORDER BY product_id          → Ascending by product_id
  */

Explanation (table transitions & key points) ▼

SELECT
  product_id,
  attrs ->> 'color' AS color
FROM   products
WHERE  attrs ?  'color'
  AND  attrs @> '{"tags": ["sale"]}'
ORDER BY product_id;

LEGEND ▼

Rows read / loaded

① FROM products ▼

FROM productsRead all 5 rows of products. Product 2 has no tags key and product 3 has no color key. In product 5, sale is the second entry of tags.

1 / 3

product_id	attrs (jsonb)
1	{"color": "red", "size": "M", "tags": ["sale", "new"]}
2	{"color": "blue", "size": "L"}
3	{"size": "M", "tags": ["sale"]}
4	{"color": "red", "size": "S", "tags": ["new"]}
5	{"color": "green", "size": "M", "tags": ["limited", "sale"]}

All 5 rows read

LEARNING POINTS

? is top level only: attrs ? 'city' does not look inside nested objects. To check a key further down, descend to the object first, as in attrs -> 'customer' ? 'city'.

@> takes a whole structure: a nested condition can be written as the shape you are looking for, such as attrs @> '{"customer": {"address": {"city": "Tokyo"}}}'. However many conditions you add, it is still one operator.

A GIN index applies: with CREATE INDEX ON products USING GIN (attrs); the operators @>, ?, ?| and ?& are served by the index. Filtering through ->> is not accelerated by it, so choose the index to match the conditions you search on.

ANTI-PATTERNS

Matching an array as a substring of text: attrs ->> 'tags' LIKE '%sale%' works, but it also hits a different value such as "sale_end", and no index helps. Testing for an array element is what @> is for.

Testing key existence with IS NOT NULL: attrs ->> 'color' IS NOT NULL is also false when the value is JSON null ({"color": null}). Use ? when you need to distinguish "the key is there but its value is null".

Field Notes: the ? operator collides with placeholders

JDBC and many other drivers read ? as the marker for a bind variable. That makes attrs ? 'color' impossible to send as written, and you end up escaping it as ?? or rewriting it as the function form jsonb_exists(attrs, 'color'). This is exactly why every operator has a matching function — @> has jsonb_contains as well. For queries issued from an application, writing the function form from the start saves trouble when you port.