Working with JSON data in GoogleSQL

This document describes how to create a table with aJSON column, insert JSONdata into a BigQuery table, and query JSON data.

BigQuery natively supports JSON data using theJSON data type.

JSON is a widely used format that allows for semi-structured data, because itdoes not require a schema. Applications can use a "schema-on-read" approach,where the application ingests the data and then queries based on assumptionsabout the schema of that data. This approach differs from theSTRUCT type inBigQuery, which requires a fixed schema that is enforced for allvalues stored in a column ofSTRUCT type.

By using theJSON data type, you can load semi-structured JSON intoBigQuery without providing a schema for the JSON data upfront.This lets you store and query data that doesn't always adhere to fixed schemasand data types. By ingesting JSON data as aJSON data type,BigQuery can encode and process each JSON field individually. Youcan then query the values of fields and array elements within the JSON data byusing the field access operator, which makes JSON queries intuitive and costefficient.

Limitations

• If you use abatch load job to ingestJSON data into a table, the source data must be in CSV, Avro, or JSON format.Other batch load formats are not supported.
• TheJSON data type has a nesting limit of 500.
• You can't uselegacy SQLto query a table that containsJSON types.
• Row-level access policies cannot be applied onJSON columns.

To learn about the properties of theJSON data type, seeJSON type.

Create a table with aJSON column

You can create an empty table with aJSON column by using SQL or by using thebq command-line tool.

bqmk--tablemydataset.table1id:INT64,cart:JSON

You can't partition or cluster a table onJSON columns, because the equalityand comparison operators are not defined on theJSON type.

CreateJSON values

You can createJSON values in the following ways:

• Use SQL to create aJSON literal.
• Use thePARSE_JSONfunction to convert aSTRING value to aJSON value.
• Use theTO_JSONfunction to convert a SQL value to aJSON value.
• Use theJSON_ARRAYfunction to create a JSON array from SQL values.
• Use theJSON_OBJECTfunction to create a JSON object from key-value pairs.

Create aJSON value

The following example insertsJSON values into a table:

INSERTINTOmydataset.table1VALUES(1,JSON'{"name": "Alice", "age": 30}'),(2,JSON_ARRAY(10,['foo','bar'],[20,30])),(3,JSON_OBJECT('foo',10,'bar',['a','b']));

Convert aSTRING type toJSON type

The following example converts a JSON-formattedSTRING value by using thePARSE_JSONfunction. The example converts a column from an existing table to aJSON typeand saves the results in a new table.

CREATEORREPLACETABLEmydataset.table_newAS(SELECTid,SAFE.PARSE_JSON(cart)AScart_jsonFROMmydataset.old_table);

TheSAFE prefixused in this example ensures that any conversion errors are returned asNULLvalues.

Convert schematized data to JSON

The following example converts key-value pairs to JSON using theJSON_OBJECTfunction.

WITHFruitsAS(SELECT0ASid,'color'ASk,'Red'ASvUNIONALLSELECT0,'fruit','apple'UNIONALLSELECT1,'fruit','banana'UNIONALLSELECT1,'ripe','true')SELECTJSON_OBJECT(ARRAY_AGG(k),ARRAY_AGG(v))ASjson_dataFROMFruitsGROUPBYid

The result is the following:

+----------------------------------+| json_data                        |+----------------------------------+| {"color":"Red","fruit":"apple"}  || {"fruit":"banana","ripe":"true"} |+----------------------------------+

Convert a SQL type toJSON type

The following example converts a SQLSTRUCT value to aJSON value by usingtheTO_JSONfunction:

SELECTTO_JSON(STRUCT(1ASid,[10,20]AScoordinates))ASpt;

The result is the following:

+--------------------------------+| pt                             |+--------------------------------+| {"coordinates":[10,20],"id":1} |+--------------------------------+

Ingest JSON data

You can ingest JSON data into a BigQuery table in the followingways:

• Use a batch load job to load intoJSON columns from the following formats.
  • CSV
  • Avro
  • JSON
• Use theBigQuery Storage Write API.
• Use the legacytabledata.insertAll streaming API

Load from CSV files

The following example assumes that you have a CSV file namedfile1.csv thatcontains the following records:

1,202,"""This is a string"""3,"{""id"": 10, ""name"": ""Alice""}"

Note that the second column contains JSON data that is encoded as a string. Thisinvolves correctly escaping the quotes for the CSV format. In CSV format, quotesare escaped by using the two character sequence"".

To load this file using the bq command-line tool, use thebq load command:

bqload--source_format=CSVmydataset.table1file1.csvid:INTEGER,json_data:JSONbqshowmydataset.table1LastmodifiedSchemaTotalRowsTotalBytes--------------------------------------------------------------22Dec22:10:32|-id:integer363|-json_data:json

Load from newline delimited JSON files

The following example assumes that you have a file namedfile1.jsonl thatcontains the following records:

