> ## Documentation Index
> Fetch the complete documentation index at: https://docs.e6data.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Read JSON and nested values

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

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?

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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?

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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?

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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?

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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?

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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?

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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](https://docs.e6data.com/transformation/learn/analysis-problems.md), or return to [Learn Laminar](https://docs.e6data.com/transformation/learn.md).


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.