jsonb type

jsonb data expresses a JSON object similar to PostgreSQL’s implementation.

Detail Info
Quick Syntax '{"1":2,"3":4}'::jsonb
Size Variable
Catalog name pg_catalog.jsonb
OID 3802

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

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
-> text, int Access field by name or index position, and return jsonb (docs)
->> text, int Access field by name or index position, and return text (docs)
#> text[] Access field by path, and return jsonb (docs)
#>> text[] Access field by path, and return text (docs)
|| jsonb Concatenate LHS and RHS (docs)
- text Delete all values with key of RHS (docs)
@> jsonb Does element contain RHS? (docs)
<@ jsonb Does RHS contain element? (docs)
? text Is RHS a top-level key? (docs)

Functions

Function

jsonb_agg(expression) -> jsonb

Aggregate values (including nulls) as a jsonb array (docs).

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

Output each element of x as 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_agg(keys, values) -> jsonb

Aggregate keys and values (including nulls) as a jsonb 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 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:

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

Manually parsing JSON-formatted data in SQL can be tedious. 🫠 You can use the widget below to automatically turn a sample JSON payload into a parsing view with the individual fields mapped to columns.

Target object type

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.

Back to top ↑