This page describes how to work with the JSONB
data type when using Spanner.
JSONB
is a PostgreSQL data type used for holding semi-structured
data in the Spanner PostgreSQL dialect. JSONB
holds data
in JavaScript Object Notation (JSON) format, which follows the specification
described in RFC 7159
.
Specifications
The Spanner JSONB
data type stores a normalized representation of
the input document. This implies the following:
- Quotation marks and whitespace characters are not preserved.
- Comments are not supported. Transactions or queries with comments fail.
- Object keys are sorted first by key length and then lexicographically by the equivalent object key length. If there are duplicate object keys, only the last one is preserved.
- Primitive types (
string,boolean,number, andnull) have their type and value preserved.-
stringtype values are preserved exactly. - Trailing zeros are preserved. The output format for
numbertype values does not use scientific notation.
-
-
JSONBnullvalues are treated as SQL non-NULL. For example:SELECT null :: jsonb IS NULL ; -- Returns true SELECT 'null' :: jsonb IS NULL ; -- Returns false SELECT '{"a":null}' :: jsonb - > 'a' IS NULL ; -- Returns false SELECT '{"a":null}' :: jsonb - > 'b' IS NULL ; -- Returns true SELECT '{"a":null}' :: jsonb - > 'a' ; -- Returns a JSONB 'null' SELECT '{"a":null}' :: jsonb - > 'b' ; -- Returns a SQL NULL -
JSONB array element order is preserved.
Restrictions
The following restrictions apply with Spanner JSONB
:
- Arguments to the
to_jsonbfunction can be only from the PostgreSQL data types that Spanner supports. - Number type values can have 4,932 digits before the decimal point and 16,383 digits after the decimal point.
- The maximum permitted size of the normalized storage format is 10 MB.
-
JSONBdocuments must be encoded in UTF-8. Transactions or queries withJSONBdocuments encoded in other formats return an error.
Create a table with JSONB columns
You can add a JSONB
column to a table when you create the table.
CREATE
TABLE
Venues
(
VenueId
BIGINT
PRIMARY
KEY
,
VenueName
VARCHAR
(
1024
),
VenueAddress
VARCHAR
(
1024
),
VenueFeatures
JSONB
,
DateOpened
TIMESTAMPTZ
);
A sample VenueFeatures
JSONB
object follows:
{
"rating"
:
4.5
,
"capacity"
:
"1500"
,
"construction"
:"brick"
,
"tags"
:
[
"multi-cuisine"
,
"open-seating"
,
"stage"
,
"public address system"
]
}
Add and remove JSONB columns from existing tables
You can add a JSONB
column and drop it by using ALTER
statements as follows:
ALTER
TABLE
Venues
ADD
COLUMN
VenueDetails
JSONB
;
ALTER
TABLE
Venues
DROP
COLUMN
VenueDetails
;
The following sample shows how to add a JSONB
column called VenueDetails
to
the Venues
table using Spanner client libraries.
C++
C#
Go
Java
Node.js
PHP
Python
Ruby
Modify JSONB data
You can modify a JSONB
column just like any other column.
An example follows:
UPDATE
Venues
SET
VenueFeatures
=
'{"rating": 4.5, "tags":["multi-cuisine", "open-seating"] }'
WHERE
VenueId
=
1
;
The following sample shows how to update JSONB
data using
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 JSONB data by using secondary indexes and search indexes with your JSONB data. Spanner doesn't support using JSONB type columns as keys in secondary indexes.
Use secondary index
Secondary indexes are useful when filtering against scalar values within a JSONB document. To use secondary indexes with JSONB, create a generated column that extracts the relevant scalar data and casts it to an appropriate SQL data 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
BIGINT
GENERATED
ALWAYS
AS
((
VenueFeatures
-
>> 'capacity'
)
::
BIGINT
)
VIRTUAL
,
DateOpened
TIMESTAMPTZ
);
CREATE
INDEX
VenuesByCapacity
ON
Venues
(
VenueCapacity
);
SELECT
VenueName
FROM
Venues
WHERE
VenueCapacity
>
1000
;
Use search indexes
Search indexes are useful when you query against JSONB documents that are dynamic or varied. Unlike secondary indexes, you can create search indexes over any JSONB document stored in a JSONB 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
spanner
.
tokenlist
GENERATED
ALWAYS
AS
(
spanner
.
tokenize_jsonb
(
VenueDetails
))
VIRTUAL
HIDDEN
;
CREATE
SEARCH
INDEX
VenuesByVenueDetails
ON
Venues
(
VenueDetails_Tokens
);
SELECT
VenueName
FROM
Venues
WHERE
VenueDetails
@
>
'{"labels": ["large"], "open": {"Friday": true}}'
::
jsonb
;
For more information, see JSON search indexes .
Query JSONB data
You can query JSONB
columns based on the values of the underlying fields. The
following example extracts VenueId
and VenueName
from Venues
where VenueFeatures
has a rating
value greater than 3.5
.
SELECT
VenueId
,
VenueName
FROM
Venues
WHERE
(
VenueFeatures
-
>> 'rating'
)
::
FLOAT8
>
3.5
;
The following sample shows how to query JSONB
data using
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 .
Unsupported PostgreSQL JSONB features
The following open source PostgreSQL JSONB
features aren't supported on
Spanner JSONB
:
- Ordering, comparison, and aggregation
- PrimaryKey and ForeignKey
- Indexing, including the GIN index. You can use a Spanner search index instead, which accelerates the same JSONB operations as a GIN index. For more information, see Index JSON data .
- Altering a
JSONBcolumn to or from any other data type - Using parameterized queries with untyped JSONB parameters in tools that use the PostgreSQL wire protocol
-
Using coercion in the query engine. Unlike open source PostgreSQL, implicit coercion from
JSONBto text isn't supported. You must use explicit casting from theJSONBtype to match function signatures. For example:SELECT concat ( 'abc' :: text , '{"key1":1}' :: jsonb ); -- Returns error SELECT concat ( 'abc' :: text , CAST ( '{"key1":1}' :: jsonb AS TEXT )); -- This works SELECT 3 + CAST ( '5' :: jsonb AS INTEGER ); -- This works
