jsonb Data Type

jsonb data expresses a JavaScript Object Notation (JSON) object similar to PostgreSQL’s implementation.

Materialize does not yet support a type more similar to PostgreSQL’s implementation of json.

Syntax

jsonb functions + operators

Materialize supports the following operators and functions.

Operators

Functions

jsonb_array_elements(j: jsonb) -> Col<jsonb>

jsonb_array_elements_text(j: jsonb) -> Col<string>

jsonb_array_length(j: jsonb) -> int

jsonb_build_array(x: ...) -> jsonb

jsonb_build_object(x: ...) -> jsonb

jsonb_each(j: jsonb) -> Col<(key: string, value: jsonb)>

jsonb_each_text(j: jsonb) -> Col<(key: string, value: string)>

jsonb_object_keys(j: jsonb) -> Col<string>

jsonb_pretty(j: jsonb) -> string

jsonb_typeof(j: jsonb) -> string

jsonb_strip_nulls(j: jsonb) -> jsonb

to_jsonb(v: T) -> jsonb

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

• jsonb elements can be of the following types:
  • Objects
  • Arrays
  • String
  • Number
  • Boolean
  • Null
• Numbers in jsonb elements are all equivalent to float in SQL.
  • To operate on elements as ints, you must cast them to float and then to, e.g. ::float::int.

Valid casts

From jsonb

You can cast jsonb to:

• boolean (explicitly)
• numeric (explicitly)
• int (explicitly)
• real/double precision (explicitly)
• text (by assignment) (stringifies jsonb)

To jsonb

You can explicitly cast from text to jsonb.

Notes about converting jsonb to text

jsonb can have some odd-feeling corner cases when converting to or from text.

• jsonb::text always produces the printed version of the JSON.

  SELECT ('"a"'::jsonb)::text AS jsonb_elem;
  

   jsonb_elem
  ------------
   "a"
  

• ->> and the _text functions produce the printed version of the inner element, unless the output is a single JSON string in which case they print it without quotes, i.e. as a SQL text value.

  SELECT ('"a"'::jsonb)->>0 AS string_elem;
  

   jsonb_elem
  ------------
   a
  

• text values passed to to_jsonb with quotes (") produced jsonb strings with the quotes escaped.

  SELECT to_jsonb('"foo"') AS escaped_quotes;
  

   escaped_quotes
  ----------------
   "\"foo\""
  

Examples

Operators

Field access as jsonb (->)

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

• JSON objects require a string:

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

   field_jsonb
  -------------
   2.0
  

• JSON arrays require an int:

  SELECT '["1", "a", 2]'::jsonb->1 AS field_jsonb;
  

   field_jsonb
  -------------
   "a"
  

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 text (->>)

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

• JSON objects require text:

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

   field_text
  -------------
   2.0
  

• JSON arrays require an int:

  SELECT '["1", "a", 2]'::text->>1 AS field_text;
  

   field_text
  -------------
   a
  

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_text;

 field_text
-------------
 c

Note that all returned values are string.

Path access as jsonb (#>)

You can access specific elements in a jsonb value using a “path”, which is a text array where each element is either a field key or an array element:

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

 field_jsonb
-------------
 "c"

The operator returns a value of type jsonb. If the path is invalid, it returns NULL.

Path access as text (#>>)

The #>> operator is equivalent to the #> operator, except that the operator returns a value of type text.

SELECT '{"1": 2, "a": ["b", "c"]}'::jsonb #>> '{a,1}' AS field_text;

 field_text
-------------
 c

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.