Working with JSON data in Google Standard SQL

Posted on by

Create a table with a JSON column

You can create an empty table with a JSON column by using SQL or by using the bq command-line tool.

CREATE TABLE mydataset.table1(
  id INT64,
  cart JSON
);

Create JSON values

You can create JSON values in the following ways:

  • Use SQL to create a JSON literal.
  • Use the PARSE_JSON function to convert a string to a JSON type.
  • Use the TO_JSON function to convert a SQL type to a JSON type.

Create a JSON literal

The following example uses a DML statement to insert a JSON literal into a table:

INSERT INTO mydataset.table1
VALUES(1, JSON '{"name": "Alice", "age": 30}');
SELECT * FROM mydataset.table1
+----+---------------------------+
| id | cart                      |
+----+---------------------------+
| 1  | {"age":30,"name":"Alice"} |
+----+---------------------------+

Convert a string to JSON

The following example converts JSON data stored as a string to a JSON type, by using the PARSE_JSON function. The example converts a column from an existing table to a JSON type and stores the results to a new table.

Convert a SQL type to JSON

The following example converts a SQL STRUCT value to a JSON type, by using the TO_JSON function:

SELECT TO_JSON(STRUCT(1 AS id, [10,20] AS coordinates)) AS pt;

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 following ways:

Query JSON data

This section describes how to use Standard SQL to extract values from the JSON. JSON is case-sensitive and supports UTF-8 in both fields and values.

The examples in this section use the following table:

CREATE OR REPLACE TABLE mydataset.table1(id INT64, cart JSON);

INSERT INTO mydataset.table1 VALUES
(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 a JSON type in BigQuery, you can access the fields in a JSON expression by using the field access operator. The following example returns the name field of the cart column.

SELECT cart.name
FROM mydataset.table1;
+---------+
|  name   |
+---------+
| "Alice" |
| "Bob"   |
+---------+

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

SELECT cart.items[0] AS first_item
FROM mydataset.table1
+-------------------------------+
|          first_item           |
+-------------------------------+
| {"price":10,"product":"book"} |
| {"price":20,"product":"pen"}  |
+-------------------------------+
SELECT cart.items[1] AS first_item
FROM mydataset.table1
+-------------------------------+
|          first_item           |
+-------------------------------+
| {"price":5,"product":"food"}  |
| NULL                          |
+-------------------------------+

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

SELECT cart['name']
FROM mydataset.table1;
+---------+
|  name   |
+---------+
| "Alice" |
| "Bob"   |
+---------+

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

DECLARE int_val INT64 DEFAULT 0;

SELECT
  cart[CONCAT('it','ems')][int_val + 1].product AS item
FROM mydataset.table1;
+--------+
|  item  |
+--------+
| "food" |
| NULL   |
+--------+

Field access and subscript operators both return JSON types, so you can chain expressions that use them or pass the result to other functions that take JSON types.

These operators are syntactic sugar for the JSON_QUERY function. For example, the expression cart.name is equivalent to JSON_QUERY(cart, "$.name").

If a member with the specified name is not found in the JSON object, or if the JSON array doesn’t have an element with the specified position, then these operators return SQL NULL.

SELECT
  cart.address AS address,
  cart.items[1].price AS item1_price
FROM
  mydataset.table1;
+---------+-------------+
| address | item1_price |
+---------+-------------+
| NULL    | 5           |
| NULL    | NULL        |
+---------+-------------+

The equality and comparison operators are not defined on the JSON data type. Therefore, you can’t use JSON values directly in clauses like GROUP BY or ORDER BY. Instead, use the JSON_VALUE function to extract field values as SQL strings, as described in the next section.

Extract values as strings

The JSON_VALUE function extracts a scalar value and returns it as a SQL string. It returns SQL NULL if cart.name doesn’t point to a scalar value in the JSON.

SELECT JSON_VALUE(cart.name) AS name
FROM mydataset.table1;
+-------+
| name  |
+-------+
| Alice |
+-------+

You can use the JSON_VALUE function in contexts that require equality or comparison, such as WHERE clauses and GROUP BY clauses. The following example shows a WHERE clause that filters against a JSON value:

SELECT
  cart.items[0] AS first_item
FROM
  mydataset.table1
WHERE
  JSON_VALUE(cart.name) = 'Alice';
+-------------------------------+
| first_item                    |
+-------------------------------+
| {"price":10,"product":"book"} |
+-------------------------------+

ลอง WHERE โดยไม่ใช้ JSON_VALUE จะได้ error

SELECT
  cart.items[0] AS first_item
FROM
  mydataset.table1
WHERE
  cart.name = 'Alice';
No matching signature for operator = for argument types: JSON, STRING. Supported signature: ANY = ANY at [6:3]

Alternatively, you can use the STRING function which extracts a JSON string and returns that value as a SQL STRING. For example:

SELECT STRING(JSON '"purple"') AS color;
+--------+
| color  |
+--------+
| purple |
+--------+
SELECT STRING(JSON_QUERY(JSON '{"name": "sky", "color": "blue"}', "$.color")) AS color;
+--------+
| color  |
+--------+
| blue   |
+--------+

In addition to STRING, you might have to extract JSON values and return them as another SQL data type. The following value extraction functions are available:

To obtain the type of the JSON value, you can use the JSON_TYPE function.

Extract arrays from JSON

JSON can contain JSON arrays, which are not directly equivalent to an ARRAY<JSON> type in BigQuery. You can use the following functions to extract a BigQuery ARRAY from JSON:

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

The following example uses JSON_QUERY_ARRAY to extract JSON arrays.

SELECT JSON_QUERY_ARRAY(cart.items) AS items
FROM mydataset.table1;
+----------------------------------------------------------------+
| items                                                          |
+----------------------------------------------------------------+
| [{"price":10,"product":"book"}","{"price":5,"product":"food"}] |
| [{"price":20,"product":"pen"}]                                 |
+----------------------------------------------------------------+

To split an array into its individual elements, use the UNNEST operator, which returns a table with one row for each element in the array. The following example selects the product member from each member of the items array:

SELECT
  id,
  JSON_VALUE(item.product) AS product
FROM
  mydataset.table1, UNNEST(JSON_QUERY_ARRAY(cart.items)) AS item
ORDER BY id;
+----+---------+
| id | product |
+----+---------+
|  1 | book    |
|  1 | food    |
|  2 | pen     |
+----+---------+

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

SELECT
  id,
  ARRAY_AGG(JSON_VALUE(item.product)) AS products
FROM
  mydataset.table1, UNNEST(JSON_QUERY_ARRAY(cart.items)) AS item
GROUP BY id
ORDER BY id;
+----+-----------------+
| id | products        |
+----+-----------------+
|  1 | ["book","food"] |
|  2 | ["pen"]         |
+----+-----------------+

JSON nulls

The JSON type has a special null value that is different from the SQL NULL. A JSON null is not treated as a SQL NULL value, as the following example shows.

SELECT JSON 'null' IS NULL;
+-------+
| f0_   |
+-------+
| false |
+-------+

When you extract a JSON field with a null value, the behavior depends on the function:

  • The JSON_QUERY function returns a JSON null, because it is a valid JSON value.
  • The JSON_VALUE function returns the SQL NULL, because JSON null is not a scalar value.

The following example shows the different behaviors:

SELECT
  json.a AS json_query, -- Equivalent to JSON_QUERY(json, '$.a')
  JSON_VALUE(json, '$.a') AS json_value
FROM (SELECT JSON '{"a": null}' AS json);
+------------+------------+
| json_query | json_value |
+------------+------------+
| null       | NULL       |
+------------+------------+