Read a field from a JSON column

Pull data->name and a nested data.items[0].sku out of a JSON column. Each engine has its own operators.

-- PostgreSQL (json/jsonb):  ->  keeps JSON,  ->>  returns text
SELECT
  data ->> 'name'                AS name,
  data -> 'address' ->> 'zip'    AS zip,
  data #>> '{items,0,sku}'       AS first_sku   -- #>> = text at a path
FROM events;

-- MySQL  ( ->> is shorthand for JSON_UNQUOTE(JSON_EXTRACT(...)) )
SELECT
  data ->> '$.name'          AS name,
  data ->> '$.items[0].sku'  AS first_sku
FROM events;

-- SQLite (json1) and DuckDB
SELECT
  json_extract(data, '$.name')         AS name,
  json_extract(data, '$.items[0].sku') AS first_sku
FROM events;            -- DuckDB also supports  data ->> '$.name'

-- BigQuery
SELECT
  JSON_VALUE(data, '$.name')     AS name,        -- scalar -> STRING
  JSON_QUERY(data, '$.address')  AS address_json -- sub-object -> JSON
FROM events;

-- Snowflake (VARIANT column)
SELECT
  data:name::string          AS name,
  data:items[0].sku::string  AS first_sku
FROM events;

JSON vs text is the #1 gotcha. Postgres -> and BigQuery JSON_QUERY return JSON (a quoted "Ann"); you want the text forms — ->>, #>>, JSON_VALUE, JSON_UNQUOTE, or a ::string cast — when comparing to or displaying a plain value. In Snowflake, always cast (::string, ::int) off a VARIANT path, or you get a quoted variant back.