JSON data types are for storing JSON (JavaScript Object
Notation) data, as specified in RFC 7159. Such
data can also be stored as text, but the
JSON data types have the advantage of enforcing that each stored
value is valid according to the JSON rules. There are also
assorted JSON-specific functions and operators available for data
stored in these data types; see Section 9.15.

There are two JSON data types: json and
jsonb. They accept almost identical sets of values as
input. The major practical difference is one of efficiency. The
json data type stores an exact copy of the
input text, which processing functions must reparse on each
execution; while jsonb data is stored in a
decomposed binary format that makes it slightly slower to input
due to added conversion overhead, but significantly faster to
process, since no reparsing is needed. jsonb also supports indexing, which can be a
significant advantage.

Because the json type stores an exact
copy of the input text, it will preserve
semantically-insignificant white space between tokens, as well as
the order of keys within JSON objects. Also, if a JSON object
within the value contains the same key more than once, all the
key/value pairs are kept. (The processing functions consider the
last value as the operative one.) By contrast, jsonb does not preserve white space, does not
preserve the order of object keys, and does not keep duplicate
object keys. If duplicate keys are specified in the input, only
the last value is kept.

In general, most applications should prefer to store JSON data
as jsonb, unless there are quite
specialized needs, such as legacy assumptions about ordering of
object keys.

PostgreSQL allows only one
character set encoding per database. It is therefore not possible
for the JSON types to conform rigidly to the JSON specification
unless the database encoding is UTF8. Attempts to directly
include characters that cannot be represented in the database
encoding will fail; conversely, characters that can be
represented in the database encoding but not in UTF8 will be
allowed.

