JSONB

On this page

Warning:

CockroachDB v2.1 is no longer supported as of April 30, 2020. 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.

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

`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                  |
+-----------------------------+---------------------------+

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