Migrate from json_extract

-- Before
SELECT json_extract(data, '$.name') AS name FROM users;

-- After
SELECT jsonata('name', data) AS name FROM users;

-- Before
SELECT json_extract(data, '$.account.order.product') AS product FROM events;

-- After
SELECT jsonata('account.order.product', data) AS product FROM events;

-- Before
SELECT
  CASE WHEN json_extract(data, '$.status') = 'active'
       THEN json_extract(data, '$.name')
  END AS active_name
FROM users;

-- After
SELECT jsonata('status = "active" ? name', data) AS active_name FROM users;

-- Before
SELECT u.id,
  (SELECT SUM(j.value) FROM json_each(u.data, '$.amounts') AS j) AS total
FROM users u;

-- After
SELECT id, jsonata_query('$sum(amounts)', data) AS total FROM users;

-- Before
SELECT json_extract(data, '$.first') AS first,
       json_extract(data, '$.last') AS last,
       json_extract(data, '$.email') AS email,
       json_extract(data, '$.phone') AS phone
FROM contacts;

-- After
SELECT jsonata('{ "first": first, "last": last, "email": email, "phone": phone }', data)
FROM contacts;

-- Before: 5 CASE/WHEN/json_extract chains
SELECT json_extract(data, '$.id') AS id,
  CASE WHEN json_extract(data, '$.type') = 'premium'
       THEN json_extract(data, '$.rate') * 1.2
       ELSE json_extract(data, '$.rate') END AS adjusted_rate,
  CASE WHEN json_extract(data, '$.region') IN ('us', 'ca')
       THEN 'north_america' ELSE 'other' END AS market,
  CASE WHEN json_extract(data, '$.score') > 80 THEN 'high'
       WHEN json_extract(data, '$.score') > 50 THEN 'mid'
       ELSE 'low' END AS tier
FROM accounts;

-- After: one expression
SELECT jsonata_query('{
  "id": id,
  "adjusted_rate": type = "premium" ? rate * 1.2 : rate,
  "market": region in ["us", "ca"] ? "north_america" : "other",
  "tier": score > 80 ? "high" : score > 50 ? "mid" : "low"
}', data)
FROM accounts;

-- Before
SELECT o.id AS order_id, j.value AS item
FROM orders o, json_each(o.data, '$.items') AS j;

-- After
SELECT o.id AS order_id, item.value AS item
FROM orders o, jsonata_each('items', o.data) AS item;