Use the JSONB
data type to create a column that can store JSON data.
Notes:
bigint
type larger than (2**53)-1
.JSONB
column for GROUP BY
and ORDER BY
clauses or PRIMARY
and INDEX
keys. The exact behavior may change in the future.
STRUCT
if the schema is known,VARCHAR
and convert it into JSONB
later.Syntax:JSONB
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.
To add values to a JSONB
column, simply write the JSON as a string. For example, '{"key": "value"}'
.
The statement below adds values to table x
.
The statement below adds values to table y
.
The statement below adds values to table product
.
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.
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.
For the full list of JSONB functions and operators, see JSON functions and operators.