{"id": 1, "json_data": 20}{"id": 2, "json_data": "This is a string"}{"id": 3, "json_data": {"id": 10, "name": "Alice"}}

To load this file using the bq command-line tool, use thebq load command:

bqload--source_format=NEWLINE_DELIMITED_JSONmydataset.table1file1.jsonlid:INTEGER,json_data:JSONbqshowmydataset.table1LastmodifiedSchemaTotalRowsTotalBytes--------------------------------------------------------------22Dec22:10:32|-id:integer363|-json_data:json

Use the Storage Write API

You can use theStorage Write API toingest JSON data. The following example uses the Storage Write APIPython client to write data into a table with a JSON data type column.

Define a protocol buffer to hold the serialized streaming data. The JSON datais encoded as a string. In the following example, thejson_col field holdsJSON data.

message SampleData {  optional string string_col = 1;  optional int64 int64_col = 2;  optional string json_col = 3;}

Format the JSON data for each row as aSTRING value:

row.json_col = '{"a": 10, "b": "bar"}'row.json_col = '"This is a string"' # The double-quoted string is the JSON value.row.json_col = '10'

Append the rows to the write stream as shown in thecode example.The client library handles serialization to protocol buffer format.

If you aren't able to format the incoming JSON data, you need to use thejson.dumps() method in your code. Here is an example:

import json...row.json_col = json.dumps({"a": 10, "b": "bar"})row.json_col = json.dumps("This is a string") # The double-quoted string is the JSON value.row.json_col = json.dumps(10)...

Use the legacy streaming API

The following example loads JSON data from a local file and streams it toa BigQuery table with a JSON data-type column namedjson_data using thelegacy streaming API.

fromgoogle.cloudimportbigqueryimportjson# TODO(developer): Replace these variables before running the sample.project_id='MY_PROJECT_ID'table_id='MY_TABLE_ID'client=bigquery.Client(project=project_id)table_obj=client.get_table(table_id)# The column json_data is represented as a JSON data-type column.rows_to_insert=[{"id":1,"json_data":20},{"id":2,"json_data":"This is a string"},{"id":3,"json_data":{"id":10,"name":"Alice"}}]# If the column json_data is represented as a String data type, modify the rows_to_insert values:#rows_to_insert = [#    {"id": 1, "json_data": json.dumps(20)},#    {"id": 2, "json_data": json.dumps("This is a string")},#    {"id": 3, "json_data": json.dumps({"id": 10, "name": "Alice"})}#]# Throw errors if encountered.# https://cloud.google.com/python/docs/reference/bigquery/latest/google.cloud.bigquery.client.Client#google_cloud_bigquery_client_Client_insert_rowserrors=client.insert_rows(table=table_obj,rows=rows_to_insert)iferrors==[]:print("New rows have been added.")else:print("Encountered errors while inserting rows:{}".format(errors))

For more information, seeStreaming data into BigQuery.

Query JSON data

This section describes how to use GoogleSQL to extract values fromJSON. JSON is case-sensitive and supports UTF-8 in both fields and values.

The examples in this section use the following table:

CREATEORREPLACETABLEmydataset.table1(idINT64,cartJSON);INSERTINTOmydataset.table1VALUES(1,JSON"""{        "name": "Alice",        "items": [            {"product": "book", "price": 10},            {"product": "food", "price": 5}        ]    }"""),(2,JSON"""{        "name": "Bob",        "items": [            {"product": "pen", "price": 20}        ]    }""");

Extract values as JSON

Given aJSON type in BigQuery, you can access the fields in aJSON expression by using thefield access operator.The following example returns thename field of thecart column.

SELECTcart.nameFROMmydataset.table1;

+---------+|  name   |+---------+| "Alice" || "Bob"   |+---------+

To access an array element, use theJSON subscript operator.The following example returns the first element of theitems array:

SELECTcart.items[0]ASfirst_itemFROMmydataset.table1

+-------------------------------+|          first_item           |+-------------------------------+| {"price":10,"product":"book"} || {"price":20,"product":"pen"}  |+-------------------------------+

You can also use the JSON subscript operator to reference the members of a JSONobject by name:

SELECTcart['name']FROMmydataset.table1;

+---------+|  name   |+---------+| "Alice" || "Bob"   |+---------+

For subscript operations, the expression inside the brackets can be anyarbitrary string or integer expression, including non-constant expressions:

DECLAREint_valINT64DEFAULT0;SELECTcart[CONCAT('it','ems')][int_val+1].productASitemFROMmydataset.table1;

+--------+|  item  |+--------+| "food" || NULL   |+--------+

Field access and subscript operators both returnJSON types, so you can chainexpressions that use them or pass the result to other functions that takeJSONtypes.

These operators improve readability for the basic functionality of theJSON_QUERYfunction. For example, the expressioncart.name is equivalent toJSON_QUERY(cart, "$.name").

If a member with the specified name is not found in the JSON object, or ifthe JSON array doesn't have an element with the specified position, thenthese operators return SQLNULL.

