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.-
string
type values are preserved exactly. - Trailing zeros are preserved. The output format for
number
type values does not use scientific notation.
-
-
JSONB
null
values 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_jsonb
function 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.
-
JSONB
documents must be encoded in UTF-8. Transactions or queries withJSONB
documents 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 .
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 PostgreSQL JSONB
features are not supported on Spanner JSONB
:
- Ordering, comparison, and aggregation
- PrimaryKey and ForeignKey
- Indexing, including the GIN index. For more information, see Indexing .
- Altering a
JSONB
column to or from any other data type - Using parameterized queries with untyped JSONB parameters in tools that use the PostgreSQL wire protocol
-
Coercion in the query engine. Unlike Standard PostgreSQL, coercion from
JSONB
to text is not supported. Only validJSON
strings are coerced toJSONB
type to match function signatures. Examples:SELECT concat('abc'::text, '{"key1":1}'::jsonb); -- Returns error SELECT concat('abc'::text, CAST('{"key1":1}'::jsonb AS TEXT)); -- This works
Indexing
JSONB
columns do not support indexing. However, you can create an index on
a generated column to extract a scalar value from a JSONB
column.
CREATE TABLE Venues (
VenueId BIGINT PRIMARY KEY,
VenueName VARCHAR(1024),
VenueAddress VARCHAR(1024),
VenueFeatures JSONB,
TotalCapacity BIGINT GENERATED ALWAYS AS ((VenueFeatures->>'capacity')::BIGINT) STORED,
DateOpened TIMESTAMPTZ
);
CREATE INDEX VenuesByCapacity ON Venues(TotalCapacity);