JSONB ​

To use the JSONB type and all the functions associated with it, you must be connected to a UTF-8 database.

While the feature is in beta, you must also explicitly enable the feature by setting the configuration parameter enable_full_json to ON.

JSONB data type stores JSON documents in a binary format.

Storing data in JSONB columns eliminates the need for JSON string parsing during each function execution. The binary format includes metadata which speeds up operations such as index or key lookups. During insertion, JSON documents are validated and canonicalized: whitespace is removed, and name-value pairs are reordered. Duplicate keys are not permitted.

CREATE TABLE t (c JSONB);

INSERT INTO t VALUES ('{"c":3, "b":2, "a":1}');

INSERT INTO t VALUES ('{"a":');
-- ERROR:  invalid input syntax for type jsonb: {"a":
-- LINE 1: INSERT INTO t VALUES ('{"a":');
                              ^
SELECT * FROM t;
-- {"a":1,"b":2,"c":3}

Comparisons ​

Comparisons on JSONB expressions operate on the JSON representation of the JSONB. Since JSONB reorders the keys into a canonical form, two JSONB objects with the same key/value pairs in different order are equal. The C collation is used for ordering.

SELECT '{"a":1,"b":2}'::JSONB = '{"b":2,"a":1}'::JSONB;
--  t

SELECT '{"a":1,"b":2}'::JSONB < '{"b":2,"a":1}'::JSONB;
--  f