JSONB

On this page

Warning:

As of May 12, 2021, CockroachDB v19.2 is no longer supported. For more details, refer to the Release Support Policy.

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 inverted indexes.

Tip:

For a hands-on demonstration of storing and querying JSON data from a third-party API, see the JSON tutorial.

Note:

Vectorized execution is currently not supported for this data type.

Alias

In CockroachDB, JSON is an alias for JSONB.

Note:

In PostgreSQL, 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 JSONB values.
A standard index cannot be created on a JSONB column; you must use an inverted index.

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 standard int64)
Array (i.e., an ordered sequence of JSONB values)
Object (i.e., a mapping from strings to JSONB values)

Examples:

'{"type": "account creation", "username": "harvestboy93"}'
'{"first_name": "Ernie", "status": "Looking for treats", "location" : "Brooklyn"}'

Note:

If duplicate keys are included in the input, only the last value is kept.

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.

`JSONB` Functions

Function	Description	Example
`jsonb_array_elements(<jsonb>)`	Expands a `JSONB` array to a set of `JSONB` values.	`SELECT jsonb_array_elements('[1,true, 2,false]');`
`jsonb_build_object(<any_element>...)`	Builds a `JSONB` object out of a variadic argument list that alternates between keys and values.	`SELECT json_build_object('Zoo',1,'Enter',2);`
`jsonb_each(<jsonb>)`	Expands the outermost `JSONB` object into a set of key-value pairs.	`SELECT * from json_each('{"a":"Apple", "b":"ball"}');`
`jsonb_object_keys(<jsonb>)`	Returns sorted set of keys in the outermost `JSONB` object.	`SELECT * from jsonb_object_keys('{"fb1":"abc123","fb2":{"fb3":"ant", "f4":"ball"}}');`
`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.

`JSONB` 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.

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.

Create a table with a `JSONB` column and a computed column

In this example, create a table with a JSONB column and a computed column:

> CREATE TABLE student_profiles (
    id STRING PRIMARY KEY AS (profile->>'id') STORED,
    profile JSONB
);

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                                                       |
+--------+---------------------------------------------------------------------------------------------------------------------+
| d78236 | {"age": "16", "credits": 120, "id": "d78236", "name": "Arthur Read", "school": "PVPHS", "sports": "none"}           |
| f98112 | {"age": "15", "clubs": "MUN", "credits": 67, "id": "f98112", "name": "Buster Bunny", "school": "THS"}               |
| t63512 | {"clubs": "Chess", "id": "t63512", "name": "Ernie Narayan", "school": "Brooklyn Tech", "sports": "Track and Field"} |
+--------+---------------------------------------------------------------------------------------------------------------------+

The primary key id is computed as a field from the profile column.

Supported casting and conversion

JSONB values can be cast to the following data type:

STRING

Cockroach
University

Docs Hub

JSONB

Alias

Considerations

Syntax

Size

`JSONB` Functions

`JSONB` Operators

Examples

Create a Table with a `JSONB` Column

Retrieve formatted `JSONB` data

Retrieve specific fields from a `JSONB` value

Create a table with a `JSONB` column and a computed column

Supported casting and conversion

See also

Cockroach University

Docs Hub

Cockroach University

Docs Hub

JSONB

Alias

Considerations

Syntax

Size

JSONB Functions

JSONB Operators

Examples

Create a Table with a JSONB Column

Retrieve formatted JSONB data

Retrieve specific fields from a JSONB value

Create a table with a JSONB column and a computed column

Supported casting and conversion

See also

Cockroach
University

Cockroach
University

`JSONB` Functions

`JSONB` Operators

Create a Table with a `JSONB` Column

Retrieve formatted `JSONB` data

Retrieve specific fields from a `JSONB` value

Create a table with a `JSONB` column and a computed column