---
title: "Creating JSON Objects and Arrays in BigQuery (2025)"
canonical: "https://www.owox.com/blog/articles/bigquery-json-objects-and-arrays"
updated: "2025-08-08"
---

# Creating and Managing JSON Objects and Arrays in BigQuery

[Google BigQuery](/blog/topics/bigquery) · Updated August 8, 2025 · [Ievgen Krasovytskyi](/team/ievgen-krasovytskyi) · 19 min read

![Creating and Managing JSON Objects and Arrays in BigQuery](https://cdn.owox.ai/www/webflow/696a724ee4301228e63d5c31_JSON-OBJECTS-ARRAYS.png/public)

Working with nested or semi-structured data? BigQuery’s JSON functions make it simple to **turn raw SQL results into structured, usable formats**. Whether you’re preparing API responses, storing complex records, or building flexible pipelines, creating JSON arrays and objects directly in SQL can save hours of manual effort.

![Banner reading “JSON objects and arrays: creating and managing”, with the BigQuery logo.](https://cdn.owox.ai/www/webflow/68960e35175c450c668891cb_Frame-23137732-min.jpg/public)

In this article, we’ll show you how to create and manage JSON arrays and objects using built-in BigQuery functions like **JSON\_ARRAY, JSON\_OBJECT**, and **TO\_JSON**. You’ll learn how to use these functions, explore real-world examples, handle common errors, and apply best practices for efficient JSON construction in BigQuery.

## **What is JSON, and Why Use It in BigQuery?**

JSON (**JavaScript Object Notation**) is a widely used format for storing and sharing data. It uses key-value pairs and arrays to represent information, which makes it perfect for working with flexible or nested data that doesn’t always fit into a strict structure.

[BigQuery](https://www.owox.com/blog/articles/bigquery-everything-you-need-to-know/) supports JSON as a native [data type](https://www.owox.com/blog/articles/bigquery-top-essential-data-types/), allowing you **to load and query JSON** without defining a strict schema. This makes it easier to work with changing or unstructured data while keeping queries efficient and straightforward.

## **Overview of JSON Functions Used for Data Construction in BigQuery**

BigQuery provides built-in JSON functions that help you **turn SQL data into structured JSON formats**. These functions are useful for creating arrays, objects, and converting values to JSON, making it easier to prepare data for storage, APIs, or reporting.

### **JSON\_ARRAY**

When you want to group multiple values into a single JSON structure, JSON\_ARRAY is the function to use in BigQuery. It helps you build list-style JSON outputs directly from your **SQL** results, which is useful for reporting, [aggregation](https://www.owox.com/blog/articles/bigquery-aggregate-functions/), or sending data to **APIs**.

#### **Syntax**

```
JSON_ARRAY([value1, value2, ..., valueN])
```

```
JSON_OBJECT(key1, value1 [, key2, value2, ...])
```

```
TO_JSON(sql_value [, stringify_wide_numbers => { TRUE | FALSE }])
```

Here is the breakdown of each parameter:

*   **value1, value2, …, valueN**: These are the SQL values you want to include in your JSON array. Each one is added as a separate element in the array.
*   **Return Type**: The result is a **JSON array**, where each value is enclosed in square brackets (\[\]) and separated by commas.

#### **Example**

Suppose you want to **list all product names in a single JSON** array from order **ORD001**. The data is stored in the EventData column of the Sales\_Events table.

![Using BigQuery JSON\_QUERY\_ARRAY function to collect product names into a single JSON array from nested order items.](https://cdn.owox.ai/www/webflow/6895fa68abc3483fe899f5dc_AD_4nXcu8PFli1B9BF2g_A9zb9PJcUyB8KU4TINc3XFoiSGvGJYMhNK-D3p3smvYrj335OJbcgsQAzX7x7MIWleSrp660x5-QSdPJhl-cQzulkPky1NNTNf02gijpRNmSNc6ex8ehTnccg.png/public)

Here\*\*:\*\*

*   **FROM clause:** Queries the Sales\_Events\_parsed table. Uses UNNEST(JSON\_QUERY\_ARRAY(EventData, ‘$.order.items’)) AS item to flatten the items array from the EventData JSON column.
*   **WHERE OrderID = ‘ORD001’:** Filters the result to a specific order (order ID ORD001).
*   **JSON\_QUERY\_ARRAY(EventData, ‘$.order.items’):** Extracts the array of items from the order object in the nested JSON.
*   **UNNEST(…) AS item:** Treats each item in the items array as a separate row.
*   **JSON\_VALUE(item, ‘$.name’):** Extracts the name field (product name) from each item.
*   **ARRAY\_AGG(…):** Aggregates all product names from the order into a single array result.

This function is perfect for grouping values as arrays for reporting, API payloads, or nested analytics.

### **JSON\_OBJECT**

When you need to build a structured JSON output with named fields, JSON\_OBJECT is the function to use. It helps you create key-value pair JSON objects directly from your [SQL query](https://www.owox.com/blog/use-cases/google-bigquery-functions-overview/), making it easier to represent structured data, such as user profiles, order details, or API-ready responses.

#### **Syntax**

Here is the breakdown of each parameter:

*   **key1, key2, …**: String literals that define the keys in the resulting JSON object.
*   **value1, value2, …**: Corresponding SQL expressions or values for each key.
*   The result is a **JSON object** with key-value pairs like: {“key1”: value1, “key2”: value2}.

#### **Example**

Suppose you want to create a JSON object that contains a **customer’s name and the order status for OrderID ORD002**. The data is stored in the EventData column of the Sales\_Events table.

```
JSON_OBJECT(
    "customer_name", JSON_VALUE(EventData, "$.customer.name"),
    "order_status", JSON_VALUE(EventData, "$.order.status")
  ) AS order_summary
FROM `owox-d-ikrasovytskyi-001.OWOX_Demo.Sales_Events_parsed`
WHERE OrderID = 'ORD002';
```

```
SELECT
  ARRAY_AGG(JSON_VALUE(item, '$.name')) AS product_names_array
FROM `owox-d-ikrasovytskyi-001.OWOX_Demo.Sales_Events_parsed`,
UNNEST(JSON_QUERY_ARRAY(EventData, '$.order.items')) AS item
WHERE OrderID = 'ORD001';
```

![Using BigQuery JSON\_OBJECT function to build a structured JSON object with customer name and order status from nested fields.](https://cdn.owox.ai/www/webflow/6895fa68abc3483fe899f5d6_AD_4nXfHNosbu_l7iFojM8dYJACYjDiiYpOmKnUc8mYzO-KONkV4AfTfPc9r2tMC1n71wpLW7gd5Uza53EmWUE5YhoRQ1HGBkpN_EWKO95AOlCDZsbQiQ763iw6hOhU2spYRoaIsIT-1dQ.png/public)

Here\*\*:\*\*

*   **JSON\_VALUE(EventData, “$.customer.name”)** pulls the customer’s name.
*   **JSON\_VALUE(EventData, “$.order.status”)** pulls the order status.
*   **JSON\_OBJECT(…)** wraps both into a structured JSON object:
    {“customer\_name”: “Bob”, “order\_status”: “shipped”} 

Use JSON\_OBJECT when you want to build clean, readable JSON outputs from specific fields in your data.

### **TO\_JSON**

If you want to convert full rows or SQL values into structured JSON, TO\_JSON is the function you need. It’s useful when preparing data for JSON-based exports, APIs, or simply storing semi-structured data in a readable format.

#### **Syntax**

Here is the breakdown of each parameter:

*   **sql\_value**: A single value, expression, STRUCT, or entire row you want to convert into a JSON object.
*   **stringify\_wide\_numbers (optional)**: When set to TRUE, very large numbers are automatically converted to strings to avoid precision loss.

The result is a valid **JSON object** representation of the input SQL value.

#### **Example**

Suppose you want to **convert all fields from OrderID ORD005 into a JSON object** using the Sales\_Events dataset.

```
SELECT
  TO_JSON(STRUCT(
    OrderID,
    EventTimestamp,
    JSON_VALUE(EventData, "$.customer.name") AS customer_name,
    JSON_VALUE(EventData, "$.order.status") AS order_status
  )) AS json_output
FROM `owox-d-ikrasovytskyi-001.OWOX_Demo.Sales_Events_parsed`
WHERE OrderID = 'ORD005';
```

![BigQuery TO\_JSON function converting selected fields from a STRUCT into a JSON object for order ORD005.](https://cdn.owox.ai/www/webflow/6895fa68abc3483fe899f5e7_AD_4nXdTHHxHvUz7S4WJX4ttSU_R_VBaQ2mMk1os3Bwqf2ZalYZLAziRWnB2mEagLK_zVPykLK23yILJjUELaUngpMI11tDHgJWO3dtVitMuqisPEASlAumjU6MdAXTAWFXX6z_PwU1zXQ.png/public)

Here:

*   Builds a **STRUCT** from selected fields like **OrderID, EventTimestamp, customer.name, and order.status**.
*   **TO\_JSON(…)** converts that STRUCT into a complete JSON object. 

Use TO\_JSON when you want to serialize structured data into JSON format for easier integration, logging, or storage.

## 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](https://www.owox.com/demo)

We'll use your actual use case

## **Practical Use Cases for JSON Functions for Data Construction**

JSON functions in BigQuery aren’t just for formatting—they’re essential tools for transforming and preparing semi-structured data for real-world use. In this section, we’ll cover practical examples like API formatting, aggregation, and object creation. 

### **Creating a JSON Array Containing an Empty JSON\_Array**

Sometimes, you may need to represent an array that includes an empty array inside it—for example, to match an expected structure in an API response or to indicate no items were found. BigQuery makes this easy using JSON\_ARRAY.

**Example:**

Suppose you want to build a JSON array that includes an empty items list for an order like **ORD003**, where no products were added.

```
SELECT
  JSON_ARRAY(JSON_QUERY(EventData, '$.order.items')) AS nested_empty_array
FROM `owox-d-ikrasovytskyi-001.OWOX_Demo.Sales_Events_parsed`
WHERE OrderID = 'ORD003';
```

![Using BigQuery JSON\_ARRAY function to wrap an existing array into a nested JSON array.](https://cdn.owox.ai/www/webflow/6895fa68abc3483fe899f5df_AD_4nXeC4wp7YI-hFZuKk8BY75W55jrKMWGWwXfuKUEgHykL9_KAqcR1IGaHEQy5oiF2DOPn-zSmLAA157OypzzZ3Oxlwztx3azIlrjD83-LaKHaxnpldMRn6pSLn2WFvS5DJjDybBNQ.png/public)

**Here:**

*   **JSON\_QUERY(EventData, ‘$.order.items’)**: Extracts the items array from the order object. In ORD003, this array is empty: \[\].
*   **JSON\_ARRAY(…)**: Wraps that empty array inside another array, resulting in: \[\[\]\].

This structure is useful when an outer array must contain placeholders, even if the inner list is empty.

### **JSON\_ARRAY for API Integration**

APIs often expect data in clean JSON array formats, especially when sending lists like products, tags, or transactions. In BigQuery, JSON\_QUERY\_ARRAY and JSON\_VALUE help build these structured responses directly from your data without extra transformation steps.

**Example:**

Suppose you want to prepare a JSON array of all product names in **ORD005** to send as part of an **API request payload**. Each product is listed under the items array in the EventData field.

```
SELECT
  ARRAY_AGG(JSON_VALUE(item, '$.name')) AS api_product_list
FROM `owox-d-ikrasovytskyi-001.OWOX_Demo.Sales_Events_parsed`,
UNNEST(JSON_QUERY_ARRAY(EventData, '$.order.items')) AS item
WHERE OrderID = 'ORD005';
```

![Using BigQuery JSON\_QUERY\_ARRAY function to create a JSON array of product names from nested JSON fields for API payloads.](https://cdn.owox.ai/www/webflow/6895fa68abc3483fe899f5d9_AD_4nXdq5OfitsJNK18PsW80VI7ZhTSaY5DjhrJb-rh1kZ0w7lu47TIku7nCIXeIj63qsMa5m3HsQIt2jtMiLWm-zc8RUbiHKP4ck469ctfCv1KfXM6fUz0EKHa1PqGYlewzxD3vKuzhHA.png/public)

Here:

*   **JSON\_QUERY\_ARRAY(EventData, ‘$.order.items’)**: Extracts the items array from the JSON data.
*   **UNNEST(…) AS item**: Expands each item into a separate row for querying.
*   **JSON\_VALUE(item, ‘$.name’)**: Extracts the name of each product.
*   **ARRAY\_AGG(…)**: Combines the product names into a single JSON array suitable for API transmission.
     

For ORD005, this will return: \[“Printer”, “Paper”\]. 

This method is ideal for constructing JSON payloads when your API endpoint expects product lists, tags, or other grouped values in an array format.

### **Data Aggregation with JSON Arrays**

Combining multiple values into a single JSON array is a common method for organizing data to facilitate easier analysis and visualization. BigQuery’s JSON\_VALUE\_ARRAY function helps summarize repeated fields, such as customer tags or item names, into a single compact array.

**Example:**

Suppose you want to **aggregate all customer tags into a JSON array** for order **ORD001**, which has more than one tag.

```
SELECT
  OrderID,
  ARRAY_AGG(tag) AS aggregated_tags
FROM `owox-d-ikrasovytskyi-001.OWOX_Demo.Sales_Events_parsed`,
UNNEST(JSON_VALUE_ARRAY(EventData, '$.customer.tags')) AS tag
WHERE OrderID = 'ORD001'
GROUP BY OrderID;
```

![Using BigQuery ARRAY\_AGG function to aggregate customer tags into a JSON array from a nested JSON field.](https://cdn.owox.ai/www/webflow/6895fa68abc3483fe899f5f3_AD_4nXdJNWZkSPqEBUOmQqlrAfBxUjLFb0vEGgIkZ63SVfcSVvQfTaeTrhIv1Kx4v4i0d50G0M2FU2raebAbOH5XzbFj-s3_cU6yH0y32afkNWyGqJu7wrrJBgKnet4nRtcYCYq81lbTYg.png/public)

Here\*\*:\*\*

*   **JSON\_VALUE\_ARRAY(EventData, ‘$.customer.tags’):** Extracts the tag list from the customer object in the EventData JSON column.
*   **UNNEST(…) AS tag:** Turns the JSON array of tags into individual rows - one row per tag.
*   **ARRAY\_AGG(tag):** Groups the tags back into a single array for output.
*   **WHERE OrderID = ‘ORD001’:** Focuses the query on a real case in the dataset where multiple tags exist.
*   **GROUP BY OrderID:** Required when using ARRAY\_AGG after UNNEST to produce one result per order.

This query outputs a JSON array like \[“new”, “promo”\], making customer tags easily usable for personalization, analytics, or export.

### **Generating a JSON Object with Key-Value Pairs**

Creating a JSON object from SQL fields is useful when you want to return structured data in a compact format. With JSON\_OBJECT, you can build a clear key-value representation of selected fields - ideal for APIs, logs, or exporting simplified order summaries.

**Example:**

Suppose you want to generate a JSON object that includes the **OrderID, customer ID, and order status for order ORD002** from the Sales\_Events dataset.

```
SELECT
  JSON_OBJECT(
    "OrderID", OrderID,
    "CustomerID", JSON_VALUE(EventData, "$.customer.id"),
    "OrderStatus", JSON_VALUE(EventData, "$.order.status")
  ) AS order_info
FROM `owox-d-ikrasovytskyi-001.OWOX_Demo.Sales_Events_parsed`
WHERE OrderID = 'ORD002';
```

![BigQuery JSON\_OBJECT function constructing a structured JSON object combining SQL and JSON fields.](https://cdn.owox.ai/www/webflow/6895fa68abc3483fe899f5d3_AD_4nXdaJtmUD__JFda2U1_uFWyg40n4HwyNX_qSIjYL5eb3nbbdqxd9oml84u3HFHR_04xe-pNmjf4h6Bywcrbou2lJUPxUDKynuucuYynkv-nx41KjSLghLdFaifido-rs25Dl9GoW.png/public)

Here\*\*:\*\*

*   **“OrderID”, OrderID**: Inserts the SQL field OrderID as a key-value pair in the JSON object.
*   **“CustomerID”, JSON\_VALUE(…)**: Extracts and inserts the customer ID from the JSON column.
*   **“OrderStatus”, JSON\_VALUE(…)**: Adds the order status from the nested JSON.
*   **JSON\_OBJECT(…)**: Combines all the key-value pairs into one structured JSON object. 

### **Creating an Empty JSON Object in BigQuery**

There are cases where you need to insert or return a placeholder JSON object without any content. This is especially useful when you’re ingesting or storing data from APIs where certain fields are expected to be present, even if they’re currently empty.

**Example:**

Suppose you’re preparing to load JSON data into BigQuery and want to include an **empty order\_notes field** for **ORD003**, where no additional notes exist. You can create an empty JSON object as a placeholder.

```
SELECT
  OrderID,
  JSON_OBJECT() AS order_notes
FROM `owox-d-ikrasovytskyi-001.OWOX_Demo.Sales_Events_parsed`
WHERE OrderID = 'ORD003';
```

![BigQuery JSON\_OBJECT function generating an empty JSON object for a specific order.](https://cdn.owox.ai/www/webflow/6895fa68abc3483fe899f5c7_AD_4nXcMeBtfbaPhcophC1EVYLHFccsL3Gyvk9uT7h5vLQs3rlU3NkhwY4HkKFprJUkeq2SQKdwX8AWfa-RUB6SXz-vQCoYI20wetIuBrExggrnwrbNXMCYWnRKW64XJV04CTlyUFddZ8g.png/public)

Here\*\*:\*\*

*   **JSON\_OBJECT()**: When called without any key-value pairs, it produces an empty object: {}.
*   **AS order\_notes**: Assigns the empty object to a new field, which can later be filled or kept as-is.
*   **WHERE OrderID = ‘ORD003’:** Targets a specific order where no notes currently exist.

This technique ensures your JSON structure remains consistent, even when optional or future fields have no current values.

## **Advanced Use Cases for JSON Functions for Data Construction** 

BigQuery’s advanced JSON functions help manage complex structures, perform precise type conversions, and support cross-platform data sharing. In this section, we’ll cover how to apply these functions in real scenarios like nested object handling and JSON stringification.

### **Cross-Platform Data Transfer**

When you’re sending data between systems, like sharing order details with a shipping partner or syncing with another tool, JSON arrays are a common format. BigQuery lets you build these arrays right in SQL, making the process smooth and well-organized.

**Example:**

Suppose you want to generate a JSON array of product details (product ID and quantity) **for each order** to send to an external fulfillment system. Each item in the array represents a product from that order.

```
SELECT
  OrderID,
  ARRAY_AGG(
    JSON_OBJECT(
      "product_id", JSON_VALUE(item, '$.product_id'),
      "quantity", JSON_VALUE(item, '$.quantity')
    )
  ) AS product_payload
FROM `owox-d-ikrasovytskyi-001.OWOX_Demo.Sales_Events_parsed`,
UNNEST(JSON_QUERY_ARRAY(EventData, '$.order.items')) AS item
GROUP BY OrderID;
```

![BigQuery query using JSON\_QUERY\_ARRAY and JSON\_OBJECT to generate a JSON array of product details per order.](https://cdn.owox.ai/www/webflow/6895fa68abc3483fe899f5f0_AD_4nXeDNJ_uRjLRo-zTQ9PKwgFgsbXSluNhtnoKthLey6QaU2qbdnWDnJhv4t5ISBVVX5hAcgrMIzBbhvp7Gt0OaGM7Ep0UC68j520EF90NJn_kJzcGxBs3uY2rkBIk0AhFm41-E88K.png/public)

Here\*\*:\*\*

*   **JSON\_QUERY\_ARRAY(EventData, ‘$.order.items’)**: Extracts the array of items from each order.
*   **UNNEST(…)**: Flattens the items array, so each item can be accessed individually.
*   **JSON\_OBJECT(…)**: Creates a key-value JSON object for each item.
*   **JSON\_AGG(…)**: Collects all item objects into a single JSON array for each order.
*   **GROUP BY OrderID**: Ensures one JSON payload per order for external transmission. 

This helps format and organize order item details for transfer to any platform that accepts JSON inputs.

### **Converting Large Numerical Values to JSON Strings**

When working with large numeric values, especially in financial data, it’s important to ensure accuracy during JSON conversion. BigQuery’s TO\_JSON function safely turns these large numbers into JSON-compatible [strings](https://www.owox.com/blog/articles/string-functions-bigquery/) without losing precision.

**Example:**

Suppose you want to extract the **quantities** from the order and convert them into a JSON string format using TO\_JSON.

```
SELECT
  OrderID,
  TO_JSON(SUM(CAST(JSON_VALUE(item, '$.quantity') AS INT64))) AS json_total_quantity
FROM `owox-d-ikrasovytskyi-001.OWOX_Demo.Sales_Events_parsed`,
UNNEST(JSON_QUERY_ARRAY(EventData, '$.order.items')) AS item
WHERE OrderID IN ('ORD002', 'ORD005')
GROUP BY OrderID;
```

![BigQuery query using TO\_JSON to convert quantity into a JSON string from nested JSON data.](https://cdn.owox.ai/www/webflow/6895fa68abc3483fe899f5ea_AD_4nXe-xN9TT5CbzJ5o9t1RhvTRIQwiAh7EiCvAp1EgEOWRtCdZp2aixsEHWCIhTzagDNCW87ciuJREO3XAKoezy-rfSfAUkNXZhtZn0Yo-PUr99blI9FecQhXT7BvqXI2wrCpVB6ZRSw.png/public)

Here\*\*:\*\*

*   **JSON\_QUERY\_ARRAY(EventData, ‘$.order.items’):** Extracts the items array from the order object in the JSON column.
*   **UNNEST(…) AS item:** Flattens the items array, so each product becomes a separate row for processing.
*   **JSON\_VALUE(item, ‘$.quantity’):** Retrieves the quantity value from each item (as a string).
*   **CAST(… AS INT64):** Converts the extracted string value into a numeric type so it can be summed.
*   **SUM(…):** Adds up all quantities for the specified order — totals the number of products.
*   **TO\_JSON(…):** Converts the numeric result into a JSON-formatted string.
*   **WHERE OrderID IN (‘ORD002’, ‘ORD005’):** Filters the query to focus only on two orders for demonstration.
*   **GROUP BY OrderID:** Groups the results by OrderID so that one total is calculated per order.

Using TO\_JSON ensures that large numerical values, such as invoices, are accurately converted to JSON strings for API transfers or external system exports.

### **Converting Values to FLOAT64 in JSON Stringification**

When combining large integers and decimal values into a single JSON structure, BigQuery automatically converts them to a compatible type. This ensures the output is consistent and avoids precision issues. TO\_JSON handles this by promoting both values to FLOAT64.

**Example:**

Suppose you want to construct a **JSON array** that includes a large **INT64** invoice amount and a **FLOAT64** discount rate. BigQuery will implicitly convert both to FLOAT64.

```
SELECT
  OrderID,
  TO_JSON(JSON_ARRAY(
    JSON_VALUE(EventData, '$.order.total_invoice_amount'),
    0.15
  )) AS json_float_array
FROM
  owox-d-ikrasovytskyi-001.OWOX_Demo.Sales_Events_parsed
WHERE
  OrderID IN ('ORD002', 'ORD005')
```

![BigQuery query using TO\_JSON with JSON\_ARRAY to convert a large INT64 and a FLOAT64 into a unified FLOAT64 JSON array.](https://cdn.owox.ai/www/webflow/6895fa68abc3483fe899f5d0_AD_4nXecg_noccfv4Y9Us0-U9uQlvsRWm3GORBQOQPd89KQmXWPZAXPKe9xaNAT1p5DCyg1O6M4h8TLUL0e4kXzXfWCVBDmZK9lw2_isPD9BiMf9RZElIzwZZNDeMMxdMupoWJUNGk7yIQ.png/public)

Here\*\*:\*\*

*   **TO\_JSON**: Converts the entire array to a valid JSON string.
*   **JSON\_ARRAY(…)**: Combines the large integer from total\_invoice\_amount with a float value 0.15.
*   **Implicit Conversion**: Both numeric values are converted to FLOAT64 inside the JSON to maintain consistency.
*   **WHERE Clause**: Filters only the orders with total\_invoice\_amount added earlier (ORD002 and ORD005). 

When large integers and floats are mixed, BigQuery promotes them to FLOAT64 to ensure accurate and consistent JSON output, especially important for financial or scientific data structures.

_💡 Want to understand how to filter data effectively in BigQuery? Explore this guide on_ [_WHERE vs. HAVING vs. QUALIFY_](https://www.owox.com/blog/articles/bigquery-sql-where-vs-having-vs-qualify/) _to learn when and how to use each clause._

  [![WHERE vs. HAVING vs. QUALIFY: SQL Filter Operators Explained](https://cdn.owox.ai/www/webflow/679195ce4e90449d22d5902e_53045.png/public) ![](https://cdn.owox.ai/www/webflow/67ab2481c8b464fb967cfbe5_Rectangle-11.svg/public) Dive deeper with this read WHERE vs. HAVING vs. QUALIFY: SQL Filter Operators Explained](https://www.owox.com/blog/articles/bigquery-sql-where-vs-having-vs-qualify/)

### **Combining JSON Objects with Nested Structures in BigQuery**

Nested JSON structures are beneficial when working with complex data that naturally lends itself to a hierarchical structure. This is especially useful when representing customers, orders, and delivery details in a single, unified format for querying and reporting.

**Example:**

Suppose you’re preparing to export order data for ORD002, and you need to send a structured JSON object that includes the customer’s ID and name, along with nested fields for order status and delivery details. 

```
SELECT
  OrderID,
  TO_JSON(STRUCT(
    JSON_VALUE(EventData, '$.customer.id') AS customer_id,
    JSON_VALUE(EventData, '$.customer.name') AS customer_name,
    STRUCT(
      JSON_VALUE(EventData, '$.order.status') AS status,
      JSON_QUERY(EventData, '$.order.delivery') AS delivery
    ) AS order_info
  )) AS nested_json_object
FROM
  owox-d-ikrasovytskyi-001.OWOX_Demo.Sales_Events_parsed
WHERE
  OrderID = 'ORD002'
```

![BigQuery query using TO\_JSON with nested STRUCT to generate a hierarchical JSON object for customer and order details.](https://cdn.owox.ai/www/webflow/6895fa68abc3483fe899f5ed_AD_4nXf6x_k_mRvmwMD5lwKuXcFzx_p1sQaCQziKFjlOIIIj10Zg1InVuYNd_09CqiMNFiizL6xSBknB_vuU796r4fHaX37-bT5L2cMc2oTuin5K-DYsaCzioOMnc0XSqSiYQjOD5m5U.png/public)

Here\*\*:\*\*

*   **TO\_JSON(STRUCT(…)):** Converts the entire structured block into a nested JSON object.
*   **customer\_id and customer\_name:** Extracted from the JSON customer block.
*   **order\_info:** A nested structure that combines the order status and delivery object. 

This approach ensures your JSON output for ORD002 is cleanly structured with nested objects, making it easy to integrate with APIs or systems that expect complex hierarchies.

## **Handling Errors in JSON Data Construction**

When building JSON data in BigQuery, unexpected errors can occur if the input values or structure aren’t handled properly. This section covers common issues like null keys, mismatched pairs, and unsupported types, and how to avoid or fix them effectively.

### **JSON Key Cannot Be NULL**

⚠️ **Issue:** BigQuery does not allow NULL as a key in a JSON\_OBJECT. JSON keys must always be valid strings because a NULL key leads to confusion during parsing and data interpretation.

For example, the query **SELECT JSON\_OBJECT(NULL, 1**) will result in an error.

✅ **Advice:** To avoid this, use IFNULL() or COALESCE() to replace null keys with default values like “unknown” before constructing the JSON object.

### **Mismatch Between JSON Keys and Values**

⚠️ **Issue:** When using JSON\_OBJECT, BigQuery expects each key to have a matching value. If you provide an uneven number of keys and values, an error will be thrown.

For example, **SELECT JSON\_OBJECT(‘a’, 1, ‘b’)** fails because ‘b’ has no corresponding value.

 ✅ **Advice:** Always double-check that your keys and values are correctly paired to ensure the JSON object is valid and complete.

### **Unsupported Data Type in TO\_JSON**

⚠️ **Issue:** The TO\_JSON function in BigQuery cannot handle certain SQL data types like **GEOGRAPHY, BYTES, or INTERVAL**. If you try to convert these directly, it will result in an error.

✅ **Advice:** To fix this, convert the unsupported type into a compatible format, such as **STRING**, using **CAST()** or SAFE\_CAST() before applying TO\_JSON. This ensures the JSON string output remains valid and usable.

## **Best Practices for JSON Construction in BigQuery** 

Creating JSON in BigQuery is powerful, but it’s easy to make mistakes that affect performance or data quality. In this section, we’re covering simple tips to build JSON structures that are clean and error-free. 

### **Optimize Queries for Efficient JSON Construction**

When creating JSON in BigQuery, keep your queries simple and focused. Skip extra columns or complicated joins that just slow things down and cost more. It’s also smart to filter your data early to reduce what needs to be processed. This approach ensures faster execution, minimizes resource usage, and produces only the relevant output needed for JSON construction.

### **Avoid Redundant Nesting in JSON Structures**

Overly nested JSON structures can make your data harder to read, process, and debug. Deep hierarchies often add complexity without real benefit and may not be compatible with tools expecting flat or lightly nested formats. Aim for simplicity, and structure your JSON to include only the essential levels of nesting required for the use case.

### **Maintain Consistent Naming Conventions for JSON Keys**

Using uniform and descriptive key names helps maintain clarity across your datasets. Stick to lowercase with underscores (e.g., user\_id, order\_date) and avoid mixing styles like camelCase and snake\_case in the same structure. Consistent naming makes it easier for **teams to understand the data** and reduces errors during reporting or integration.

### **Handle NULL Values Gracefully in JSON Data**

NULL values can cause JSON functions to fail or produce incomplete outputs. For example, a NULL key in JSON\_OBJECT results in an error. Always include checks like **IFNULL() or** [**COALESCE**](https://www.owox.com/blog/articles/coalesce-operator-bigquery/)**()** to replace nulls with default or placeholder values. This ensures your JSON remains valid and doesn’t break downstream processes.

### **Validate JSON Structure Before Export or Integration**

Before sending your JSON to an API, app, or external system, make sure the format is correct. Invalid types, missing keys, or structural issues can cause failures during ingestion. Use **validation tools or test queries** to ensure your JSON is properly structured and ready to use.

## **Essential BigQuery Function Types for Working with JSON Data**

BigQuery offers a variety of functions that make it easier to work with nested, semi-structured, and JSON data. Here are the key types you’ll use most often:

*   [**Array Functions**](https://www.owox.com/blog/articles/bigquery-array-functions/) – Use these to extract, manipulate, and aggregate JSON arrays. Essential for parsing fields like product lists or customer tags.
*   [**Conversion Functions**](https://www.owox.com/blog/articles/bigquery-conversion-functions/) – Functions like CAST and SAFE\_CAST help convert string or numeric fields into appropriate types for accurate querying and transformation.
*   [**Navigation Functions**](https://www.owox.com/blog/articles/bigquery-navigation-functions/) – Includes UNNEST and related functions to flatten and query nested JSON arrays within columns.
*   [**Window Functions**](https://www.owox.com/blog/articles/bigquery-window-functions/) – Calculate rankings, differences, and moving metrics across rows, often used in time-based or grouped JSON reports.
*   [**Numbering Functions**](https://www.owox.com/blog/articles/bigquery-numbering-functions/) – Assign sequence numbers or percentiles to rows using functions such as ROW\_NUMBER, RANK, or NTILE, which are helpful in ordering JSON-derived results.
*   [**Timestamp Functions**](https://www.owox.com/blog/articles/bigquery-timestamp-functions/) – Work with date and time values extracted from JSON to compute durations, format timestamps, or filter by time windows.

## **Gain Deeper Insights with the OWOX BigQuery Data Marts**

[OWOX BigQuery Data Marts](https://workspace.google.com/marketplace/app/owox_bi_bigquery_reports/263000453832) makes it easier to work with your **BigQuery** data. You can run **SQL queries**, build charts, and transform results into JSON, all within Google Sheets. No need to switch between tools or wait for engineering help.

This extension is perfect for analysts and marketers who want **faster insights and cleaner reports**. With just a few clicks, you can analyze, visualize, and export structured JSON data directly from BigQuery to your workspace.

## 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 some best practices for working with JSON data in BigQuery?

Keep structures simple, use consistent naming for keys, optimize queries to reduce cost, validate JSON before export, and handle NULL values properly. These practices improve performance and ensure your JSON is reliable and usable.

How do I handle NULL values when constructing JSON in BigQuery?

Use IFNULL() or COALESCE() to replace nulls with default values. This prevents errors and ensures that your JSON output is valid, especially when working with JSON\_OBJECT keys or important fields.

What is the difference between JSON\_ARRAY and JSON\_OBJECT in BigQuery?

JSON\_ARRAY creates a list-like JSON structure from values, while JSON\_OBJECT builds a key-value pair structure. Use JSON\_ARRAY for unordered lists and JSON\_OBJECT for structured records with named fields.

What errors should I watch out for when constructing JSON data in BigQuery?

Common errors include using NULL as a JSON key, mismatched numbers of keys and values in JSON\_OBJECT, and passing unsupported data types to TO\_JSON. These issues can break queries or return invalid JSON.

How can I create a nested JSON object in BigQuery?

You can create a nested JSON object by combining JSON\_OBJECT and JSON\_ARRAY, or by using TO\_JSON with a STRUCT that contains other STRUCTs or arrays. This allows you to represent hierarchical data with multiple levels.

What are JSON functions in BigQuery used for?

JSON functions in BigQuery help you create, manipulate, and query JSON-formatted data directly using SQL. They’re commonly used to build structured outputs like arrays and objects, convert SQL values to JSON strings, and prepare data for APIs or external systems.

## Who wrote this

![Ievgen Krasovytskyi](https://cdn.owox.ai/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 Partitioned Tables: Complete Guide for 2025 · September 16, 2024](/blog/articles/bigquery-partitioned-tables)

[Google BigQuery · BigQuery Aggregates: Boost Your Data Analysis in 2025 · June 10, 2024](/blog/articles/bigquery-statistical-aggregate-functions)

[Google BigQuery · BigQuery DML Commands: A Complete Guide for Data Analysts · March 14, 2024](/blog/articles/bigquery-data-manipulation-language)

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

## References

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
- [BigQuery](https://www.owox.com/blog/articles/bigquery-everything-you-need-to-know) — /blog/articles/bigquery-everything-you-need-to-know.md
- [data type](https://www.owox.com/blog/articles/bigquery-top-essential-data-types) — /blog/articles/bigquery-top-essential-data-types.md
- [aggregation](https://www.owox.com/blog/articles/bigquery-aggregate-functions) — /blog/articles/bigquery-aggregate-functions.md
- [SQL query](https://www.owox.com/blog/use-cases/google-bigquery-functions-overview)
- [Book a Demo](https://www.owox.com/demo) — /demo.md
- [strings](https://www.owox.com/blog/articles/string-functions-bigquery) — /blog/articles/string-functions-bigquery.md
- [WHERE vs. HAVING vs. QUALIFY](https://www.owox.com/blog/articles/bigquery-sql-where-vs-having-vs-qualify) — /blog/articles/bigquery-sql-where-vs-having-vs-qualify.md
- [COALESCE](https://www.owox.com/blog/articles/coalesce-operator-bigquery) — /blog/articles/coalesce-operator-bigquery.md
- [Array Functions](https://www.owox.com/blog/articles/bigquery-array-functions) — /blog/articles/bigquery-array-functions.md
- [Conversion Functions](https://www.owox.com/blog/articles/bigquery-conversion-functions) — /blog/articles/bigquery-conversion-functions.md
- [Navigation Functions](https://www.owox.com/blog/articles/bigquery-navigation-functions) — /blog/articles/bigquery-navigation-functions.md
- [Window Functions](https://www.owox.com/blog/articles/bigquery-window-functions) — /blog/articles/bigquery-window-functions.md
- [Numbering Functions](https://www.owox.com/blog/articles/bigquery-numbering-functions) — /blog/articles/bigquery-numbering-functions.md
- [Timestamp Functions](https://www.owox.com/blog/articles/bigquery-timestamp-functions) — /blog/articles/bigquery-timestamp-functions.md
- [Get started free](https://www.owox.com/app-signup)
- [Google BigQuery · BigQuery Partitioned Tables: Complete Guide for 2025 · September 16, 2024](https://www.owox.com/blog/articles/bigquery-partitioned-tables) — /blog/articles/bigquery-partitioned-tables.md
- [Google BigQuery · BigQuery Aggregates: Boost Your Data Analysis in 2025 · June 10, 2024](https://www.owox.com/blog/articles/bigquery-statistical-aggregate-functions) — /blog/articles/bigquery-statistical-aggregate-functions.md
- [Google BigQuery · BigQuery DML Commands: A Complete Guide for Data Analysts · March 14, 2024](https://www.owox.com/blog/articles/bigquery-data-manipulation-language) — /blog/articles/bigquery-data-manipulation-language.md
- [See all articles →](https://www.owox.com/blog/articles) — /blog/articles.md