jsonb Data Type

jsonb data expresses a JavaScript Object Notation (JSON) object.

Detail Info
Quick Syntax '{"1":2,"3":4}'::JSONB
Size Variable

Syntax 

' json_string ' ::JSONB
Field Use
json_string A well-formed JSON object.

JSONB functions + operators 

Materialize supports the following operators and functions.

Operators 

Operator RHS Type Description
-> string, int Access field by name or index position, and return jsonb (docs)
->> string, int Access field by name or index position, and return string (docs)
|| jsonb Concatenate LHS and RHS (docs)
- string Delete all values with key of RHS (docs)
@> jsonb Does element contain RHS? (docs)
<@ jsonb Does RHS contain element? (docs)
? string Is RHS a top-level key? (docs)

Functions 

Function Computes
jsonb_array_elements(j: jsonb) -> Col<jsonb>
j’s elements if j is an array. (docs)
jsonb_array_elements_text(j: jsonb) -> Col<string>
j’s elements if j is an array. (docs)
jsonb_array_length(j: jsonb) -> int
Number of elements in j’s outermost array. (docs)
jsonb_build_array(x: ...) -> jsonb
The elements of x in a jsonb array. Elements can be of heterogenous types. (docs)
jsonb_build_object(x: ...) -> jsonb
The elements of x as a jsonb object. The argument list alternates between keys and values. (docs)
jsonb_each(j: jsonb) -> Col<(key: string, value: jsonb)>
j’s outermost elements if j is an object. (docs)
jsonb_each_text(j: jsonb) -> Col<(key: string, value: string)>
j’s outermost elements if j is an object. (docs)
jsonb_object_keys(j: jsonb) -> Col<string>
j’s outermost keys if j is an object. (docs)
jsonb_pretty(j: jsonb) -> string
Pretty printed (i.e. indented) j. (docs)
jsonb_typeof(j: jsonb) -> string
Type of j’s outermost value. One of object, array, string, number, boolean, and null. (docs)
jsonb_strip_nulls(j: jsonb) -> jsonb
j with all object fields with a value of null removed. Other null values remain. (docs)
to_jsonb(v: T) -> jsonb
v as jsonb (docs)

Detail 

Functions that return Cols are considered table function and can only be used as tables, i.e. you cannot use them as scalar values. For example, you can only use jsonb_object_keys in the following way:

SELECT * FROM jsonb_object_keys('{"1":2,"3":4}'::JSONB);

Details 

Valid casts 

From jsonb 

You can cast jsonb to:

To jsonb 

You can cast the following types to jsonb:

Notes about converting jsonb to string/text 

jsonb can have some odd-feeling corner cases when converting to or from string (also known as text).

Examples 

Operators 

Field access as jsonb (->

The type of JSON element you’re accessing dictates the RHS’s type.

Field accessors can also be chained together.

SELECT '{"1": 2, "a": ["b", "c"]}'::JSONB->'a'->1 AS field_jsonb;
 field_jsonb
-------------
 "c"

Note that all returned values are jsonb.


Field access as string (->>

The type of JSON element you’re accessing dictates the RHS’s type.

Field accessors can also be chained together, as long as the LHS remains jsonb.

SELECT '{"1": 2, "a": ["b", "c"]}'::JSONB->'a'->>1 AS field_jsonb;
 field_jsonb
-------------
 c

Note that all returned values are string.


jsonb concat (||

SELECT '{"1": 2}'::JSONB ||
       '{"a": ["b", "c"]}'::JSONB AS concat;
             concat
---------------------------------
 {"1":2.0,"a":["b","c"]}

Remove key (-

 SELECT '{"1": 2, "a": ["b", "c"]}'::JSONB - 'a' AS rm_key;
  rm_key
-----------
 {"1":2.0}

LHS contains RHS (@>

SELECT '{"1": 2, "a": ["b", "c"]}'::JSONB @>
       '{"1": 2}'::JSONB AS lhs_contains_rhs;
 lhs_contains_rhs
------------------
 t

RHS contains LHS (<@

SELECT '{"1": 2}'::JSONB <@
       '{"1": 2, "a": ["b", "c"]}'::JSONB AS lhs_contains_rhs;
 rhs_contains_lhs
------------------
 t

Search top-level keys (?

SELECT '{"1": 2, "a": ["b", "c"]}'::JSONB ? 'a' AS search_for_key;
 search_for_key
----------------
 t
SELECT '{"1": 2, "a": ["b", "c"]}'::JSONB ? 'b' AS search_for_key;
 search_for_key
----------------
 f

Functions 

jsonb_array_elements 

SELECT * FROM jsonb_array_elements('[true, 1, "a", {"b": 2}, null]'::JSONB);
   value
-----------
 true
 1.0
 "a"
 {"b":2.0}
 null

Note that the value column is jsonb.


jsonb_array_elements_text 

SELECT * FROM jsonb_array_elements_text('[true, 1, "a", {"b": 2}, null]'::JSONB);
   value
-----------
 true
 1.0
 "a"
 {"b":2.0}
 null

Note that the value column is string.


jsonb_array_length 

SELECT jsonb_array_length('[true, 1, "a", {"b": 2}, null]'::JSONB);
 jsonb_array_length
--------------------
                  5

jsonb_build_array 

SELECT jsonb_build_array('a', 1::float, 2.0::float, true);
 jsonb_build_array
--------------------
 ["a",1.0,2.0,true]

jsonb_build_object 

SELECT jsonb_build_object(2::float, 'b', 'a', 1::float);
 jsonb_build_object
--------------------
 {"2":true,"a":1.0}

jsonb_each 

SELECT * FROM jsonb_each('{"1": 2, "a": ["b", "c"]}'::JSONB);
 key |   value
-----+-----------
 1   | 2.0
 a   | ["b","c"]

Note that the value column is jsonb.


jsonb_each_text 

SELECT * FROM jsonb_each_text('{"1": 2, "a": ["b", "c"]}'::JSONB);
 key |   value
-----+-----------
 1   | 2.0
 a   | ["b","c"]

Note that the value column is string.


jsonb_object_keys 

SELECT * FROM jsonb_object_keys('{"1": 2, "a": ["b", "c"]}'::JSONB);
 jsonb_object_keys
-------------------
 1
 a

jsonb_pretty 

SELECT jsonb_pretty('{"1": 2, "a": ["b", "c"]}'::JSONB);
 jsonb_pretty
--------------
 {           +
   "1": 2.0, +
   "a": [    +
     "b",    +
     "c"     +
   ]         +
 }

jsonb_typeof 

SELECT jsonb_typeof('[true, 1, "a", {"b": 2}, null]'::JSONB);
 jsonb_typeof
--------------
 array
SELECT * FROM jsonb_typeof('{"1": 2, "a": ["b", "c"]}'::JSONB);
 jsonb_typeof
--------------
 object

jsonb_strip_nulls 

SELECT jsonb_strip_nulls('[{"1":"a","2":null},"b",null,"c"]'::JSONB);
    jsonb_strip_nulls
--------------------------
 [{"1":"a"},"b",null,"c"]

to_jsonb 

SELECT to_jsonb('hello');
 to_jsonb
----------
 "hello"

Note that the output is jsonb.