jsonb Data Type
jsonb
data expresses a JavaScript Object Notation (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
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 |
---|---|
|
j 's elements if j is an array.
(docs)
|
|
j 's elements if j is an array.
(docs)
|
|
Number of elements in j 's outermost array.
(docs)
|
|
The elements of x in a jsonb array. Elements can be of heterogenous types.
(docs)
|
|
The elements of x as a jsonb object. The argument list alternates between keys and values.
(docs)
|
|
j 's outermost elements if j is an object.
(docs)
|
|
j 's outermost elements if j is an object.
(docs)
|
|
j 's outermost keys if j is an object.
(docs)
|
|
Pretty printed (i.e. indented) j .
(docs)
|
|
Type of j 's outermost value. One of object , array , string , number , boolean , and null .
(docs)
|
|
j with all object fields with a value of null removed. Other null values remain.
(docs)
|
|
v as jsonb
(docs)
|
Detail
Functions that return Col
s 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 tofloat
in SQL.- To operate on elements as
int
s, you must cast them tofloat
and then to, e.g.::float::int
.
- To operate on elements as
Valid casts
From jsonb
You can cast jsonb
to:
boolean
(explicitly)numeric
(explicitly)int
(explicitly)real
/double precision
(explicitly)text
(by assignment) (stringifiesjsonb
)
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
string
(also known as text
).
-
jsonb::text
always produces the printed version of the JSON.SELECT ('"a"'::JSONB)::STRING 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 SQLstring
.SELECT ('"a"'::JSONB)->>0 AS string_elem;
jsonb_elem ------------ a
-
string
values passed toto_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 string
(->>
)
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, 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
.