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.
I. 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 ais 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!1
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)
II. 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 good position in the field of document-oriented database systems. It will be interesting to compare to other document-oriented databases. Comments are welcome!
Part 2 of the series will describe the indexing support that can make these queries extremely efficient.
The cover image, Jason and the Argonauts Disembark at Colchis by Coyau is licensed under CC-BY-SA-3.0
The other image is Epic drummer T-Rex nuke
The original article appeared on the Hasura blog: http://hasura.io/blog/the-unofficial-guide-to-jsonb-operators-in-postgres/
