Creating and Managing JSON Objects and Arrays in BigQuery

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.

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 supports JSON as a native data type, 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, or sending data to APIs.

Syntax

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

‍

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.

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

‍

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, making it easier to represent structured data, such as user profiles, order details, or API-ready responses.

Syntax

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

‍

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.

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

‍

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

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

‍

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.

1SELECT 
2  TO_JSON(STRUCT(
3    OrderID, 
4    EventTimestamp, 
5    JSON_VALUE(EventData, "$.customer.name") AS customer_name, 
6    JSON_VALUE(EventData, "$.order.status") AS order_status
7  )) AS json_output
8FROM `owox-d-ikrasovytskyi-001.OWOX_Demo.Sales_Events_parsed`
9WHERE OrderID = 'ORD005';

‍

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.

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.

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

‍

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.

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

‍

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.

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

‍

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.

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

‍

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.

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

‍

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.

1SELECT 
2  OrderID,
3  ARRAY_AGG(
4    JSON_OBJECT(
5      "product_id", JSON_VALUE(item, '$.product_id'),
6      "quantity", JSON_VALUE(item, '$.quantity')
7    )
8  ) AS product_payload
9FROM `owox-d-ikrasovytskyi-001.OWOX_Demo.Sales_Events_parsed`,
10UNNEST(JSON_QUERY_ARRAY(EventData, '$.order.items')) AS item
11GROUP BY OrderID;

‍

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 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.

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

‍

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.

1SELECT 
2  OrderID,
3  TO_JSON(JSON_ARRAY(
4    JSON_VALUE(EventData, '$.order.total_invoice_amount'), 
5    0.15
6  )) AS json_float_array
7FROM 
8  owox-d-ikrasovytskyi-001.OWOX_Demo.Sales_Events_parsed
9WHERE 
10  OrderID IN ('ORD002', 'ORD005')

‍

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 to learn when and how to use each clause.

Dive deeper with this read

WHERE vs. HAVING vs. QUALIFY: SQL Filter Operators Explained

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. 

1SELECT 
2  OrderID,
3  TO_JSON(STRUCT(
4    JSON_VALUE(EventData, '$.customer.id') AS customer_id,
5    JSON_VALUE(EventData, '$.customer.name') AS customer_name,
6    STRUCT(
7      JSON_VALUE(EventData, '$.order.status') AS status,
8      JSON_QUERY(EventData, '$.order.delivery') AS delivery
9    ) AS order_info
10  )) AS nested_json_object
11FROM 
12  owox-d-ikrasovytskyi-001.OWOX_Demo.Sales_Events_parsed
13WHERE 
14  OrderID = 'ORD002'

‍

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() 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 – Use these to extract, manipulate, and aggregate JSON arrays. Essential for parsing fields like product lists or customer tags.
• Conversion Functions – Functions like CAST and SAFE_CAST help convert string or numeric fields into appropriate types for accurate querying and transformation.
• Navigation Functions – Includes UNNEST and related functions to flatten and query nested JSON arrays within columns.
• Window Functions – Calculate rankings, differences, and moving metrics across rows, often used in time-based or grouped JSON reports.
• 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 – 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 BI Reports Extension for BigQuery

OWOX BI Reports Extension 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.