This page describes how to work with JSON using Spanner.
The JSON data type is a semi-structured data type used for holding JSON (JavaScript Object Notation) data. The specifications for the JSON format are described in RFC 7159.
JSON is useful to supplement a relational schema for data that is sparse or has a loosely-defined or changing structure. However, the query optimizer relies on the relational model to efficiently filter, join, aggregate, and sort at scale. Queries over JSON will have fewer built-in optimizations and fewer affordances to inspect and tune performance.
Specifications
Spanner JSON type stores a normalized representation of the input JSON document.
- JSON can be nested to a maximum of 80 levels.
- Whitespace is not preserved.
- Comments are not supported. Transactions or queries with comments will fail.
- Members of a JSON object are sorted lexicographically.
- JSON array elements have their order preserved.
- If a JSON object has duplicate keys, only the first one is preserved.
- Primitive types (string, boolean, number, and null) have their type
and value preserved.
- String type values are preserved exactly.
- Number type values are preserved, but may have their textual
representation changed as a result of the normalization process. For
example, an input number of 10000 may have a normalized
representation of 1e+4. Number value preservation semantics are as
follows:
- Signed integers in the range of [INT64_MIN, INT64_MAX] are preserved.
- Unsigned integers in the range of [0, UINT64_MAX] are preserved.
- Double values that can be roundtripped from string to double to
string without precision loss are preserved. If a double value cannot
round trip in this manner, the transaction or query fails.
- For example,
SELECT JSON '2.2412421353246235436'
fails. - A functional workaround is
PARSE_JSON('2.2412421353246235436', wide_number_mode=>'round')
, which returnsJSON '2.2412421353246237'
.
- For example,
- Use the
TO_JSON()
,JSON_OBJECT()
, and theJSON_ARRAY()
functions to construct JSON documents in SQL. These functions implement the necessary quoting and escaping characters.
The maximum permitted size of the normalized document is 10 MB.
Nullability
JSON null
values are treated as SQL non-NULL.
For example:
SELECT (JSON '{"a":null}').a IS NULL; -- Returns FALSE
SELECT (JSON '{"a":null}').b IS NULL; -- Returns TRUE
SELECT JSON_QUERY(JSON '{"a":null}', "$.a"); -- Returns a JSON 'null'
SELECT JSON_QUERY(JSON '{"a":null}', "$.b"); -- Returns a SQL NULL
Encoding
JSON documents must be encoded in UTF-8. Transactions or queries with JSON documents encoded in other formats return an error.
Create a table with JSON columns
A JSON column can be added to a table when the table is created. JSON type values can be nullable.
CREATE TABLE Venues (
VenueId INT64 NOT NULL,
VenueName STRING(1024),
VenueAddress STRING(1024),
VenueFeatures JSON,
DateOpened DATE,
) PRIMARY KEY(VenueId);
Add and remove JSON columns from existing tables
A JSON column can also be added to and dropped from existing tables.
ALTER TABLE Venues ADD COLUMN VenueDetails JSON;
ALTER TABLE Venues DROP COLUMN VenueDetails;
The following sample shows how to add a JSON
column called VenueDetails
to
the Venues
table using the Spanner client libraries.
C++
C#
Go
Java
Node.js
PHP
Python
Ruby
Modify JSON data
The following sample shows how to update JSON
data using the
Spanner client libraries.
C++
To learn how to install and use the client library for Spanner, see Spanner client libraries.
To authenticate to Spanner, set up Application Default Credentials. For more information, see Set up authentication for a local development environment.
C#
To learn how to install and use the client library for Spanner, see Spanner client libraries.
To authenticate to Spanner, set up Application Default Credentials. For more information, see Set up authentication for a local development environment.
Go
To learn how to install and use the client library for Spanner, see Spanner client libraries.
To authenticate to Spanner, set up Application Default Credentials. For more information, see Set up authentication for a local development environment.
Java
To learn how to install and use the client library for Spanner, see Spanner client libraries.
To authenticate to Spanner, set up Application Default Credentials. For more information, see Set up authentication for a local development environment.
Node.js
To learn how to install and use the client library for Spanner, see Spanner client libraries.
To authenticate to Spanner, set up Application Default Credentials. For more information, see Set up authentication for a local development environment.
PHP
To learn how to install and use the client library for Spanner, see Spanner client libraries.
To authenticate to Spanner, set up Application Default Credentials. For more information, see Set up authentication for a local development environment.
Python
To learn how to install and use the client library for Spanner, see Spanner client libraries.
To authenticate to Spanner, set up Application Default Credentials. For more information, see Set up authentication for a local development environment.
Ruby
To learn how to install and use the client library for Spanner, see Spanner client libraries.
To authenticate to Spanner, set up Application Default Credentials. For more information, see Set up authentication for a local development environment.
Query JSON data
The following sample shows how to query JSON
data using the
Spanner client libraries.
C++
To learn how to install and use the client library for Spanner, see Spanner client libraries.
To authenticate to Spanner, set up Application Default Credentials. For more information, see Set up authentication for a local development environment.
C#
To learn how to install and use the client library for Spanner, see Spanner client libraries.
To authenticate to Spanner, set up Application Default Credentials. For more information, see Set up authentication for a local development environment.
Go
To learn how to install and use the client library for Spanner, see Spanner client libraries.
To authenticate to Spanner, set up Application Default Credentials. For more information, see Set up authentication for a local development environment.
Java
To learn how to install and use the client library for Spanner, see Spanner client libraries.
To authenticate to Spanner, set up Application Default Credentials. For more information, see Set up authentication for a local development environment.
Node.js
To learn how to install and use the client library for Spanner, see Spanner client libraries.
To authenticate to Spanner, set up Application Default Credentials. For more information, see Set up authentication for a local development environment.
PHP
To learn how to install and use the client library for Spanner, see Spanner client libraries.
To authenticate to Spanner, set up Application Default Credentials. For more information, see Set up authentication for a local development environment.
Python
To learn how to install and use the client library for Spanner, see Spanner client libraries.
To authenticate to Spanner, set up Application Default Credentials. For more information, see Set up authentication for a local development environment.
Ruby
To learn how to install and use the client library for Spanner, see Spanner client libraries.
To authenticate to Spanner, set up Application Default Credentials. For more information, see Set up authentication for a local development environment.
Restrictions
- JSON columns cannot be used in ORDER BY.
- JSON type columns do not support indexing. However, an index can be created on a generated column that extracts a scalar value from a JSON element.
In the example below, an index VenueMisc
is created on the generated column
Details
which extracts a scalar value from the JSON element VenueDetails
.
The editable json_path is a STRING value in
JSONPath format.
CREATE TABLE Venues (
VenueId INT64 NOT NULL,
VenueName STRING(1024),
VenueAddress STRING(1024),
DateOpened DATE,
VenueDetails JSON,
Details STRING(MAX) AS (JSON_VALUE(VenueDetails, json_path)) STORED
) PRIMARY KEY(VenueId);
CREATE INDEX VenueMisc ON Venues(Details);
References
- JSON data type
- JSON functions
- JSON operators: