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.
Considerations
- The primary key, foreign key, and unique constraints cannot be used on
JSONBvalues. - A standard index cannot be created on a
JSONBcolumn; you must use a GIN index. - CockroachDB does not currently key-encode JSON values. As a result, tables cannot be ordered by
JSONB/JSON-typed columns. For details, see tracking issue.
Syntax
The syntax for the JSONB data type follows the format specified in RFC8259. A constant value of type JSONB can be expressed 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
JSONBvalues) - Object (i.e., a mapping from strings to
JSONBvalues)
Examples:
'{"type": "account creation", "username": "harvestboy93"}''{"first_name": "Ernie", "status": "Looking for treats", "location" : "Brooklyn"}'
Size
The size of a JSONB value is variable, but it's recommended to keep values under 1 MB to ensure performance. Above that threshold, write amplification and other considerations may cause significant performance degradation.
Functions
| Function | Description |
|---|---|
jsonb_array_elements(<jsonb>) |
Expands a JSONB array to a set of JSONB values. |
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. |
jsonb_object_keys(<jsonb>) |
Returns sorted set of keys in the outermost JSONB object. |
jsonb_pretty(<jsonb>) |
Returns the given JSONB value as a STRING indented and with newlines. See the example below. |
For the full list of supported JSONB functions, see Functions and Operators.
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; |
For the full list of supported JSONB operators, see Functions and Operators.
Known limitations
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 type JSON, CockroachDB returns the error unable to encode table key: *tree.DJSON. If the memory limit is not reached, then the query will be processed without error.
For details, see tracking issue.
Examples
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 |
+--------------+-----------+-------------+-------------------+-----------------------+-------------+
| profile_id | UUID | false | gen_random_uuid() | | {"primary"} |
| last_updated | TIMESTAMP | true | now() | | {} |
| user_profile | JSON | true | NULL | | {} |
+--------------+-----------+-------------+-------------------+-----------------------+-------------+
(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 | 2018-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 | 2018-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 | 2018-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 | 2018-02-27 16:39:28.155024+00:00 | { |
| | | "first_name": "Ernie", |
| | | "location": "Brooklyn", |
| | | "status": "Looking for treats" |
| | | } |
+--------------------------------------+----------------------------------+------------------------------------+
Retrieve specific fields from a JSONB value
To retrieve a specific field from a JSONB value, use the -> operator. For example, retrieve a field from the table you created in the first example:
> SELECT user_profile->'first_name',user_profile->'location' FROM users;
+----------------------------+--------------------------+
| user_profile->'first_name' | user_profile->'location' |
+----------------------------+--------------------------+
| "Lola" | "NYC" |
| "Ernie" | "Brooklyn" |
+----------------------------+--------------------------+
You can also use the ->> operator to return JSONB field values as STRING values:
> SELECT user_profile->>'first_name', user_profile->>'location' FROM users;
+-----------------------------+---------------------------+
| user_profile->>'first_name' | user_profile->>'location' |
+-----------------------------+---------------------------+
| Lola | NYC |
| Ernie | Brooklyn |
+-----------------------------+---------------------------+
You can use the @> operator to filter the values in key-value pairs to return JSONB field values:
> SELECT user_profile->'first_name', user_profile->'location' FROM users WHERE user_profile @> '{"location":"NYC"}';
+-----------------------------+---------------------------+
| user_profile->>'first_name' | user_profile->>'location' |
+-----------------------------+---------------------------+
| Lola | NYC |
+-----------------------------+---------------------------+
For the full list of functions and operators we support, see Functions and Operators.
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
Now let’s 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.
For the full list of functions and operators we support, see Functions and Operators.
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
All JSONB values can be cast to the following data type:
Numeric JSONB values can be cast 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)
The parse_timestamp function is used to parse strings in TIMESTAMP format.
SELECT parse_timestamp ('2021-09-28T10:53:25.160Z');
parse_timestamp
--------------------------
2021-09-28 10:53:25.16
(1 row)
The parse_timestamp function can be used 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":"2021-09-28T10:53:25.160Z"}}');
SELECT event_created FROM events;
CREATE TABLE
Time: 6ms total (execution 6ms / network 0ms)
INSERT 1
Time: 9ms total (execution 9ms / network 0ms)
event_created
--------------------------
2021-09-28 10:53:25.16
(1 row)
Time: 1ms total (execution 1ms / network 0ms)
See also
Was this helpful?
