The JSONB
data type stores JSON (JavaScript Object Notation) data as a binary representation of the JSONB
value, which eliminates whitespace, duplicate keys, and key ordering. JSONB
supports GIN indexes.
Alias
In CockroachDB, JSON
is an alias for JSONB
.
JSONB
and JSON
are two different data types. In CockroachDB, the JSONB
/ JSON
data type is similar in behavior to the JSONB
data type in PostgreSQL.
Syntax
The syntax for the JSONB
data type follows the format specified in RFC8259. You can express a constant value of type JSONB
using an interpreted literal or a string literal annotated with type JSONB
.
There are six types of JSONB
values:
null
- Boolean
- String
- Number (i.e.,
decimal
, not the standardint64
) - Array (i.e., an ordered sequence of
JSONB
values) - Object (i.e., a mapping from strings to
JSONB
values)
Examples:
'[{"foo":"bar"}]'
'{"type": "account creation", "username": "harvestboy93"}'
'{"first_name": "Ernie", "status": "Looking for treats", "location" : "Brooklyn"}'
'{"prices" : [ { "05/01/2022" : 100.5 } , { "06/01/2022" : 101.5 } ]}'
Size
The size of a JSONB
value is variable, but we recommend that you keep values under 1 MB to ensure satisfactory performance. Above that threshold, write amplification and other considerations may cause significant performance degradation.
We strongly recommend adding size limits to all indexed columns, which includes columns in primary keys.
Values exceeding 1 MiB can lead to storage layer write amplification and cause significant performance degradation or even crashes due to OOMs (out of memory errors).
To add a size limit using CREATE TABLE
:
CREATE TABLE name (first STRING(100), last STRING(100));
To add a size limit using ALTER TABLE ... ALTER COLUMN
:
SET enable_experimental_alter_column_type_general = true;
ALTER TABLE name ALTER first TYPE STRING(99);
Operators
Operator | Description | Example |
---|---|---|
-> |
Access a JSONB field, returning a JSONB value. |
SELECT '[{"foo":"bar"}]'::JSONB->0->'foo'; = "bar"::JSONB |
->> |
Access a JSONB field, returning a string. |
SELECT '{"foo":"bar"}'::JSONB->>'foo'; = bar::STRING; |
@> |
Tests whether the left JSONB field contains the right JSONB field. |
SELECT ('{"foo": {"baz": 3}, "bar": 2}'::JSONB@>'{"foo": {"baz":3}}'::JSONB ); = true; |
>@ |
Tests whether the left JSONB field is contained by the right JSONB field. |
SELECT('{"bar":2}'::JSONB<@'{"foo":1, "bar":2}'::JSONB); = true; |
#> |
Access a JSONB field at the specified path, returning a JSONB value. |
SELECT '[{"foo":"bar"}]'::JSONB#>'{0,foo}'; = "bar"::JSONB |
#>> |
Access a JSONB field at the specified path, returning a string. |
SELECT '[{"foo":"bar"}]'::JSONB#>>'{0,foo}'; = bar::STRING |
? |
Does the key or element string exist within the JSONB value? | SELECT('{"foo":1, "bar":2}'::JSONB?'bar'); = true; |
?& |
Do all the key or element strings exist within the JSONB value? | SELECT('{"foo":1, "bar":2}'::JSONB?&array['foo','bar']); = true; |
?| |
Do any of the key or element strings exist within the JSONB value? | SELECT('{"foo":1, "bar":2}'::JSONB?|array['bar']); = true; |
For the full list of supported JSONB
operators, see Operators.
Functions
Function | Description |
---|---|
jsonb_array_elements(<jsonb>) |
Expands a JSONB array to a set of JSONB values. See Map a JSONB array field into rows. |
jsonb_build_object(<any_element>...) |
Builds a JSONB object out of a variadic argument list that alternates between keys and values. |
jsonb_each(<jsonb>) |
Expands the outermost JSONB object into a set of key-value pairs. See Retrieve key-value pairs from a JSONB field. |
jsonb_object_keys(<jsonb>) |
Returns sorted set of keys in the outermost JSONB object. See Retrieve the distinct keys from a JSONB field. |
jsonb_pretty(<jsonb>) |
Returns the given JSONB value as a STRING indented and with newlines. See Retrieve formatted JSONB data. |
jsonb_set(val: jsonb, path: string[], to: jsonb) |
Returns the JSON value pointed to by the variadic arguments. See Update an array element. |
For the full list of supported JSONB
functions, see JSONB functions.
Index JSONB
data
To index a JSONB
column you can use a GIN index or index an expression on the column.
Known limitations
You cannot use primary key, foreign key, and unique constraints on
JSONB
values.You cannot use comparison operators (such as
<
or>
) onJSONB
elements. For example, the following query does not work and returns an error:
SELECT '{"a": 1}'::JSONB -> 'a' < '{"b": 2}'::JSONB -> 'b';
ERROR: unsupported comparison operator: <jsonb> < <jsonb>
SQLSTATE: 22023
You cannot order queries using
JSONB
andJSON
-typed columns.If the execution of a join query exceeds the limit set for memory-buffering operations (i.e., the value set for the
sql.distsql.temp_storage.workmem
cluster setting), CockroachDB will spill the intermediate results of computation to disk. If the join operation spills to disk, and at least one of the columns is of typeJSON
, CockroachDB returns the errorunable to encode table key: *tree.DJSON
. If the memory limit is not reached, then the query will be processed without error.
Examples
This section shows how to create tables with JSONB
columns and use operators and functions to access and update JSONB
data. For the full list of operators and functions, see Operators and JSONB functions.
Create a table with a JSONB
column
> CREATE TABLE users (
profile_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
last_updated TIMESTAMP DEFAULT now(),
user_profile JSONB
);
> SHOW COLUMNS FROM users;
column_name | data_type | is_nullable | column_default | generation_expression | indices | is_hidden
---------------+-----------+-------------+-------------------+-----------------------+--------------+------------
profile_id | UUID | false | gen_random_uuid() | | {users_pkey} | false
last_updated | TIMESTAMP | true | now():::TIMESTAMP | | {users_pkey} | false
user_profile | JSONB | true | NULL | | {users_pkey} | false
(3 rows)
> INSERT INTO users (user_profile) VALUES
('{"first_name": "Lola", "last_name": "Dog", "location": "NYC", "online" : true, "friends" : 547}'),
('{"first_name": "Ernie", "status": "Looking for treats", "location" : "Brooklyn"}');
> SELECT * FROM users;
+--------------------------------------+----------------------------------+----------------------------------------------------------------------------------------------+
| profile_id | last_updated | user_profile |
+--------------------------------------+----------------------------------+----------------------------------------------------------------------------------------------+
| 33c0a5d8-b93a-4161-a294-6121ee1ade93 | 2022-02-27 16:39:28.155024+00:00 | {"first_name": "Lola", "friends": 547, "last_name": "Dog", "location":"NYC", "online": true} |
| 6a7c15c9-462e-4551-9e93-f389cf63918a | 2022-02-27 16:39:28.155024+00:00 | {"first_name": "Ernie", "location": "Brooklyn", "status": "Looking for treats} |
+--------------------------------------+----------------------------------+----------------------------------------------------------------------------------------------+
Retrieve formatted JSONB
data
To retrieve JSONB
data with easier-to-read formatting, use the jsonb_pretty()
function. For example, retrieve data from the table you created in the first example:
> SELECT profile_id, last_updated, jsonb_pretty(user_profile) FROM users;
+--------------------------------------+----------------------------------+------------------------------------+
| profile_id | last_updated | jsonb_pretty |
+--------------------------------------+----------------------------------+------------------------------------+
| 33c0a5d8-b93a-4161-a294-6121ee1ade93 | 2022-02-27 16:39:28.155024+00:00 | { |
| | | "first_name": "Lola", |
| | | "friends": 547, |
| | | "last_name": "Dog", |
| | | "location": "NYC", |
| | | "online": true |
| | | } |
| 6a7c15c9-462e-4551-9e93-f389cf63918a | 2022-02-27 16:39:28.155024+00:00 | { |
| | | "first_name": "Ernie", |
| | | "location": "Brooklyn", |
| | | "status": "Looking for treats" |
| | | } |
+--------------------------------------+----------------------------------+------------------------------------+
Retrieve a specific field from JSONB
data
To retrieve a specific field from JSONB
data, use the ->
operator. For example, retrieve a field from the table you created in Create a table with a JSONB
column, run:
> SELECT user_profile->'first_name',user_profile->'location' FROM users;
+----------------------------+--------------------------+
| user_profile->'first_name' | user_profile->'location' |
+----------------------------+--------------------------+
| "Lola" | "NYC" |
| "Ernie" | "Brooklyn" |
+----------------------------+--------------------------+
Use the ->>
operator to return JSONB
fields as STRING
values:
> SELECT user_profile->>'first_name', user_profile->>'location' FROM users;
+-----------------------------+---------------------------+
| user_profile->>'first_name' | user_profile->>'location' |
+-----------------------------+---------------------------+
| Lola | NYC |
| Ernie | Brooklyn |
+-----------------------------+---------------------------+
Use the @>
operator to filter the values in a field in a JSONB
column:
> SELECT user_profile->'first_name', user_profile->'location' FROM users WHERE user_profile @> '{"location":"NYC"}';
+-----------------------------+---------------------------+
| user_profile->>'first_name' | user_profile->>'location' |
+-----------------------------+---------------------------+
| Lola | NYC |
+-----------------------------+---------------------------+
Use the #>>
operator with a path to return all first names:
select user_profile#>>'{first_name}' as "first name" from users;
first name
--------------
Lola
Ernie
(2 rows)
Retrieve the distinct keys from a JSONB
field
SELECT DISTINCT jsonb_object_keys(user_profile) AS keys FROM users;
keys
--------------
first_name
friends
last_name
location
online
status
(6 rows)
Retrieve key-value pairs from a JSONB
field
SELECT jsonb_each(user_profile) AS pairs FROM users;
pairs
-------------------------------------
(first_name,"""Lola""")
(friends,547)
(last_name,"""Dog""")
(location,"""NYC""")
(online,true)
(first_name,"""Ernie""")
(location,"""Brooklyn""")
(status,"""Looking for treats""")
(8 rows)
Group and order JSONB
values
To organize your JSONB
field values, use the GROUP BY
and ORDER BY
clauses with the ->>
operator. For example, organize the first_name
values from the table you created in the first example:
For this example, we will add a few more records to the existing table. This will help us see clearly how the data is grouped.
> INSERT INTO users (user_profile) VALUES
('{"first_name": "Lola", "last_name": "Kim", "location": "Seoul", "online": false, "friends": 600}'),
('{"first_name": "Parvati", "last_name": "Patil", "location": "London", "online": false, "friends": 500}');
> SELECT user_profile->>'first_name' AS first_name, user_profile->>'location' AS location FROM users;
first_name | location
-------------+-----------
Ernie | Brooklyn
Lola | NYC
Parvati | London
Lola | Seoul
Group and order the data.
> SELECT user_profile->>'first_name' first_name, count(*) total FROM users group by user_profile->>'first_name' order by total;
first_name | total
-------------+-------
Ernie | 1
Parvati | 1
Lola | 2
The ->>
operator returns STRING
and uses string comparison rules to order the data. If you want numeric ordering, cast the resulting data to FLOAT
.
Map a JSONB
array field into rows
To map a JSONB
array field into rows, use the jsonb_array_elements
function:
CREATE TABLE commodity (id varchar(10), data jsonb);
INSERT INTO commodity (id, data) values ('silver', '{"prices" : [ { "05/01/2022" : 100.5 } , { "06/01/2022" : 101.5 } ]}');
INSERT INTO commodity (id, data) values ('gold', '{"prices" : [ { "05/01/2022" : 200.5 } , { "06/01/2022" : 211.5 } ]}');
SELECT * FROM commodity;
id | data
---------+-------------------------------------------------------------
silver | {"prices": [{"05/01/2022": 100.5}, {"06/01/2022": 101.5}]}
gold | {"prices": [{"05/01/2022": 200.5}, {"06/01/2022": 211.5}]}
(2 rows)
SELECT id as commodity, jsonb_array_elements(commodity.data->'prices') AS "price" FROM commodity;
commodity | price
------------+------------------------
silver | {"05/01/2022": 100.5}
silver | {"06/01/2022": 101.5}
gold | {"05/01/2022": 200.5}
gold | {"06/01/2022": 211.5}
(4 rows)
Access nested JSONB
fields
To display the commodity prices for May, run:
SELECT id AS commodity, data->'prices'->0->'05/01/2022' AS "May prices" from commodity;
commodity | May prices
------------+-------------
silver | 100.5
gold | 200.5
(2 rows)
Update an array element
To update a field value, use the jsonb_set
function. For example, to update the price of silver
on 06/01/2022
to 90.5
, run:
UPDATE commodity SET data = jsonb_set(data, '{prices, 1, "06/01/2022"}', '90.5') where id = 'silver';
UPDATE 1
SELECT * FROM commodity;
id | data
---------+-------------------------------------------------------------
silver | {"prices": [{"05/01/2022": 100.5}, {"06/01/2022": 90.5}]}
gold | {"prices": [{"05/01/2022": 200.5}, {"06/01/2022": 211.5}]}
(2 rows)
Create a table with a JSONB
column and a computed column
In this example, create a table with a JSONB
column and a stored computed column:
> CREATE TABLE student_profiles (
id STRING PRIMARY KEY AS (profile->>'id') STORED,
profile JSONB
);
Create a compute column after you create a table:
> ALTER TABLE student_profiles ADD COLUMN age INT AS ( (profile->>'age')::INT) STORED;
Then, insert a few rows of data:
> INSERT INTO student_profiles (profile) VALUES
('{"id": "d78236", "name": "Arthur Read", "age": "16", "school": "PVPHS", "credits": 120, "sports": "none"}'),
('{"name": "Buster Bunny", "age": "15", "id": "f98112", "school": "THS", "credits": 67, "clubs": "MUN"}'),
('{"name": "Ernie Narayan", "school" : "Brooklyn Tech", "id": "t63512", "sports": "Track and Field", "clubs": "Chess"}');
> SELECT * FROM student_profiles;
+--------+---------------------------------------------------------------------------------------------------------------------+------+
| id | profile | age |
---------+---------------------------------------------------------------------------------------------------------------------+------+
| d78236 | {"age": "16", "credits": 120, "id": "d78236", "name": "Arthur Read", "school": "PVPHS", "sports": "none"} | 16 |
| f98112 | {"age": "15", "clubs": "MUN", "credits": 67, "id": "f98112", "name": "Buster Bunny", "school": "THS"} | 15 |
| t63512 | {"clubs": "Chess", "id": "t63512", "name": "Ernie Narayan", "school": "Brooklyn Tech", "sports": "Track and Field"} | NULL |
+--------+---------------------------------------------------------------------------------------------------------------------+------|
The primary key id
is computed as a field from the profile
column. Additionally the age
column is computed from the profile column data as well.
This example shows how add a stored computed column with a coerced type:
CREATE TABLE json_data (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
json_info JSONB
);
INSERT INTO json_data (json_info) VALUES ('{"amount": "123.45"}');
ALTER TABLE json_data ADD COLUMN amount DECIMAL AS ((json_info->>'amount')::DECIMAL) STORED;
SELECT * FROM json_data;
id | json_info | amount
---------------------------------------+----------------------+---------
e7c3d706-1367-4d77-bfb4-386dfdeb10f9 | {"amount": "123.45"} | 123.45
(1 row)
Create a table with a JSONB
column and a virtual computed column
In this example, create a table with a JSONB
column and virtual computed columns:
> CREATE TABLE student_profiles (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
profile JSONB,
full_name STRING AS (concat_ws(' ',profile->>'firstName', profile->>'lastName')) VIRTUAL,
birthday TIMESTAMP AS (parse_timestamp(profile->>'birthdate')) VIRTUAL
);
Then, insert a few rows of data:
> INSERT INTO student_profiles (profile) VALUES
('{"id": "d78236", "firstName": "Arthur", "lastName": "Read", "birthdate": "2010-01-25", "school": "PVPHS", "credits": 120, "sports": "none"}'),
('{"firstName": "Buster", "lastName": "Bunny", "birthdate": "2011-11-07", "id": "f98112", "school": "THS", "credits": 67, "clubs": "MUN"}'),
('{"firstName": "Ernie", "lastName": "Narayan", "school" : "Brooklyn Tech", "id": "t63512", "sports": "Track and Field", "clubs": "Chess"}');
> SELECT * FROM student_profiles;
id | profile | full_name | birthday
---------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------+---------------+----------------------
0e420282-105d-473b-83e2-3b082e7033e4 | {"birthdate": "2011-11-07", "clubs": "MUN", "credits": 67, "firstName": "Buster", "id": "f98112", "lastName": "Bunny", "school": "THS"} | Buster Bunny | 2011-11-07 00:00:00
6e9b77cd-ec67-41ae-b346-7b3d89902c72 | {"birthdate": "2010-01-25", "credits": 120, "firstName": "Arthur", "id": "d78236", "lastName": "Read", "school": "PVPHS", "sports": "none"} | Arthur Read | 2010-01-25 00:00:00
f74b21e3-dc1e-49b7-a648-3c9b9024a70f | {"clubs": "Chess", "firstName": "Ernie", "id": "t63512", "lastName": "Narayan", "school": "Brooklyn Tech", "sports": "Track and Field"} | Ernie Narayan | NULL
(3 rows)
Time: 2ms total (execution 2ms / network 0ms)
The virtual column full_name
is computed as a field from the profile
column's data. The first name and last name are concatenated and separated by a single whitespace character using the concat_ws
string function.
The virtual column birthday
is parsed as a TIMESTAMP
value from the profile
column's birthdate
string value. The parse_timestamp
function is used to parse strings in TIMESTAMP
format.
Supported casting and conversion
This section describes how to cast and convert JSONB
values.
You can cast all JSONB
values to the following data type:
You can cast numeric JSONB
values to the following numeric data types:
For example:
> SELECT '100'::JSONB::INT;
int8
--------
100
(1 row)
> SELECT '100000'::JSONB::FLOAT;
float8
----------
100000
(1 row)
> SELECT '100.50'::JSONB::DECIMAL;
numeric
-----------
100.50
(1 row)
You use the parse_timestamp
function to parse strings in TIMESTAMP
format.
SELECT parse_timestamp ('2022-05-28T10:53:25.160Z');
parse_timestamp
--------------------------
2022-05-28 10:53:25.16
(1 row)
You can use the parse_timestamp
function to retrieve string representations of timestamp data within JSONB
columns in TIMESTAMP
format.
CREATE TABLE events (
raw JSONB,
event_created TIMESTAMP AS (parse_timestamp(raw->'event'->>'created')) VIRTUAL
);
INSERT INTO events (raw) VALUES ('{"event":{"created":"2022-05-28T10:53:25.160Z"}}');
SELECT event_created FROM events;
CREATE TABLE
INSERT 1
event_created
--------------------------
2022-05-28 10:53:25.16
(1 row)