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

Limits

JSONB values cannot exceed 64 KB in their binary form. This limit can be hit even when the original JSON document is under 64 KB, because the binary format contains additional metadata that is used to speed up lookups.

SELECT (REPEAT('9', 63993))::JSONB;
--  ERROR:  Jsonb cast function output is longer than the maximum of 64000 bytes

JSONB values also have a maximum depth limit of 250. The depth of primitives is 0, and the depth of arrays and objects is 1 plus the maximum depth of any of their values.

SELECT '
{
  "level1": {
    "level2": {
      "level3": {
        ...
        "level249": {
          "level250": "deep value"
        }
      }
    }
  }
}
'::JSONB;
--  OK

SELECT '
{
  "level1": {
    "level2": {
      "level3": {
        ...
        "level250": {
          "level251": "deep value"
        }
      }
    }
  }
}
'::JSONB;
--  ERROR:  jsonb exceeded maximum nested depth 250