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 .
Index JSON data
You can accelerate querying JSON data by using secondary indexes and search indexes with your JSON data. Spanner doesn't support using JSON type columns as keys in secondary indexes.
Use secondary index
Secondary indexes are useful when filtering against scalar values within a JSON document. To use secondary indexes with JSON, create a generated column that extracts the relevant scalar data and convert the data to an appropriate SQL type. You can then create a secondary index over this generated column. The index accelerates eligible queries that run against the generated column.
In the following example, you create a VenuesByCapacity
index that the
database uses to find the venues with capacities greater than 1000. Instead of
checking every row, Spanner uses the index to locate the relevant
rows, which improves query performance, especially for large tables.
ALTER
TABLE
Venues
ADD
COLUMN
VenueCapacity
INT64
AS
(
INT64
(
VenueDetails
.
capacity
));
CREATE
INDEX
VenuesByCapacity
ON
Venue
(
VenueCapacity
);
SELECT
VenueName
FROM
Venues
WHERE
VenueCapacity
>
1000
;
Use search indexes
Search indexes are useful when you query against JSON documents that are dynamic or varied. Unlike secondary indexes, you can create search indexes over any JSON document stored in a JSON column. The search index automatically adapts to variations across JSON documents, between different rows, and over time.
In the following example, you create a VenuesByVenueDetails
search index that
the database uses to find the venues with specific details such as size and
operating schedule. Instead of checking every row, Spanner uses
the index to locate the relevant rows, which improves query performance,
especially for large tables.
ALTER
TABLE
Venues
ADD
COLUMN
VenueDetails_Tokens
TOKENLIST
AS
(
TOKENIZE_JSON
(
VenueDetails
))
HIDDEN
;
CREATE
SEARCH
INDEX
VenuesByVenueDetails
ON
Venue
(
VenueDetails_Tokens
);
SELECT
VenueName
FROM
Venues
WHERE
JSON_CONTAINS
(
VenueDetails
,
JSON
'{"labels": ["large"], "open": {"Friday": true}}'
);
For more information, see JSON search indexes .
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
- You can't use JSON columns in an
ORDER BYclause. - You can't use JSON type columns as primary keys or as keys in secondary indexes. For more information, see Index JSON data .
