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

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

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

## See also

- JSON tutorial
- Inverted Indexes
- Computed Columns
- Data Types
- Functions and Operators