<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<HTML
><HEAD
><TITLE
>JSON Types</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK
REV="MADE"
HREF="mailto:pgsql-docs@postgresql.org"><LINK
REL="HOME"
TITLE="PostgreSQL 9.4.15 Documentation"
HREF="index.html"><LINK
REL="UP"
TITLE="Data Types"
HREF="datatype.html"><LINK
REL="PREVIOUS"
TITLE="XML Type"
HREF="datatype-xml.html"><LINK
REL="NEXT"
TITLE="Arrays"
HREF="arrays.html"><LINK
REL="STYLESHEET"
TYPE="text/css"
HREF="stylesheet.css"><META
HTTP-EQUIV="Content-Type"
CONTENT="text/html; charset=ISO-8859-1"><META
NAME="creation"
CONTENT="2017-11-10T00:43:05"></HEAD
><BODY
CLASS="SECT1"
><DIV
CLASS="NAVHEADER"
><TABLE
SUMMARY="Header navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="4"
ALIGN="center"
VALIGN="bottom"
><A
HREF="index.html"
>PostgreSQL 9.4.15 Documentation</A
></TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
TITLE="XML Type"
HREF="datatype-xml.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="datatype.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="60%"
ALIGN="center"
VALIGN="bottom"
>Chapter 8. Data Types</TD
><TD
WIDTH="20%"
ALIGN="right"
VALIGN="top"
><A
TITLE="Arrays"
HREF="arrays.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><DIV
CLASS="SECT1"
><H1
CLASS="SECT1"
><A
NAME="DATATYPE-JSON"
>8.14. <ACRONYM
CLASS="ACRONYM"
>JSON</ACRONYM
> Types</A
></H1
><P
> JSON data types are for storing JSON (JavaScript Object Notation)
data, as specified in <A
HREF="http://rfc7159.net/rfc7159"
TARGET="_top"
>RFC
7159</A
>. Such data can also be stored as <TT
CLASS="TYPE"
>text</TT
>, 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 <A
HREF="functions-json.html"
>Section 9.15</A
>.
</P
><P
> There are two JSON data types: <TT
CLASS="TYPE"
>json</TT
> and <TT
CLASS="TYPE"
>jsonb</TT
>.
They accept <SPAN
CLASS="emphasis"
><I
CLASS="EMPHASIS"
>almost</I
></SPAN
> identical sets of values as
input. The major practical difference is one of efficiency. The
<TT
CLASS="TYPE"
>json</TT
> data type stores an exact copy of the input text,
which processing functions must reparse on each execution; while
<TT
CLASS="TYPE"
>jsonb</TT
> 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. <TT
CLASS="TYPE"
>jsonb</TT
> also supports indexing, which can be a
significant advantage.
</P
><P
> Because the <TT
CLASS="TYPE"
>json</TT
> 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, <TT
CLASS="TYPE"
>jsonb</TT
> 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.
</P
><P
> In general, most applications should prefer to store JSON data as
<TT
CLASS="TYPE"
>jsonb</TT
>, unless there are quite specialized needs, such as
legacy assumptions about ordering of object keys.
</P
><P
> <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> 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.
</P
><P
> RFC 7159 permits JSON strings to contain Unicode escape sequences
denoted by <TT
CLASS="LITERAL"
>\u<TT
CLASS="REPLACEABLE"
><I
>XXXX</I
></TT
></TT
>. In the input
function for the <TT
CLASS="TYPE"
>json</TT
> type, Unicode escapes are allowed
regardless of the database encoding, and are checked only for syntactic
correctness (that is, that four hex digits follow <TT
CLASS="LITERAL"
>\u</TT
>).
However, the input function for <TT
CLASS="TYPE"
>jsonb</TT
> is stricter: it disallows
Unicode escapes for non-ASCII characters (those above <TT
CLASS="LITERAL"
>U+007F</TT
>)
unless the database encoding is UTF8. The <TT
CLASS="TYPE"
>jsonb</TT
> type also
rejects <TT
CLASS="LITERAL"
>\u0000</TT
> (because that cannot be represented in
<SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
>'s <TT
CLASS="TYPE"
>text</TT
> 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.
</P
><DIV
CLASS="NOTE"
><BLOCKQUOTE
CLASS="NOTE"
><P
><B
>Note: </B
> Many of the JSON processing functions described
in <A
HREF="functions-json.html"
>Section 9.15</A
> 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 <TT
CLASS="TYPE"
>json</TT
>
not <TT
CLASS="TYPE"
>jsonb</TT
>. The fact that the <TT
CLASS="TYPE"
>json</TT
> 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.
</P
></BLOCKQUOTE
></DIV
><P
> When converting textual JSON input into <TT
CLASS="TYPE"
>jsonb</TT
>, the primitive
types described by <ACRONYM
CLASS="ACRONYM"
>RFC</ACRONYM
> 7159 are effectively mapped onto
native <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> types, as shown
in <A
HREF="datatype-json.html#JSON-TYPE-MAPPING-TABLE"
>Table 8-23</A
>.
Therefore, there are some minor additional constraints on what
constitutes valid <TT
CLASS="TYPE"
>jsonb</TT
> data that do not apply to
the <TT
CLASS="TYPE"
>json</TT
> type, nor to JSON in the abstract, corresponding
to limits on what can be represented by the underlying data type.
Notably, <TT
CLASS="TYPE"
>jsonb</TT
> will reject numbers that are outside the
range of the <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> <TT
CLASS="TYPE"
>numeric</TT
> data
type, while <TT
CLASS="TYPE"
>json</TT
> will not. Such implementation-defined
restrictions are permitted by <ACRONYM
CLASS="ACRONYM"
>RFC</ACRONYM
> 7159. However, in
practice such problems are far more likely to occur in other
implementations, as it is common to represent JSON's <TT
CLASS="TYPE"
>number</TT
>
primitive type as IEEE 754 double precision floating point
(which <ACRONYM
CLASS="ACRONYM"
>RFC</ACRONYM
> 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 <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> should be considered.
</P
><P
> 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 <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> types.
</P
><DIV
CLASS="TABLE"
><A
NAME="JSON-TYPE-MAPPING-TABLE"
></A
><P
><B
>Table 8-23. JSON primitive types and corresponding <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> types</B
></P
><TABLE
BORDER="1"
CLASS="CALSTABLE"
><COL><COL><COL><THEAD
><TR
><TH
>JSON primitive type</TH
><TH
><SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> type</TH
><TH
>Notes</TH
></TR
></THEAD
><TBODY
><TR
><TD
><TT
CLASS="TYPE"
>string</TT
></TD
><TD
><TT
CLASS="TYPE"
>text</TT
></TD
><TD
><TT
CLASS="LITERAL"
>\u0000</TT
> is disallowed, as are non-ASCII Unicode
escapes if database encoding is not UTF8</TD
></TR
><TR
><TD
><TT
CLASS="TYPE"
>number</TT
></TD
><TD
><TT
CLASS="TYPE"
>numeric</TT
></TD
><TD
><TT
CLASS="LITERAL"
>NaN</TT
> and <TT
CLASS="LITERAL"
>infinity</TT
> values are disallowed</TD
></TR
><TR
><TD
><TT
CLASS="TYPE"
>boolean</TT
></TD
><TD
><TT
CLASS="TYPE"
>boolean</TT
></TD
><TD
>Only lowercase <TT
CLASS="LITERAL"
>true</TT
> and <TT
CLASS="LITERAL"
>false</TT
> spellings are accepted</TD
></TR
><TR
><TD
><TT
CLASS="TYPE"
>null</TT
></TD
><TD
>(none)</TD
><TD
>SQL <TT
CLASS="LITERAL"
>NULL</TT
> is a different concept</TD
></TR
></TBODY
></TABLE
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="JSON-KEYS-ELEMENTS"
>8.14.1. JSON Input and Output Syntax</A
></H2
><P
> The input/output syntax for the JSON data types is as specified in
<ACRONYM
CLASS="ACRONYM"
>RFC</ACRONYM
> 7159.
</P
><P
> The following are all valid <TT
CLASS="TYPE"
>json</TT
> (or <TT
CLASS="TYPE"
>jsonb</TT
>) expressions:
</P><PRE
CLASS="PROGRAMLISTING"
>-- Simple scalar/primitive value
-- Primitive values can be numbers, quoted strings, true, false, or null
SELECT '5'::json;
-- Array of zero or more elements (elements need not be of same type)
SELECT '[1, 2, "foo", null]'::json;
-- Object containing pairs of keys and values
-- Note that object keys must always be quoted strings
SELECT '{"bar": "baz", "balance": 7.77, "active": false}'::json;
-- Arrays and objects can be nested arbitrarily
SELECT '{"foo": [true, "bar"], "tags": {"a": 1, "b": null}}'::json;</PRE
><P>
</P
><P
> As previously stated, when a JSON value is input and then printed without
any additional processing, <TT
CLASS="TYPE"
>json</TT
> outputs the same text that was
input, while <TT
CLASS="TYPE"
>jsonb</TT
> does not preserve semantically-insignificant
details such as whitespace. For example, note the differences here:
</P><PRE
CLASS="PROGRAMLISTING"
>SELECT '{"bar": "baz", "balance": 7.77, "active":false}'::json;
json
-------------------------------------------------
{"bar": "baz", "balance": 7.77, "active":false}
(1 row)
SELECT '{"bar": "baz", "balance": 7.77, "active":false}'::jsonb;
jsonb
--------------------------------------------------
{"bar": "baz", "active": false, "balance": 7.77}
(1 row)</PRE
><P>
One semantically-insignificant detail worth noting is that
in <TT
CLASS="TYPE"
>jsonb</TT
>, numbers will be printed according to the behavior of the
underlying <TT
CLASS="TYPE"
>numeric</TT
> type. In practice this means that numbers
entered with <TT
CLASS="LITERAL"
>E</TT
> notation will be printed without it, for
example:
</P><PRE
CLASS="PROGRAMLISTING"
>SELECT '{"reading": 1.230e-5}'::json, '{"reading": 1.230e-5}'::jsonb;
json | jsonb
-----------------------+-------------------------
{"reading": 1.230e-5} | {"reading": 0.00001230}
(1 row)</PRE
><P>
However, <TT
CLASS="TYPE"
>jsonb</TT
> will preserve trailing fractional zeroes, as seen
in this example, even though those are semantically insignificant for
purposes such as equality checks.
</P
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="JSON-DOC-DESIGN"
>8.14.2. Designing JSON documents effectively</A
></H2
><P
> 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 <SPAN
CLASS="QUOTE"
>"documents"</SPAN
> (datums)
in a table.
</P
><P
> 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.
</P
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="JSON-CONTAINMENT"
>8.14.3. <TT
CLASS="TYPE"
>jsonb</TT
> Containment and Existence</A
></H2
><P
> Testing <I
CLASS="FIRSTTERM"
>containment</I
> is an important capability of
<TT
CLASS="TYPE"
>jsonb</TT
>. There is no parallel set of facilities for the
<TT
CLASS="TYPE"
>json</TT
> type. Containment tests whether
one <TT
CLASS="TYPE"
>jsonb</TT
> document has contained within it another one.
These examples return true except as noted:
</P
><PRE
CLASS="PROGRAMLISTING"
>-- 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 <B
CLASS="EMPHASIS"
>not</B
> 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</PRE
><P
> 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.
</P
><P
> As a special exception to the general principle that the structures
must match, an array may contain a primitive value:
</P
><PRE
CLASS="PROGRAMLISTING"
>-- This array contains the primitive string value:
SELECT '["foo", "bar"]'::jsonb @> '"bar"'::jsonb;
-- This exception is not reciprocal -- non-containment is reported here:
SELECT '"bar"'::jsonb @> '["bar"]'::jsonb; -- yields false</PRE
><P
> <TT
CLASS="TYPE"
>jsonb</TT
> also has an <I
CLASS="FIRSTTERM"
>existence</I
> operator, which is
a variation on the theme of containment: it tests whether a string
(given as a <TT
CLASS="TYPE"
>text</TT
> value) appears as an object key or array
element at the top level of the <TT
CLASS="TYPE"
>jsonb</TT
> value.
These examples return true except as noted:
</P
><PRE
CLASS="PROGRAMLISTING"
>-- String exists as array element:
SELECT '["foo", "bar", "baz"]'::jsonb ? 'bar';
-- String exists as object key:
SELECT '{"foo": "bar"}'::jsonb ? 'foo';
-- Object values are not considered:
SELECT '{"foo": "bar"}'::jsonb ? 'bar'; -- yields false
-- As with containment, existence must match at the top level:
SELECT '{"foo": {"bar": "baz"}}'::jsonb ? 'bar'; -- yields false
-- A string is considered to exist if it matches a primitive JSON string:
SELECT '"foo"'::jsonb ? 'foo';</PRE
><P
> 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.
</P
><DIV
CLASS="TIP"
><BLOCKQUOTE
CLASS="TIP"
><P
><B
>Tip: </B
> Because JSON containment is nested, an appropriate query can skip
explicit selection of sub-objects. As an example, suppose that we have
a <TT
CLASS="STRUCTFIELD"
>doc</TT
> column containing objects at the top level, with
most objects containing <TT
CLASS="LITERAL"
>tags</TT
> fields that contain arrays of
sub-objects. This query finds entries in which sub-objects containing
both <TT
CLASS="LITERAL"
>"term":"paris"</TT
> and <TT
CLASS="LITERAL"
>"term":"food"</TT
> appear,
while ignoring any such keys outside the <TT
CLASS="LITERAL"
>tags</TT
> array:
</P><PRE
CLASS="PROGRAMLISTING"
>SELECT doc->'site_name' FROM websites
WHERE doc @> '{"tags":[{"term":"paris"}, {"term":"food"}]}';</PRE
><P>
One could accomplish the same thing with, say,
</P><PRE
CLASS="PROGRAMLISTING"
>SELECT doc->'site_name' FROM websites
WHERE doc->'tags' @> '[{"term":"paris"}, {"term":"food"}]';</PRE
><P>
but that approach is less flexible, and often less efficient as well.
</P
><P
> On the other hand, the JSON existence operator is not nested: it will
only look for the specified key or array element at top level of the
JSON value.
</P
></BLOCKQUOTE
></DIV
><P
> The various containment and existence operators, along with all other
JSON operators and functions are documented
in <A
HREF="functions-json.html"
>Section 9.15</A
>.
</P
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="JSON-INDEXING"
>8.14.4. <TT
CLASS="TYPE"
>jsonb</TT
> Indexing</A
></H2
><P
> GIN indexes can be used to efficiently search for
keys or key/value pairs occurring within a large number of
<TT
CLASS="TYPE"
>jsonb</TT
> documents (datums).
Two GIN <SPAN
CLASS="QUOTE"
>"operator classes"</SPAN
> are provided, offering different
performance and flexibility trade-offs.
</P
><P
> The default GIN operator class for <TT
CLASS="TYPE"
>jsonb</TT
> supports queries with
the <TT
CLASS="LITERAL"
>@></TT
>, <TT
CLASS="LITERAL"
>?</TT
>, <TT
CLASS="LITERAL"
>?&</TT
>
and <TT
CLASS="LITERAL"
>?|</TT
> operators.
(For details of the semantics that these operators
implement, see <A
HREF="functions-json.html#FUNCTIONS-JSONB-OP-TABLE"
>Table 9-41</A
>.)
An example of creating an index with this operator class is:
</P><PRE
CLASS="PROGRAMLISTING"
>CREATE INDEX idxgin ON api USING gin (jdoc);</PRE
><P>
The non-default GIN operator class <TT
CLASS="LITERAL"
>jsonb_path_ops</TT
>
supports indexing the <TT
CLASS="LITERAL"
>@></TT
> operator only.
An example of creating an index with this operator class is:
</P><PRE
CLASS="PROGRAMLISTING"
>CREATE INDEX idxginp ON api USING gin (jdoc jsonb_path_ops);</PRE
><P>
</P
><P
> 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:
</P><PRE
CLASS="PROGRAMLISTING"
>{
"guid": "9c36adc1-7fb5-4d5b-83b4-90356a46061a",
"name": "Angela Barton",
"is_active": true,
"company": "Magnafone",
"address": "178 Howard Place, Gulf, Washington, 702",
"registered": "2009-11-07T08:53:22 +08:00",
"latitude": 19.793713,
"longitude": 86.513373,
"tags": [
"enim",
"aliquip",
"qui"
]
}</PRE
><P>
We store these documents in a table named <TT
CLASS="STRUCTNAME"
>api</TT
>,
in a <TT
CLASS="TYPE"
>jsonb</TT
> column named <TT
CLASS="STRUCTFIELD"
>jdoc</TT
>.
If a GIN index is created on this column,
queries like the following can make use of the index:
</P><PRE
CLASS="PROGRAMLISTING"
>-- Find documents in which the key "company" has value "Magnafone"
SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"company": "Magnafone"}';</PRE
><P>
However, the index could not be used for queries like the
following, because though the operator <TT
CLASS="LITERAL"
>?</TT
> is indexable,
it is not applied directly to the indexed column <TT
CLASS="STRUCTFIELD"
>jdoc</TT
>:
</P><PRE
CLASS="PROGRAMLISTING"
>-- Find documents in which the key "tags" contains key or array element "qui"
SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc -> 'tags' ? 'qui';</PRE
><P>
Still, with appropriate use of expression indexes, the above
query can use an index. If querying for particular items within
the <TT
CLASS="LITERAL"
>"tags"</TT
> key is common, defining an index like this
may be worthwhile:
</P><PRE
CLASS="PROGRAMLISTING"
>CREATE INDEX idxgintags ON api USING gin ((jdoc -> 'tags'));</PRE
><P>
Now, the <TT
CLASS="LITERAL"
>WHERE</TT
> clause <TT
CLASS="LITERAL"
>jdoc -> 'tags' ? 'qui'</TT
>
will be recognized as an application of the indexable
operator <TT
CLASS="LITERAL"
>?</TT
> to the indexed
expression <TT
CLASS="LITERAL"
>jdoc -> 'tags'</TT
>.
(More information on expression indexes can be found in <A
HREF="indexes-expressional.html"
>Section 11.7</A
>.)
</P
><P
> Another approach to querying is to exploit containment, for example:
</P><PRE
CLASS="PROGRAMLISTING"
>-- Find documents in which the key "tags" contains array element "qui"
SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"tags": ["qui"]}';</PRE
><P>
A simple GIN index on the <TT
CLASS="STRUCTFIELD"
>jdoc</TT
> column can support this
query. But note that such an index will store copies of every key and
value in the <TT
CLASS="STRUCTFIELD"
>jdoc</TT
> column, whereas the expression index
of the previous example stores only data found under
the <TT
CLASS="LITERAL"
>tags</TT
> 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.
</P
><P
> Although the <TT
CLASS="LITERAL"
>jsonb_path_ops</TT
> operator class supports
only queries with the <TT
CLASS="LITERAL"
>@></TT
> operator, it has notable
performance advantages over the default operator
class <TT
CLASS="LITERAL"
>jsonb_ops</TT
>. A <TT
CLASS="LITERAL"
>jsonb_path_ops</TT
>
index is usually much smaller than a <TT
CLASS="LITERAL"
>jsonb_ops</TT
>
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.
</P
><P
> The technical difference between a <TT
CLASS="LITERAL"
>jsonb_ops</TT
>
and a <TT
CLASS="LITERAL"
>jsonb_path_ops</TT
> 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.
<A
NAME="AEN7610"
HREF="#FTN.AEN7610"
><SPAN
CLASS="footnote"
>[1]</SPAN
></A
>
Basically, each <TT
CLASS="LITERAL"
>jsonb_path_ops</TT
> index item is
a hash of the value and the key(s) leading to it; for example to index
<TT
CLASS="LITERAL"
>{"foo": {"bar": "baz"}}</TT
>, a single index item would
be created incorporating all three of <TT
CLASS="LITERAL"
>foo</TT
>, <TT
CLASS="LITERAL"
>bar</TT
>,
and <TT
CLASS="LITERAL"
>baz</TT
> 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 <TT
CLASS="LITERAL"
>foo</TT
>
appears as a key. On the other hand, a <TT
CLASS="LITERAL"
>jsonb_ops</TT
>
index would create three index items representing <TT
CLASS="LITERAL"
>foo</TT
>,
<TT
CLASS="LITERAL"
>bar</TT
>, and <TT
CLASS="LITERAL"
>baz</TT
> 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 <TT
CLASS="LITERAL"
>jsonb_path_ops</TT
> search, especially if
there are a very large number of rows containing any single one of the
three index items.
</P
><P
> A disadvantage of the <TT
CLASS="LITERAL"
>jsonb_path_ops</TT
> approach is
that it produces no index entries for JSON structures not containing
any values, such as <TT
CLASS="LITERAL"
>{"a": {}}</TT
>. If a search for
documents containing such a structure is requested, it will require a
full-index scan, which is quite slow. <TT
CLASS="LITERAL"
>jsonb_path_ops</TT
> is
therefore ill-suited for applications that often perform such searches.
</P
><P
> <TT
CLASS="TYPE"
>jsonb</TT
> also supports <TT
CLASS="LITERAL"
>btree</TT
> and <TT
CLASS="LITERAL"
>hash</TT
>
indexes. These are usually useful only if it's important to check
equality of complete JSON documents.
The <TT
CLASS="LITERAL"
>btree</TT
> ordering for <TT
CLASS="TYPE"
>jsonb</TT
> datums is seldom
of great interest, but for completeness it is:
</P><PRE
CLASS="SYNOPSIS"
><TT
CLASS="REPLACEABLE"
><I
>Object</I
></TT
> > <TT
CLASS="REPLACEABLE"
><I
>Array</I
></TT
> > <TT
CLASS="REPLACEABLE"
><I
>Boolean</I
></TT
> > <TT
CLASS="REPLACEABLE"
><I
>Number</I
></TT
> > <TT
CLASS="REPLACEABLE"
><I
>String</I
></TT
> > <TT
CLASS="REPLACEABLE"
><I
>Null</I
></TT
>
<TT
CLASS="REPLACEABLE"
><I
>Object with n pairs</I
></TT
> > <TT
CLASS="REPLACEABLE"
><I
>object with n - 1 pairs</I
></TT
>
<TT
CLASS="REPLACEABLE"
><I
>Array with n elements</I
></TT
> > <TT
CLASS="REPLACEABLE"
><I
>array with n - 1 elements</I
></TT
></PRE
><P>
Objects with equal numbers of pairs are compared in the order:
</P><PRE
CLASS="SYNOPSIS"
><TT
CLASS="REPLACEABLE"
><I
>key-1</I
></TT
>, <TT
CLASS="REPLACEABLE"
><I
>value-1</I
></TT
>, <TT
CLASS="REPLACEABLE"
><I
>key-2</I
></TT
> ...</PRE
><P>
Note that object keys are compared in their storage order;
in particular, since shorter keys are stored before longer keys, this
can lead to results that might be unintuitive, such as:
</P><PRE
CLASS="PROGRAMLISTING"
>{ "aa": 1, "c": 1} > {"b": 1, "d": 1}</PRE
><P>
Similarly, arrays with equal numbers of elements are compared in the
order:
</P><PRE
CLASS="SYNOPSIS"
><TT
CLASS="REPLACEABLE"
><I
>element-1</I
></TT
>, <TT
CLASS="REPLACEABLE"
><I
>element-2</I
></TT
> ...</PRE
><P>
Primitive JSON values are compared using the same
comparison rules as for the underlying
<SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> data type. Strings are
compared using the default database collation.
</P
></DIV
></DIV
><H3
CLASS="FOOTNOTES"
>Notes</H3
><TABLE
BORDER="0"
CLASS="FOOTNOTES"
WIDTH="100%"
><TR
><TD
ALIGN="LEFT"
VALIGN="TOP"
WIDTH="5%"
><A
NAME="FTN.AEN7610"
HREF="datatype-json.html#AEN7610"
><SPAN
CLASS="footnote"
>[1]</SPAN
></A
></TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
WIDTH="95%"
><P
> For this purpose, the term <SPAN
CLASS="QUOTE"
>"value"</SPAN
> includes array elements,
though JSON terminology sometimes considers array elements distinct
from values within objects.
</P
></TD
></TR
></TABLE
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
SUMMARY="Footer navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
HREF="datatype-xml.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="index.html"
ACCESSKEY="H"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
HREF="arrays.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><ACRONYM
CLASS="ACRONYM"
>XML</ACRONYM
> Type</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="datatype.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>Arrays</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>
distrib
>
Mageia
>
6
>
armv7hl
>
media
>
core-updates
>
by-pkgid
>
a8985c53237f42e0cfdfd17da0a53df3
>
files
>
152
