JSONB

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 PosgreSQL, 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

Syntax

The syntax for the JSONB data type follows the format specified in RFC8259. A constant value of type JSONB can be expressed using aninterpreted literal or astring literalannotated withtype 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

FunctionDescription
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

OperatorDescriptionExample
->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

  1. > CREATE TABLE users (
  2.     profile_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  3.     last_updated TIMESTAMP DEFAULT now(),
  4.     user_profile JSONB
  5.   );
  1. > SHOW COLUMNS FROM users;
  1. +--------------+-----------+-------------+-------------------+-----------------------+-------------+
  2. | column_name  | data_type | is_nullable |  column_default   | generation_expression |   indices   |
  3. +--------------+-----------+-------------+-------------------+-----------------------+-------------+
  4. | profile_id   | UUID      |    false    | gen_random_uuid() |                       | {"primary"} |
  5. | last_updated | TIMESTAMP |    true     | now()             |                       | {}          |
  6. | user_profile | JSON      |    true     | NULL              |                       | {}          |
  7. +--------------+-----------+-------------+-------------------+-----------------------+-------------+
  8. (3 rows)
  1. > INSERT INTO users (user_profile) VALUES
  2.     ('{"first_name": "Lola", "last_name": "Dog", "location": "NYC", "online" : true, "friends" : 547}'),
  3.     ('{"first_name": "Ernie", "status": "Looking for treats", "location" : "Brooklyn"}');
  1. > SELECT * FROM users;
  1. +--------------------------------------+----------------------------------+--------------------------------------------------------------------------+
  2. |              profile_id              |           last_updated           |                               user_profile                               |
  3. +--------------------------------------+----------------------------------+--------------------------------------------------------------------------+
  4. | 33c0a5d8-b93a-4161-a294-6121ee1ade93 | 2018-02-27 16:39:28.155024+00:00 | {"first_name": "Lola", "friends": 547, "last_name": "Dog", "location":   |
  5. |                                      |                                  | "NYC", "online": true}                                                   |
  6. | 6a7c15c9-462e-4551-9e93-f389cf63918a | 2018-02-27 16:39:28.155024+00:00 | {"first_name": "Ernie", "location": "Brooklyn", "status": "Looking for   |
  7. |                                      |                                  | treats"}                                                                 |
  8. +--------------------------------------+----------------------------------+--------------------------------------------------------------------------+

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:

  1. > SELECT profile_id, last_updated, jsonb_pretty(user_profile) FROM users;
  1. +--------------------------------------+----------------------------------+------------------------------------+
  2. |              profile_id              |           last_updated           |            jsonb_pretty            |
  3. +--------------------------------------+----------------------------------+------------------------------------+
  4. | 33c0a5d8-b93a-4161-a294-6121ee1ade93 | 2018-02-27 16:39:28.155024+00:00 | {                                  |
  5. |                                      |                                  |     "first_name": "Lola",          |
  6. |                                      |                                  |     "friends": 547,                |
  7. |                                      |                                  |     "last_name": "Dog",            |
  8. |                                      |                                  |     "location": "NYC",             |
  9. |                                      |                                  |     "online": true                 |
  10. |                                      |                                  | }                                  |
  11. | 6a7c15c9-462e-4551-9e93-f389cf63918a | 2018-02-27 16:39:28.155024+00:00 | {                                  |
  12. |                                      |                                  |     "first_name": "Ernie",         |
  13. |                                      |                                  |     "location": "Brooklyn",        |
  14. |                                      |                                  |     "status": "Looking for treats" |
  15. |                                      |                                  | }                                  |
  16. +--------------------------------------+----------------------------------+------------------------------------+

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:

  1. > SELECT user_profile->'first_name',user_profile->'location' FROM users;
  1. +----------------------------+--------------------------+
  2. | user_profile->'first_name' | user_profile->'location' |
  3. +----------------------------+--------------------------+
  4. | "Lola"                     | "NYC"                    |
  5. | "Ernie"                    | "Brooklyn"               |
  6. +----------------------------+--------------------------+

You can also use the ->> operator to return JSONB field values as STRING values:

  1. > SELECT user_profile->>'first_name', user_profile->>'location' FROM users;
  1. +-----------------------------+---------------------------+
  2. | user_profile->>'first_name' | user_profile->>'location' |
  3. +-----------------------------+---------------------------+
  4. | Lola                        | NYC                       |
  5. | Ernie                       | Brooklyn                  |
  6. +-----------------------------+---------------------------+

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:

  1. > CREATE TABLE student_profiles (
  2.     id STRING PRIMARY KEY AS (profile->>'id') STORED,
  3.     profile JSONB
  4. );

Then, insert a few rows of data:

  1. > INSERT INTO student_profiles (profile) VALUES
  2.     ('{"id": "d78236", "name": "Arthur Read", "age": "16", "school": "PVPHS", "credits": 120, "sports": "none"}'),
  3.     ('{"name": "Buster Bunny", "age": "15", "id": "f98112", "school": "THS", "credits": 67, "clubs": "MUN"}'),
  4.     ('{"name": "Ernie Narayan", "school" : "Brooklyn Tech", "id": "t63512", "sports": "Track and Field", "clubs": "Chess"}');
  1. > SELECT * FROM student_profiles;
  1. +--------+---------------------------------------------------------------------------------------------------------------------+
  2. |   id   |                                                       profile                                                       |
  3. +--------+---------------------------------------------------------------------------------------------------------------------+
  4. | d78236 | {"age": "16", "credits": 120, "id": "d78236", "name": "Arthur Read", "school": "PVPHS", "sports": "none"}           |
  5. | f98112 | {"age": "15", "clubs": "MUN", "credits": 67, "id": "f98112", "name": "Buster Bunny", "school": "THS"}               |
  6. | t63512 | {"clubs": "Chess", "id": "t63512", "name": "Ernie Narayan", "school": "Brooklyn Tech", "sports": "Track and Field"} |
  7. +--------+---------------------------------------------------------------------------------------------------------------------+

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

See also

Was this page helpful?
YesNo