JSONB
Use the JSONB
data type to create a column that can store JSON data.
Notes:
- Numbers not representable by IEEE 754 double precision floating point may have poor interoperability, notably numbers in the
bigint
type larger than(2**53)-1
. - Avoid using a
JSONB
column forGROUP BY
andORDER BY
clauses orPRIMARY
andINDEX
keys. The exact behavior may change in the future.- The suggested usage is to extract the target field and cast to a simple type.
- If you have JSON data contained in a string..
- use
STRUCT
if the schema is known, - or use
VARCHAR
and convert it intoJSONB
later.
- use
Define a JSONB type
Syntax:JSONB
Examples
The statement below creates a table x
that contains a JSONB
column named j_data
.
The statement below creates a table y
that contains a JSONB
column named metadata
.
Below is a real world example.
Add values to a JSONB column
To add values to a JSONB
column, simply write the JSON as a string. For example, '{"key": "value"}'
.
Examples
The statement below adds values to table x
.
The statement below adds values to table y
.
The statement below adds values to table product
.
Retrieve data from a JSONB column and casting
To retrieve data from a JSONB
column, use the ->
or ->>
operators to access the JSON object’s properties. The ->
operator returns a jsonb
value, while the ->>
operator returns a varchar value.
For details about the JSON operators, see JSON operators.
JSONB
data types can be cast to other data types such as bool, smallint, int, bigint, decimal, real, and double precision. Casting is performed using the ::data-type
cast notation, such as ::int
for casting to an integer data type.
Examples
Here are some examples for retrieving data and casting:
The output shows that the brand_bad
column contains additional double quotes. So when the target column is a varchar, stick to the dedicated operator ->>
directly rather than using the cast. Only cast a boolean or a number.
JSONB functions and operators
For the full list of JSONB functions and operators, see JSON functions and operators.