Working With JSON Data in SQL (PostgreSQL, MySQL, SQL Server)

Extracting a field: three databases, three syntaxes

Given a table with an event_properties JSON column containing {"plan_name": "pro", "seats": 5}:

-- PostgreSQL: ->> returns the value as text
SELECT event_properties ->> 'plan_name' AS plan_name
FROM events;

-- MySQL: JSON_EXTRACT (or the ->> shorthand introduced in 5.7.13+)
SELECT JSON_EXTRACT(event_properties, '$.plan_name') AS plan_name
FROM events;

-- SQL Server: JSON_VALUE for a scalar
SELECT JSON_VALUE(event_properties, '$.plan_name') AS plan_name
FROM events;

All three follow the same idea — a path expression pointing at a key — but the path syntax (bare key name vs. $.key) and the function name differ across engines.

Nested objects

-- PostgreSQL: -> keeps the result as JSON so it can be chained; ->> converts the final step to text
SELECT event_properties -> 'billing' ->> 'currency' AS currency
FROM events;

-- SQL Server / MySQL: dot-path notation handles nesting directly
SELECT JSON_VALUE(event_properties, '$.billing.currency') AS currency
FROM events;

In PostgreSQL, mixing up -> and ->> mid-path is the most common mistake — every step except the last one needs -> to stay in JSON form, or the chain breaks.

Expanding a JSON array into rows

A JSON array inside a column (like a list of items in one order event) needs to become one row per element before it can be aggregated or joined normally:

-- PostgreSQL
SELECT o.order_id, item ->> 'sku' AS sku, (item ->> 'qty')::INT AS qty
FROM orders o,
     jsonb_array_elements(o.line_items) AS item;

-- SQL Server
SELECT o.order_id, j.sku, j.qty
FROM orders o
CROSS APPLY OPENJSON(o.line_items)
  WITH (sku VARCHAR(50) '$.sku', qty INT '$.qty') AS j;

OPENJSON()'s WITH clause is doing double duty — expanding the array and shaping each element into typed columns in the same step, which jsonb_array_elements() handles separately via the ->> casts above.

JSON column type vs. plain TEXT

Storing JSON in a plain TEXT/VARCHAR column technically works — the extraction functions above generally accept text input — but a native JSON type has two real advantages: it validates the structure at insert time instead of silently storing malformed JSON, and in PostgreSQL specifically, JSONB supports indexing individual keys (via a GIN index), which a plain text column cannot.

A practical example: parsing an event log

-- PostgreSQL: daily count of "upgrade" events by plan tier
SELECT
  event_date,
  event_properties ->> 'new_plan' AS new_plan,
  COUNT(*) AS upgrade_count
FROM events
WHERE event_name = 'plan_upgraded'
  AND event_properties ->> 'new_plan' IS NOT NULL
GROUP BY event_date, event_properties ->> 'new_plan'
ORDER BY event_date;

This is the pattern behind most event-based analytics tables: extract the one or two fields a specific report needs, filter out rows where that field is missing, and group as usual — the JSON structure disappears once the relevant values are pulled out.

Common mistakes

• Using -> where ->> was needed (or vice versa) in PostgreSQL — comparing a JSON-typed value to a plain string with = often silently returns no rows instead of erroring.
• Assuming every row has the same JSON structure — event logs especially tend to add and remove fields over time; extraction functions return NULL for a missing key rather than failing, so a NULL doesn't always mean the event is invalid.
• Extracting values as text and comparing them as numbers without casting — (event_properties ->> 'seats')::INT is needed before numeric comparisons or math in PostgreSQL, since ->> always returns text.
• Storing JSON as TEXT and expecting index performance equivalent to a native JSON/JSONB column with a GIN index.