---
title: "BigQuery JSON_VALUE, JSON_QUERY and PARSE_JSON Explained"
canonical: "https://www.owox.com/blog/articles/bigquery-json-parsing-conversion-functions"
updated: "2026-10-06"
---

# BigQuery JSON functions: JSON\_VALUE, JSON\_QUERY and PARSE\_JSON explained

JSON\_VALUE returns a scalar as a string, JSON\_QUERY returns an object or array as JSON, and their \_ARRAY versions return arrays you can UNNEST. This guide runs one JSON document through each, compares them with the older JSON\_EXTRACT functions, and explains the 'must be a constant expression' error.

[Google BigQuery](/blog/topics/bigquery) · Updated October 6, 2026 · [Ievgen Krasovytskyi](/team/ievgen-krasovytskyi) · 5 min read

![BigQuery JSON functions: JSON\_VALUE, JSON\_QUERY and PARSE\_JSON explained](https://cdn.owox.com/www/content-marketing-article/bigquery-json-parsing-conversion-functions/thumbnail/1.png/public)

To read JSON in BigQuery you need four functions, and the choice depends on what sits at the path:

| At the path there is | Use | Returns |
| --- | --- | --- |
| a string, number or boolean | `JSON_VALUE` | `STRING` |
| an object or an array, kept as JSON | `JSON_QUERY` | JSON text |
| an array of scalars | `JSON_VALUE_ARRAY` | `ARRAY<STRING>` |
| an array of objects | `JSON_QUERY_ARRAY` | array of JSON |

`JSON_VALUE` on an object returns `NULL`, and so does a path that does not exist. Most “my JSON function returns NULL” questions end there.

Every result below is from a run in BigQuery on 6 October 2026, on this one document:

```
{
  "id": 7,
  "user": {"name": "ana", "tags": ["a", "b"]},
  "items": [
    {"sku": "x1", "qty": 2},
    {"sku": "y2", "qty": 1}
  ],
  "note": null,
  "a.b": "dot"
}
```

In the queries, `j` is a `STRING` column holding it.

**_Note:_** _Originally published in April 2025. Fully updated in October 2026 with one document used throughout, every result re-run, and a section on the constant expression error._

## JSON\_VALUE: get a scalar

```
SELECT
  JSON_VALUE(j, '$.user.name'),     -- ana
  JSON_VALUE(j, '$.id'),            -- 7
  JSON_VALUE(j, '$.items[0].sku'),  -- x1
  JSON_VALUE(j, '$.user'),          -- NULL
  JSON_VALUE(j, '$.missing'),       -- NULL
  JSON_VALUE(j, '$.note');          -- NULL
```

Three things to take from that:

*   **The result is always a `STRING`.** `$.id` comes back as the text `7`, not the number. Cast it: `SAFE_CAST(JSON_VALUE(j, '$.id') AS INT64)`.
*   **An object returns `NULL`.** `$.user` is an object, so `JSON_VALUE` has nothing scalar to return.
*   **You cannot tell a missing key from a JSON `null`.** Both return SQL `NULL`.

Array elements are addressed with `[0]`, counting from zero.

## JSON\_QUERY: get an object or array

```
SELECT
  JSON_QUERY(j, '$.user'),
  -- {"name":"ana","tags":["a","b"]}
  JSON_QUERY(j, '$.user.name');
  -- "ana"
```

`JSON_QUERY` returns whatever is at the path as JSON. Note the second result: on a string it returns the value **with its quotes**. If you compare that to `'ana'` the filter matches nothing. For scalars use `JSON_VALUE`.

## The Turning Point

OWOX Data Marts

See your first report built in real time. _15 minutes._

1.  Connect your data warehouse
2.  Pick your metrics
3.  Get a live Google Sheets report

In the time it takes to write a ticket. Then imagine never writing that ticket again.

[Book a Demo](/book-a-call)

We'll use your actual use case

## JSON\_VALUE\_ARRAY and JSON\_QUERY\_ARRAY

Arrays need the `_ARRAY` versions, which return a BigQuery array you can `UNNEST`.

```
SELECT
  JSON_VALUE_ARRAY(j, '$.user.tags'),
  -- [a, b]
  ARRAY_LENGTH(JSON_QUERY_ARRAY(j, '$.items'));
  -- 2
```

Use `JSON_VALUE_ARRAY` when the elements are scalars, `JSON_QUERY_ARRAY` when they are objects. To get one row per item:

```
SELECT
  JSON_VALUE(item, '$.sku') AS sku,
  SAFE_CAST(JSON_VALUE(item, '$.qty') AS INT64)
    AS qty
FROM t,
  UNNEST(JSON_QUERY_ARRAY(j, '$.items')) AS item;
-- x1, 2
-- y2, 1
```

That pattern, `UNNEST(JSON_QUERY_ARRAY(...))` and then `JSON_VALUE` on each element, covers most nested JSON. More on the array side in [array functions](/blog/articles/bigquery-array-functions).

## JSON\_EXTRACT\_SCALAR vs JSON\_VALUE

The older functions still work and you will see them in existing queries. They map one to one:

| Older | Current | Same result |
| --- | --- | --- |
| `JSON_EXTRACT_SCALAR` | `JSON_VALUE` | yes: `ana` |
| `JSON_EXTRACT` | `JSON_QUERY` | yes: `"ana"` |
| `JSON_EXTRACT_STRING_ARRAY` | `JSON_VALUE_ARRAY` | yes |
| `JSON_EXTRACT_ARRAY` | `JSON_QUERY_ARRAY` | yes |

The practical difference is how a key with special characters is written. The document above has a key called `a.b`:

```
SELECT
  JSON_VALUE(j, '$."a.b"'),            -- dot
  JSON_EXTRACT_SCALAR(j, "$['a.b']");  -- dot
```

The current functions use double quotes inside the path; the older ones use brackets and single quotes. Google’s [JSON functions reference](https://cloud.google.com/bigquery/docs/reference/standard-sql/json_functions) recommends the current names for new queries.

## “Argument 2 must be a constant expression”

The path has to be a literal. If you pass a column, BigQuery answers:

```
Argument 2 to JSON_EXTRACT must be a
constant expression
```

`JSON_VALUE` gives the same message with its own name. You hit this when the key you want is itself stored in a column. The way around it is to convert the string to the `JSON` type, which accepts a dynamic key in brackets:

```
SELECT
  key,
  STRING(PARSE_JSON(j)[key]) AS value
FROM (SELECT '{"a":"1","b":"2"}' AS j),
  UNNEST(['a', 'b']) AS key;
-- a, 1
-- b, 2
```

## PARSE\_JSON and the JSON type

BigQuery has a native `JSON` data type. `PARSE_JSON` converts a string into it, and then you can reach fields with dots:

```
SELECT
  PARSE_JSON(j).user.name,           -- "ana"
  STRING(PARSE_JSON(j).user.name),   -- ana
  INT64(PARSE_JSON(j).id),           -- 7
  JSON_TYPE(PARSE_JSON(j).id);       -- number
```

Dot access returns `JSON`, so a string still carries its quotes. Convert with `STRING()`, `INT64()`, `FLOAT64()` or `BOOL()`. These are strict: `INT64()` on a JSON string fails. The `LAX_` versions convert where they reasonably can:

```
SELECT LAX_INT64(PARSE_JSON('{"n":"12"}').n);
-- 12
```

`PARSE_JSON` stops the query on malformed input. On data you do not control, use `SAFE.PARSE_JSON`, which returns `NULL`:

```
SELECT SAFE.PARSE_JSON('{bad json');
-- NULL
```

If a column holds JSON you query often, storing it as `JSON` instead of `STRING` means it is parsed once, at load.

## JSON to string, and back

```
SELECT
  TO_JSON_STRING(PARSE_JSON(j).user),
  -- {"name":"ana","tags":["a","b"]}
  TO_JSON_STRING(STRUCT('ana' AS name, 3 AS n));
  -- {"name":"ana","n":3}
```

`TO_JSON_STRING` turns a `JSON` value, a struct or an array into JSON text. `TO_JSON` does the same but returns the `JSON` type. To go from JSON to a plain string value, use `JSON_VALUE` or `STRING()` as above. Functions that build and edit JSON are in [JSON data manipulation functions](/blog/articles/bigquery-json-data-manipulation-functions) and [creating JSON objects and arrays](/blog/articles/bigquery-json-objects-and-arrays).

## Why a JSON function returns NULL

In order of how often each is the cause:

1.  **The value is an object or array** and you used `JSON_VALUE`. Use `JSON_QUERY`.
2.  **The path is wrong.** Keys are case-sensitive, arrays start at `[0]`, and a key with a dot or space needs quotes.
3.  **The key is missing** in that row. Normal for event data.
4.  **The string is not valid JSON.** `JSON_VALUE('not json', '$.a')` returns `NULL`, not an error.
5.  **The value is a JSON `null`.**

Count them before you rely on the column: `COUNTIF(JSON_VALUE(j, '$.id') IS NULL)` tells you how many rows the extraction lost.

## Parse it once

JSON columns are where one dataset quietly becomes several. One query reads `$.user.name`, another `$.user.full_name` after a schema change, a third forgets the cast and compares `"7"` to `7`. Each returns rows, and the totals differ.

The steady fix is to extract the fields in one query, with the paths and casts written down, and have everything else read plain typed columns. In [OWOX Data Marts](https://www.owox.com/app-signup) that query is a Data Mart: your SQL, with a description and an owner, run in your warehouse. OWOX does not parse the JSON for you or guess the paths. It keeps the extraction you wrote as the one every report uses.

## Your New Normal

Turn your data into decisions.

Governed data marts give you the clean foundation ML needs to actually work.

*   No AI hallucinations
*   Analyst-governed definitions
*   Every number traces to SQL

[Get started free](https://www.owox.com/app-signup)

## FAQ

## Frequently Asked Questions

What are the LAX functions in BigQuery?

LAX\_INT64, LAX\_FLOAT64, LAX\_STRING and LAX\_BOOL convert a JSON value to a SQL type and tolerate a mismatch: LAX\_INT64 on the JSON string "12" returns 12. The strict versions, INT64() and the others, fail when the JSON type does not match.

How do I handle nested JSON arrays in BigQuery?

Turn the array into rows with UNNEST(JSON\_QUERY\_ARRAY(j, '$.items')) and read each element with JSON\_VALUE. For an array of plain values, JSON\_VALUE\_ARRAY returns an ARRAY<STRING> directly.

What is the difference between JSON\_QUERY and JSON\_VALUE in BigQuery?

JSON\_VALUE returns a scalar as a plain STRING. JSON\_QUERY returns whatever is at the path as JSON text, so an object stays an object and a string keeps its quotes. Use JSON\_VALUE for values and JSON\_QUERY for objects and arrays.

How do I extract a value from JSON in BigQuery?

JSON\_VALUE(json, '$.path') returns a string, number or boolean as a STRING. Cast the result when you need a number: SAFE\_CAST(JSON\_VALUE(j, '$.id') AS INT64). It returns NULL when the path holds an object or array, or does not exist.

What is the difference between JSON\_EXTRACT\_SCALAR and JSON\_VALUE?

They return the same result. JSON\_VALUE is the current name and JSON\_EXTRACT\_SCALAR the older one. They differ in how a key with special characters is written: JSON\_VALUE(j, '$."a.b"') against JSON\_EXTRACT\_SCALAR(j, "$\['a.b'\]").

## Who wrote this

![Ievgen Krasovytskyi](https://cdn.owox.com/www/webflow/68404586b341508a789a4aa5_.png/public)

[Ievgen Krasovytskyi](/team/ievgen-krasovytskyi) · Head of Marketing

Ievgen Krasovytskyi is the Head of Marketing at OWOX, leading strategy across content, SEO, product marketing, and AI-powered automation. With deep expertise in analytics infrastructure, data warehouses, and marketing technology, he builds systems that connect marketing performance to business outcomes. Ievgen writes about SaaS growth, analytics workflows, and the future of AI in marketing operations.

[LinkedIn](https://www.linkedin.com/in/ievgen-krasovytskyi-a38a1253/) · [All articles](/team/ievgen-krasovytskyi)

[Google BigQuery](/blog/topics/bigquery)

## Related articles

[Google BigQuery · BigQuery Date Functions: Syntax, Examples and Common Errors · October 6, 2026](/blog/articles/bigquery-date-functions)

[Google BigQuery · BigQuery Conversion Functions: Every Type-to-Type Conversion · October 6, 2026](/blog/articles/bigquery-conversion-functions)

[Google BigQuery · How to Connect GA4 to BigQuery: Setup, Limits and First Queries · October 6, 2026](/blog/articles/google-analytics-4-to-bigquery-guide)

[See all articles →](/blog/articles)

## Book a call

You are booked. The invitation is on its way to , with the calendar invite and the meeting link.

[Reschedule](#)

[Cancel](#)

## Schedule a meeting

You are booked. The invitation is on its way to , with the calendar invite and the meeting link.

[Reschedule](#)

[Cancel](#)

## Links

Pages this page links to, on this site and on docs.owox.com. Where the page has a Markdown twin, its address follows the link.

- [Google BigQuery](https://www.owox.com/blog/topics/bigquery) — /blog/topics/bigquery.md
- [Ievgen Krasovytskyi](https://www.owox.com/team/ievgen-krasovytskyi) — /team/ievgen-krasovytskyi.md
- [Book a Demo](https://www.owox.com/book-a-call) — /book-a-call.md
- [array functions](https://www.owox.com/blog/articles/bigquery-array-functions) — /blog/articles/bigquery-array-functions.md
- [JSON data manipulation functions](https://www.owox.com/blog/articles/bigquery-json-data-manipulation-functions) — /blog/articles/bigquery-json-data-manipulation-functions.md
- [creating JSON objects and arrays](https://www.owox.com/blog/articles/bigquery-json-objects-and-arrays) — /blog/articles/bigquery-json-objects-and-arrays.md
- [OWOX Data Marts](https://www.owox.com/app-signup)
- [Google BigQuery · BigQuery Date Functions: Syntax, Examples and Common Errors · October 6, 2026](https://www.owox.com/blog/articles/bigquery-date-functions) — /blog/articles/bigquery-date-functions.md
- [Google BigQuery · BigQuery Conversion Functions: Every Type-to-Type Conversion · October 6, 2026](https://www.owox.com/blog/articles/bigquery-conversion-functions) — /blog/articles/bigquery-conversion-functions.md
- [Google BigQuery · How to Connect GA4 to BigQuery: Setup, Limits and First Queries · October 6, 2026](https://www.owox.com/blog/articles/google-analytics-4-to-bigquery-guide) — /blog/articles/google-analytics-4-to-bigquery-guide.md
- [See all articles →](https://www.owox.com/blog/articles) — /blog/articles.md