Postgres Containment Operators : Part 1 — Introduction

)

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 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!¹

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!

)
  1. https://www.destroyallsoftware.com/talks/wat

Hasura is an open-source engine that gives you realtime GraphQL APIs on new or existing Postgres databases, with built-in support for stitching custom GraphQL APIs and triggering webhooks on database changes.


Hasura

Hasura

The Hasura GraphQL Engine gives you realtime, high performance GraphQL on any Postgres app. Now supports event triggers for use with serverless.

Read More