Read JSON and nested values

TutorialApplies to: Unified engine deployments

Extract useful scalar values from JSON, arrays, and structs.

Before you start

You need Workbench and a running SQL engine. Every example includes its own input rows. No catalog, files, credentials, or external data is needed. These queries create no stored tables. Copy the SQL into Workbench and run the statements in order. Compare the result with the table below the query. WITH names input data for one statement only. Each example can run on its own.

1. Read a nested JSON field

Who is named in each payload?

WITH payloads(id, body) AS (VALUES (1, '{"customer":{"name":"Ada"},"amount":20}'), (2, '{"customer":{"name":"Ben"},"amount":null}'))
SELECT id, JSON_EXTRACT_STRING(body, '$.customer.name') AS name FROM payloads ORDER BY id;

Expected result

id	name
1	Ada
2	Ben

Use a literal object path. This example reads JSON text. Change one thing: Use $.missing as the path. Both results are NULL.

2. Convert a JSON number

What are the numeric amounts?

WITH payloads(id, body) AS (VALUES (1, '{"customer":{"name":"Ada"},"amount":20}'), (2, '{"customer":{"name":"Ben"},"amount":null}'))
SELECT id, TRY_CAST(JSON_EXTRACT_STRING(body, '$.amount') AS INT) AS amount FROM payloads ORDER BY id;

Expected result

id	amount
1	20
2	NULL

JSON_EXTRACT_STRING returns text. Cast it for numeric use. Change one thing: Wrap the cast in COALESCE(…, 0). ID 2 returns zero.

3. Find a missing JSON value

Which payload has no amount?

WITH payloads(id, body) AS (VALUES (1, '{"customer":{"name":"Ada"},"amount":20}'), (2, '{"customer":{"name":"Ben"},"amount":null}'))
SELECT id FROM payloads WHERE JSON_EXTRACT_STRING(body, '$.amount') IS NULL ORDER BY id;

Expected result

id
2

A missing field and a JSON null both return SQL NULL. Change one thing: Use IS NOT NULL. Only ID 1 appears.

4. Count array values

How many tags does each item have?

WITH items(id, tags) AS (VALUES (1, ARRAY('books', 'sale')), (2, ARRAY('games')))
SELECT id, ARRAY_LENGTH(tags) AS tags FROM items ORDER BY id;

Expected result

id	tags
1	2
2	1

The result is a scalar count rather than an array column. Change one thing: Add a third tag to item 1. Its count becomes 3.

5. Test array membership

Which item has a sale tag?

WITH items(id, tags) AS (VALUES (1, ARRAY('books', 'sale')), (2, ARRAY('games')))
SELECT id FROM items WHERE ARRAY_CONTAINS(tags, 'sale') ORDER BY id;

Expected result

id
1

ARRAY_CONTAINS checks whether a value occurs in an array. Change one thing: Search for games. Only ID 2 appears.

6. Read a struct field

Can a structured record expose its name?

WITH people(id, name) AS (VALUES (1, 'Ada'), (2, 'Ben'))
SELECT id, GET_FIELD(STRUCT(id, name), 'name') AS name FROM people ORDER BY id;

Expected result

id	name
1	Ada
2	Ben

A struct groups named fields. GET_FIELD reads one field. Change one thing: Read id instead of name. Expect 1 and 2 in the second column.

Continue

Continue to Solve complete analysis problems, or return to Learn Laminar.

Transformation Engine

Learn Laminar

Workloads

SQL reference

HTTP API

Recipes

Operations

Read JSON and nested values

Before you start

1. Read a nested JSON field

2. Convert a JSON number

3. Find a missing JSON value

4. Count array values

5. Test array membership

6. Read a struct field

Continue

​Before you start

​1. Read a nested JSON field

​2. Convert a JSON number

​3. Find a missing JSON value

​4. Count array values

​5. Test array membership

​6. Read a struct field

​Continue

Before you start

1. Read a nested JSON field

2. Convert a JSON number

3. Find a missing JSON value

4. Count array values

5. Test array membership

6. Read a struct field

Continue