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 for GROUP BY and ORDER BY clauses or PRIMARY and INDEX 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