SELECTcart.addressASaddress,cart.items[1].priceASitem1_priceFROMmydataset.table1;

+---------+-------------+| address | item1_price |+---------+-------------+| NULL    | NULL        || NULL    | 5           |+---------+-------------+

The equality and comparison operators are not defined on theJSON data type.Therefore, you can't useJSON values directly in clauses likeGROUP BY orORDER BY. Instead, use theJSON_VALUE function to extract field values asSQL strings, as described in the next section.

Extract values as strings

TheJSON_VALUEfunction extracts a scalar value and returns it as a SQL string. It returns SQLNULL ifcart.name doesn't point to a scalar value in the JSON.

SELECTJSON_VALUE(cart.name)ASnameFROMmydataset.table1;

+-------+| name  |+-------+| Alice |+-------+

You can use theJSON_VALUE function in contexts that require equality orcomparison, such asWHERE clauses andGROUP BY clauses. The followingexample shows aWHERE clause that filters against aJSON value:

SELECTcart.items[0]ASfirst_itemFROMmydataset.table1WHEREJSON_VALUE(cart.name)='Alice';

+-------------------------------+| first_item                    |+-------------------------------+| {"price":10,"product":"book"} |+-------------------------------+

Alternatively, you can use theSTRINGfunction which extracts a JSON string and returns that value as a SQLSTRING.For example:

SELECTSTRING(JSON'"purple"')AScolor;

+--------+| color  |+--------+| purple |+--------+

In addition toSTRING,you might have to extractJSON values and return them as another SQL datatype. The following value extraction functions are available:

• STRING
• BOOL
• INT64
• FLOAT64

To obtain the type of theJSON value, you can use theJSON_TYPEfunction.

Flexibly convert JSON

You can convert aJSON value to a scalar SQL value flexiblywithLAX conversionfunctions.

The following example uses theLAX_INT64 functionto extract anINT64 value from aJSON value.

SELECTLAX_INT64(JSON'"10"')ASid;

+----+| id |+----+| 10 |+----+

In addition toLAX_INT64,you can convert to other SQL types flexibly to JSON with the followingfunctions:

• LAX_STRING
• LAX_BOOL
• LAX_INT64
• LAX_FLOAT64

Extract arrays from JSON

JSON can contain JSON arrays, which are not directly equivalent to anARRAY<JSON> type in BigQuery. You can use the followingfunctions to extract a BigQueryARRAY from JSON:

• JSON_QUERY_ARRAY:extracts an array and returns it as anARRAY<JSON> of JSON.
• JSON_VALUE_ARRAY:extracts an array of scalar values and returns it as anARRAY<STRING> ofscalar values.

The following example usesJSON_QUERY_ARRAY to extract JSON arrays:

SELECTJSON_QUERY_ARRAY(cart.items)ASitemsFROMmydataset.table1;

+----------------------------------------------------------------+| items                                                          |+----------------------------------------------------------------+| [{"price":10,"product":"book"}","{"price":5,"product":"food"}] || [{"price":20,"product":"pen"}]                                 |+----------------------------------------------------------------+

To split an array into its individual elements, use theUNNESToperator, which returns a table with one row for each element in the array. Thefollowing example selects theproduct member from each member of theitemsarray:

SELECTid,JSON_VALUE(item.product)ASproductFROMmydataset.table1,UNNEST(JSON_QUERY_ARRAY(cart.items))ASitemORDERBYid;

+----+---------+| id | product |+----+---------+|  1 | book    ||  1 | food    ||  2 | pen     |+----+---------+

The next example is similar but uses theARRAY_AGGfunction to aggregate the values back into a SQL array.

SELECTid,ARRAY_AGG(JSON_VALUE(item.product))ASproductsFROMmydataset.table1,UNNEST(JSON_QUERY_ARRAY(cart.items))ASitemGROUPBYidORDERBYid;

+----+-----------------+| id | products        |+----+-----------------+|  1 | ["book","food"] ||  2 | ["pen"]         |+----+-----------------+

For more information about arrays, seeWorking with arrays in GoogleSQL.

JSON nulls

TheJSON type has a specialnull value that is different from the SQLNULL. A JSONnull is not treated as a SQLNULL value, as the followingexample shows.

SELECTJSON'null'ISNULL;

+-------+| f0_   |+-------+| false |+-------+

When you extract a JSON field with anull value, the behavior depends on thefunction:

• TheJSON_QUERY function returns a JSONnull, because it is a validJSONvalue.
• TheJSON_VALUE function returns the SQLNULL, because JSONnull is not ascalar value.

The following example shows the different behaviors:

SELECTjson.aASjson_query,-- Equivalent to JSON_QUERY(json, '$.a')JSON_VALUE(json,'$.a')ASjson_valueFROM(SELECTJSON'{"a": null}'ASjson);

+------------+------------+| json_query | json_value |+------------+------------+| null       | NULL       |+------------+------------+