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.
Define a JSONB type
Syntax:
JSONB
Examples
The statement below creates a table x
that contains a JSONB
column named j_data
.
CREATE TABLE x (j_data JSONB, d INTEGER);
The statement below creates a table y
that contains a JSONB
column named metadata
.
CREATE TABLE y (id VARCHAR, metadata JSONB);
Below is a real world example.
CREATE TABLE product (
name VARCHAR,
price NUMERIC,
attributes JSONB
);
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
.
INSERT INTO x VALUES ('{"a": 3, "b": 4}', 5);
The statement below adds values to table y
.
INSERT INTO y VALUES ('ABCD1234', '{"color": "blue", "size": "M"}');
The statement below adds values to table product
.
INSERT INTO product (name, price, attributes)
VALUES
(
'T-Shirt',
19.99,
'{"color": "red", "size": "L"}'
);
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 text value.
JSONB -> int
→ JSONB
JSONB -> varchar
→ JSONB
JSONB ->> int
→ varchar
JSONB ->> varchar
→ varchar
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:
INSERT INTO product VALUES ('USB cable', 4.99, '{"lengthInFeet": 3, "backorder": true, "brand": "sin90", "compatible": ["pc", "mac", "phone"]}');
SELECT
(attributes -> 'lengthInFeet')::INT * 30.48 AS cm,
NOT (attributes -> 'backorder')::BOOL AS available,
UPPER(attributes ->> 'brand') AS brand_good,
UPPER((attributes -> 'brand')::VARCHAR) AS brand_bad,
attributes -> 'compatible'
FROM product;
-----Result
cm | available | brand_good | brand_bad | ?column?
-------+-----------+------------+-----------+------------------------
91.44 | f | SIN90 | "SIN90" | ["pc", "mac", "phone"]
(1 row)
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.
Functions
jsonb_typeof(jsonb)
→ varchar
jsonb_array_length(jsonb)
→ int