JSON Processing in SQL

Advanced SQL

Master JSON in SQL

JSON is the lingua franca of APIs and semi-structured data. SQL databases provide powerful operators to extract, transform, aggregate, and index JSON — turning unstructured blobs into queryable, analyzable data.

PostgreSQL — ->, ->>, @?, GIN indexes for jsonb
BigQuery — JSON_EXTRACT_SCALAR, JSON_EXTRACT_ARRAY
SQL Server — JSON_VALUE, JSON_QUERY, OPENJSON
MySQL — JSON_EXTRACT, ->>, JSON_TABLE

In interviews, JSON questions test your ability to navigate nested structures, unnest arrays, and build aggregations from semi-structured sources.

What Is JSON in SQL?

SQL databases store JSON as text with special operators for extraction, transformation, and indexing. The two main types are:

PostgreSQL: json vs jsonb

Feature	`json`	`jsonb`
Storage	Raw text	Decomposed binary
Indexing	GIN index (slow)	GIN index (fast)
Operators	All	All + `@?`, `@@`
Performance	Slower	2-5x faster
Duplicate keys	Preserved	Removed (last wins)

Always use jsonb in PostgreSQL — it's faster for extraction and supports GIN indexing.

JSON Extraction Operators

PostgreSQL Operators

-- Basic JSON extraction
SELECT
  id,
  data->>'name' AS name,                    -- Text extraction
  data->'address'->>'city' AS city,          -- Nested extraction
  data->'preferences'->'notifications'->>'email' AS email_pref,
  (data->>'age')::INT AS age,                -- Type casting
  jsonb_array_length(data->'tags') AS tag_count
FROM users;

BigQuery Functions

-- BigQuery JSON extraction
SELECT
  id,
  JSON_EXTRACT_SCALAR(data, '$.name') AS name,
  JSON_EXTRACT_SCALAR(data, '$.address.city') AS city,
  JSON_EXTRACT(data, '$.tags') AS tags_json,
  JSON_EXTRACT_ARRAY(data, '$.orders') AS orders_array,
  ARRAY(
    SELECT JSON_EXTRACT_SCALAR(item, '$.name')
    FROM UNNEST(JSON_EXTRACT_ARRAY(data, '$.tags')) AS item
  ) AS tag_names
FROM `project.dataset.users`;

JSON Path Expressions

-- Advanced JSON path queries (PostgreSQL 12+)
SELECT
  id,
  data @? '$.orders[*]?(@.amount > 1000)' AS has_large_orders,
  jsonb_path_query_array(data, '$.tags[*]') AS all_tags,
  jsonb_path_query_first(data, '$.orders[0].items[0].name') AS first_item,
  jsonb_path_query(data, '$.orders[*]?(@.status == "pending")') AS pending_orders
FROM users;

JSON Aggregation

-- Aggregate rows INTO JSON array
SELECT
  department_id,
  jsonb_agg(
    jsonb_build_object(
      'employee_id', employee_id,
      'name', name,
      'salary', salary
    )
  ) AS employees,
  jsonb_agg(name ORDER BY salary DESC) AS names_by_salary
FROM employees
GROUP BY department_id;

-- Aggregate rows INTO JSON object (key-value map)
SELECT
  jsonb_object_agg(
    employee_id::TEXT,
    jsonb_build_object('name', name, 'salary', salary)
  ) AS employee_map
FROM employees
WHERE department_id = 1;

JSON Transformation

-- Transform JSON structure
SELECT
  id,
  jsonb_build_object(
    'full_name', data->>'first_name' || ' ' || data->>'last_name',
    'contact', jsonb_build_object(
      'email', data->>'email',
      'phone', data->'phone_numbers'->0
    ),
    'order_count', jsonb_array_length(data->'orders'),
    'total_spent', (
      SELECT SUM((item->>'amount')::NUMERIC)
      FROM jsonb_array_elements(data->'orders') AS item
    )
  ) AS transformed_data
FROM users;

JSON Array Processing

-- Unnest JSON array into rows
SELECT
  u.id,
  u.data->>'name' AS user_name,
  tag.value AS tag
FROM users u,
LATERAL jsonb_array_elements_text(u.data->'tags') AS tag(value);

-- Filter JSON array elements
SELECT
  u.id,
  jsonb_path_query_array(
    u.data->'orders',
    '$[*]?(@.amount > 100)'
  ) AS large_orders
FROM users u;

-- Aggregate JSON array values
SELECT
  u.id,
  (SELECT SUM((item->>'amount')::NUMERIC)
   FROM jsonb_array_elements(u.data->'orders') AS item
   WHERE item->>'status' = 'completed') AS total_completed
FROM users u;

JSON Merge and Patch

-- Merge two JSON objects (right overrides left)
SELECT jsonb_merge_patch(
  '{"name": "John", "age": 30}'::jsonb,
  '{"age": 31, "city": "NYC"}'::jsonb
);
-- Result: {"name": "John", "age": 31, "city": "NYC"}

-- Patch with null removal (null deletes key)
SELECT jsonb_merge_patch(
  '{"a": 1, "b": 2, "c": 3}'::jsonb,
  '{"b": null, "d": 4}'::jsonb
);
-- Result: {"a": 1, "c": 3, "d": 4}

JSON in UPDATE Statements

-- Update specific JSON path
UPDATE users
SET data = jsonb_set(data, '{address, city}', '"San Francisco"')
WHERE id = 101;

-- Add new key to JSON object
UPDATE users
SET data = data || '{"newsletter_subscribed": true}'::jsonb
WHERE data->>'status' = 'active';

-- Remove key from JSON object
UPDATE users
SET data = data - 'temp_field'
WHERE data ? 'temp_field';

JSON Indexing

-- GIN index for general JSON queries (fastest for containment)
CREATE INDEX idx_users_data ON users USING GIN (data);

-- Specific path index (B-tree on extracted value)
CREATE INDEX idx_users_email ON users ((data->>'email'));

-- Partial index for specific values
CREATE INDEX idx_active_users ON users ((data->>'status'))
WHERE data->>'status' = 'active';

⚠️

Performance Tip: Use jsonb instead of json in PostgreSQL. jsonb is stored in a decomposed binary format that supports GIN indexing and is significantly faster for queries. The json type requires re-parsing on every extraction.

Quiz: Test Your Knowledge

Follow-Up Questions

What's the difference between json and jsonb in PostgreSQL?
How would you index a JSON column for efficient range queries?
Explain the performance implications of using JSON vs normalized tables.
How do you handle schema evolution in JSON documents?
What's the best approach for querying deeply nested JSON structures?

Key Takeaways

Use jsonb over json — binary storage + GIN indexing = 2-5x faster
-> returns JSON, ->> returns text — use ->> for comparison/casting
GIN index on jsonb for @?, @@, ? operators; B-tree for ->> equality
jsonb_array_elements_text unnests text arrays; jsonb_array_elements unnests objects
jsonb_merge_patch for combining/patching JSON documents
BigQuery: Use JSON_EXTRACT_SCALAR for text, JSON_EXTRACT for JSON

JSON Processing in SQL — Extraction, Transformation, Indexing & Aggregation