RFC 7159 permits JSON strings to contain Unicode escape
sequences denoted by \uXXXX. In the input function for the
json type, Unicode escapes are allowed
regardless of the database encoding, and are checked only for
syntactic correctness (that is, that four hex digits follow
\u). However, the input function for
jsonb is stricter: it disallows Unicode
escapes for non-ASCII characters (those above U+007F) unless the database encoding is UTF8. The
jsonb type also rejects \u0000 (because that cannot be represented in
PostgreSQL's text type), and it insists that any use of Unicode
surrogate pairs to designate characters outside the Unicode Basic
Multilingual Plane be correct. Valid Unicode escapes are
converted to the equivalent ASCII or UTF8 character for storage;
this includes folding surrogate pairs into a single
character.

Note: Many of the JSON processing functions
described in Section 9.15
will convert Unicode escapes to regular characters, and will
therefore throw the same types of errors just described even
if their input is of type json not
jsonb. The fact that the json input function does not make these checks
may be considered a historical artifact, although it does
allow for simple storage (without processing) of JSON Unicode
escapes in a non-UTF8 database encoding. In general, it is
best to avoid mixing Unicode escapes in JSON with a non-UTF8
database encoding, if possible.

When converting textual JSON input into jsonb, the primitive types described by
RFC 7159 are effectively
mapped onto native PostgreSQL
types, as shown in Table 8-23.
Therefore, there are some minor additional constraints on what
constitutes valid jsonb data that do not
apply to the json type, nor to JSON in the
abstract, corresponding to limits on what can be represented by
the underlying data type. Notably, jsonb
will reject numbers that are outside the range of the
PostgreSQLnumeric data type, while json
will not. Such implementation-defined restrictions are permitted
by RFC 7159. However, in
practice such problems are far more likely to occur in other
implementations, as it is common to represent JSON's number primitive type as IEEE 754 double precision
floating point (which RFC 7159
explicitly anticipates and allows for). When using JSON as an
interchange format with such systems, the danger of losing
numeric precision compared to data originally stored by
PostgreSQL should be
considered.

Conversely, as noted in the table there are some minor
restrictions on the input format of JSON primitive types that do
not apply to the corresponding PostgreSQL types.

Table 8-23. JSON primitive types and
corresponding PostgreSQL
types

JSON primitive type

PostgreSQL type

Notes

string

text

\u0000 is disallowed, as are
non-ASCII Unicode escapes if database encoding is not
UTF8

As previously stated, when a JSON value is input and then
printed without any additional processing, json outputs the same text that was input, while
jsonb does not preserve
semantically-insignificant details such as whitespace. For
example, note the differences here:

One semantically-insignificant detail worth noting is that
in jsonb, numbers will be printed
according to the behavior of the underlying numeric type. In practice this means that numbers
entered with E notation will be
printed without it, for example:

Representing data as JSON can be considerably more flexible
than the traditional relational data model, which is compelling
in environments where requirements are fluid. It is quite
possible for both approaches to co-exist and complement each
other within the same application. However, even for
applications where maximal flexibility is desired, it is still
recommended that JSON documents have a somewhat fixed
structure. The structure is typically unenforced (though
enforcing some business rules declaratively is possible), but
having a predictable structure makes it easier to write queries
that usefully summarize a set of "documents" (datums) in a table.

JSON data is subject to the same concurrency-control
considerations as any other data type when stored in a table.
Although storing large documents is practicable, keep in mind
that any update acquires a row-level lock on the whole row.
Consider limiting JSON documents to a manageable size in order
to decrease lock contention among updating transactions.
Ideally, JSON documents should each represent an atomic datum
that business rules dictate cannot reasonably be further
subdivided into smaller datums that could be modified
independently.

Testing containment is an important
capability of jsonb. There is no parallel
set of facilities for the json type.
Containment tests whether one jsonb
document has contained within it another one. These examples
return true except as noted:

-- Simple scalar/primitive values contain only the identical value:
SELECT '"foo"'::jsonb @> '"foo"'::jsonb;
-- The array on the right side is contained within the one on the left:
SELECT '[1, 2, 3]'::jsonb @> '[1, 3]'::jsonb;
-- Order of array elements is not significant, so this is also true:
SELECT '[1, 2, 3]'::jsonb @> '[3, 1]'::jsonb;
-- Duplicate array elements don't matter either:
SELECT '[1, 2, 3]'::jsonb @> '[1, 2, 2]'::jsonb;
-- The object with a single pair on the right side is contained
-- within the object on the left side:
SELECT '{"product": "PostgreSQL", "version": 9.4, "jsonb":true}'::jsonb @> '{"version":9.4}'::jsonb;
-- The array on the right side is not considered contained within the
-- array on the left, even though a similar array is nested within it:
SELECT '[1, 2, [1, 3]]'::jsonb @> '[1, 3]'::jsonb; -- yields false
-- But with a layer of nesting, it is contained:
SELECT '[1, 2, [1, 3]]'::jsonb @> '[[1, 3]]'::jsonb;
-- Similarly, containment is not reported here:
SELECT '{"foo": {"bar": "baz"}}'::jsonb @> '{"bar": "baz"}'::jsonb; -- yields false

The general principle is that the contained object must
match the containing object as to structure and data contents,
possibly after discarding some non-matching array elements or
object key/value pairs from the containing object. But remember
that the order of array elements is not significant when doing
a containment match, and duplicate array elements are
effectively considered only once.

As a special exception to the general principle that the
structures must match, an array may contain a primitive
value:

jsonb also has an existence operator, which is a variation on the
theme of containment: it tests whether a string (given as a
text value) appears as an object key or
array element at the top level of the jsonb value. These examples return true except as
noted:

JSON objects are better suited than arrays for testing
containment or existence when there are many keys or elements
involved, because unlike arrays they are internally optimized
for searching, and do not need to be searched linearly.

The various containment and existence operators, along with
all other JSON operators and functions are documented in
Section 9.15.

GIN indexes can be used to efficiently search for keys or
key/value pairs occurring within a large number of jsonb documents (datums). Two GIN "operator classes" are provided, offering
different performance and flexibility trade-offs.

The default GIN operator class for jsonb supports queries with the @>, ?, ?& and ?|
operators. (For details of the semantics that these operators
implement, see Table 9-41.)
An example of creating an index with this operator class
is:

CREATE INDEX idxgin ON api USING gin (jdoc);

The non-default GIN operator class jsonb_path_ops supports indexing the @> operator only. An example of creating an
index with this operator class is:

CREATE INDEX idxginp ON api USING gin (jdoc jsonb_path_ops);

Consider the example of a table that stores JSON documents
retrieved from a third-party web service, with a documented
schema definition. A typical document is:

Still, with appropriate use of expression indexes, the above
query can use an index. If querying for particular items within
the "tags" key is common, defining an
index like this may be worthwhile:

CREATE INDEX idxgintags ON api USING gin ((jdoc -> 'tags'));

Now, the WHERE clause jdoc -> 'tags' ? 'qui' will be recognized as
an application of the indexable operator ? to the indexed expression jdoc -> 'tags'. (More information on
expression indexes can be found in Section 11.7.)

A simple GIN index on the jdoc
column can support this query. But note that such an index will
store copies of every key and value in the jdoc column, whereas the expression index of
the previous example stores only data found under the
tags key. While the simple-index
approach is far more flexible (since it supports queries about
any key), targeted expression indexes are likely to be smaller
and faster to search than a simple index.

Although the jsonb_path_ops
operator class supports only queries with the @> operator, it has notable performance
advantages over the default operator class jsonb_ops. A jsonb_path_ops index is usually much smaller
than a jsonb_ops index over the same
data, and the specificity of searches is better, particularly
when queries contain keys that appear frequently in the data.
Therefore search operations typically perform better than with
the default operator class.

The technical difference between a jsonb_ops and a jsonb_path_ops GIN index is that the former
creates independent index items for each key and value in the
data, while the latter creates index items only for each value
in the data. [1] Basically, each
jsonb_path_ops index item is a hash of
the value and the key(s) leading to it; for example to index
{"foo": {"bar": "baz"}}, a single
index item would be created incorporating all three of
foo, bar, and
baz into the hash value. Thus a
containment query looking for this structure would result in an
extremely specific index search; but there is no way at all to
find out whether foo appears as a key.
On the other hand, a jsonb_ops index
would create three index items representing foo, bar, and
baz separately; then to do the
containment query, it would look for rows containing all three
of these items. While GIN indexes can perform such an AND
search fairly efficiently, it will still be less specific and
slower than the equivalent jsonb_path_ops search, especially if there are a
very large number of rows containing any single one of the
three index items.

A disadvantage of the jsonb_path_ops approach is that it produces no
index entries for JSON structures not containing any values,
such as {"a": {}}. If a search for
documents containing such a structure is requested, it will
require a full-index scan, which is quite slow. jsonb_path_ops is therefore ill-suited for
applications that often perform such searches.

jsonb also supports btree and hash indexes.
These are usually useful only if it's important to check
equality of complete JSON documents. The btree ordering for jsonb
datums is seldom of great interest, but for completeness it
is: