jsonb type

jsonb data expresses a 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_agg(expression) -> jsonb

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_agg(keys, values) -> jsonb

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 functions 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 numeric in SQL.

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\""
  

Subscripting

You can use subscript notation ([]) to extract an element from a jsonb array or object.

The returned value is always of type jsonb. If the requested array element or object key does not exist, or if either the input value or subscript value is NULL, the subscript operation returns NULL.

Arrays

To extract an element from an array, supply the 0-indexed position as the subscript:

SELECT ('[1, 2, 3]'::jsonb)[1]

 jsonb
-------
 2

Negative indexes count backwards from the end of the array. Slice syntax is not supported. Note also that 0-indexed positions are at variance with list and array types, whose subscripting operation uses 1-indexed positions.

Objects

To extract a value from an object, supply the key as the subscript:

SELECT ('{"a": 1, "b": 2, "c": 3}'::jsonb)['b'];

 jsonb
-------
 2

You can chain subscript operations to retrieve deeply nested elements:

SELECT ('{"1": 2, "a": ["b", "c"]}'::jsonb)['a'][1];

 jsonb
-------
 "c"

Remarks

Because the output type of the subscript operation is always jsonb, when comparing the output of a subscript to a string, you must supply a JSON string to compare against:

SELECT ('["a", "b"]::jsonb)[1] = '"b"'

Note the extra double quotes on the right-hand side of the comparison.

Parsing

Examples

Operators

Field access as jsonb (->)

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

• Use a string to return the value for a specific key:

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

   field_jsonb
  -------------
   2
  

• Use an int to return the value in an array at a specific index:

  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.

• Use a string to return the value for a specific key:

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

   field_text
  -------------
   2
  

• Use an int to return the value in an array at a specific index:

  SELECT '["1", "a", 2]'::jsonb->>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,"a":["b","c"]}

Remove key (-)

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

  rm_key
-----------
 {"1":2}

LHS contains RHS (@>)

Here, the left hand side does contain the right hand side, so the result is t for true.

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

 lhs_contains_rhs
------------------
 t

RHS contains LHS (<@)

Here, the right hand side does contain the left hand side, so the result is t for true.

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

Expanding a JSON array

SELECT * FROM jsonb_array_elements('[true, 1, "a", {"b": 2}, null]'::jsonb);

   value
-----------
 true
 1.0
 "a"
 {"b":2.0}
 null

Flattening a JSON array

SELECT t.id,
       obj->>'a' AS a,
       obj->>'b' AS b
FROM (
  VALUES
    (1, '[{"a":1,"b":2},{"a":3,"b":4}]'::jsonb),
    (2, '[{"a":5,"b":6},{"a":7,"b":8}]'::jsonb)
) AS t(id, json_col)
CROSS JOIN jsonb_array_elements(t.json_col) AS obj;

 id | a | b
----+---+---
  1 | 1 | 2
  1 | 3 | 4
  2 | 5 | 6
  2 | 7 | 8

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

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.0::float, 'b', 'a', 1.1::float);

 jsonb_build_object
--------------------
 {"2":"b","a":1.1}

jsonb_each

SELECT * FROM jsonb_each('{"1": 2.1, "a": ["b", "c"]}'::jsonb);

 key |   value
-----+-----------
 1   | 2.1
 a   | ["b","c"]

Note that the value column is jsonb.

jsonb_each_text

SELECT * FROM jsonb_each_text('{"1": 2.1, "a": ["b", "c"]}'::jsonb);

 key |   value
-----+-----------
 1   | 2.1
 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,   +
   "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(t) AS jsonified_row
FROM (
  VALUES
  (1, 'hey'),
  (2, NULL),
  (3, 'hi'),
  (4, 'salutations')
  ) AS t(id, content)
WHERE t.content LIKE 'h%';

      jsonified_row
--------------------------
 {"content":"hi","id":3}
 {"content":"hey","id":1}

Note that the output is jsonb.