Why Nested JSON in SQL Is So Painful

If you have ever exported data from Salesforce, NetSuite, or Zendesk into a database, you have probably stared at a JSON column and tried to write a WHERE clause against it.

PostgreSQL: jsonb_extract_path, ->>, and #>>

The -> and ->> Operators

SELECT id, payload ->> 'email' AS email
FROM salesforce_contacts
WHERE payload ->> 'account_tier' = 'enterprise';

Nested Paths with #>>

SELECT id,
  payload #>> '{contact,email}' AS contact_email,
  payload #>> '{account,name}' AS account_name
FROM salesforce_exports
WHERE payload #>> '{account,tier}' = 'enterprise';

Querying Arrays with jsonb_array_elements

SELECT DISTINCT t.id, t.subject
FROM zendesk_tickets t,
  jsonb_array_elements_text(t.payload -> 'tags') AS tag
WHERE tag = 'billing';

MySQL: JSON_EXTRACT and the ->> Operator

SELECT id, payload ->> '$.contact.email' AS email
FROM salesforce_contacts
WHERE payload ->> '$.account.tier' = 'enterprise';

NetSuite Line Items with JSON_TABLE

SELECT o.order_id, jt.sku, jt.quantity
FROM netsuite_orders o,
  JSON_TABLE(o.lines, '$[*]' COLUMNS (
    sku VARCHAR(50) PATH '$.sku',
    quantity INT PATH '$.quantity'
  )) AS jt
WHERE jt.quantity > 10;

SQL Server: JSON_VALUE and OPENJSON

SELECT id, JSON_VALUE(payload, '$.contact.email') AS email
FROM salesforce_contacts
WHERE JSON_VALUE(payload, '$.account.tier') = 'enterprise';

OPENJSON for Arrays

SELECT o.order_id, line.sku, line.quantity
FROM netsuite_orders o
CROSS APPLY OPENJSON(o.lines)
WITH (sku NVARCHAR(50) '$.sku', quantity INT '$.quantity') AS line
WHERE line.quantity > 10;

Common Mistakes That Return NULL

• Case sensitivity: JSON keys are case-sensitive

• Array index: Arrays are 0-indexed

• Wrong operator in PostgreSQL: -> returns JSON, ->> returns text

• Escaped quotes in MySQL: JSON_EXTRACT returns quoted strings

How AI2SQL Handles JSON Queries

AI2SQL lets you describe what you want in plain English and generates the correct JSON extraction query for your target database. You do not have to remember whether it is jsonb_array_elements_text or JSON_TABLE or OPENJSON.

Quick Reference

• PostgreSQL: payload ->> 'key' or payload #>> '{nested,key}'

• MySQL: payload ->> '$.nested.key'

• SQL Server: JSON_VALUE(payload, '$.nested.key')

When that happens, AI2SQL can save you the lookup time.