### An unofficial guide to JSONB operators in Postgres.

## What is JSONB ?

`jsonb`

and `json`

are datatypes in Postgres that can store arbitrary JSON documents. They differ primarily in the storage format - `json`

stores data verbatim (as text) whereas `jsonb`

stores it in a decomposed binary format, allowing the content to be indexed.

`jsonb`

has more interesting operators like containment and existence of key/element, in addition to the data extraction operators that `json`

supports.

The official Postgres documentation is lacking, especially in defining the containment operators. This article aims to correct that.

## Containment

The containment boolean operators, `@>`

and `<@`

, are used to check if one JSON document is contained inside another. They denote **contains** and **contained-in** respectively. We describe only the `@>`

operator.

The datatypes in `jsonb`

fall into three broad categories - **scalars**, **objects** and **arrays**. The **scalars** are *null*, *boolean*, *string* or *int*.

`a @> b`

is `false`

whenever `a`

and `b`

are in different categories, however with one exception - the case when `a`

is an **array** and `b`

is a **scalar**. This leaves us with four non-trivial cases.

## Case 1 — (object, object)

`a @> b`

is `true`

if all the keys in `b`

are in `a`

and moreover, for each key `k`

in `b`

, `v_a @> v_b`

must hold, where `v_a`

and `v_b`

are values corresponding to the key `k`

in objects `a`

and `b`

respectively. For example,

```
select '{"a" : 1, "b" : 2}' :: jsonb @> '{"a" : 1}' as result;
result
--------
t
(1 row)
```

## Case 2 — (array, array)

`a @> b`

is `true`

if for each element `y`

of `b`

, there is an `x`

in `a`

such that `x @> y`

. Note that this definition implies that ordering and repetition are irrelevant.

If you think that’s a reasonable definition you may be in for a nasty surprise! Suppose you want to find all arrays that contain the objects `{"a" : 1},`

and `{"b" : 2}`

. More things contain `[{"a" : 1}, {"b" : 2}]`

than you might think. For example,

```
select '[{"a" : 2}, {"b" : 4}]' :: jsonb @> '[{"a" : 2}, {"b" : 4}]' as result;
result
--------
t
(1 row)
select '[{"a" : 2, "b" : 4}]' :: jsonb @> '[{"a" : 2}, {"b" : 4}]' as result;
result
--------
t
(1 row)
```

In the second case, an array with one object contains an array with two objects! Wat!¹

## Case 3 — (scalar, scalar)

`a @> b`

is `true`

if `a`

is exactly equal to `b`

.

## Case 4 — (array, scalar)

`a @> b`

is `true`

if `a @> [b]`

. Note, this seems to work only at the top level as demonstrated by the example below.

```
postgres=# SELECT '[1]'::jsonb @> '1'::jsonb as result;
result
--------
t
(1 row)
postgres=# SELECT '[[1]]'::jsonb @> '[1]'::jsonb as result;
result
--------
f
(1 row)
```

## Existence

Though this class of operators is well-defined in the official documentation we include them for the sake of completeness.

This class of operators is used to query for the existence of keys in objects or elements in arrays. The examples below demonstrate their usage. All of them evaluate to `true`

.

OperatorDescriptionExample`?`

Does the key/element string exist within the JSON value?`'{"a":1, "b":2}'::jsonb ? 'b'?|`

Do any of these key/element strings exist?`'{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'c']?&`

Do all of these key/element strings exist?`'["a", "b"]'::jsonb ?& array['a', 'b']`

These operators combined with indexing put Postgres in a good position in the field of document-oriented database systems. It will be interesting to compare this to other document-oriented databases (*Update: We have a follow up post **here** with a comparison*). Comments are welcome!