Movatterモバイル変換

8.14.1. JSON Input and Output Syntax

8.14.2. Designing JSON Documents

8.14.3.jsonb Containment and Existence

8.14.4.jsonb Indexing

8.14.5.jsonb Subscripting

8.14.6. Transforms

8.14.7. jsonpath Type

Note

Many of the JSON processing functions described inSection 9.16 will convert Unicode escapes to regular characters, and will therefore throw the same types of errors just described even if their input is of typejson notjsonb. The fact that thejson 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 database encoding that does not support the represented characters.

Table 8.23. JSON Primitive Types and CorrespondingPostgres Pro Types

The input/output syntax for the JSON data types is as specified inRFC 7159.

The following are all validjson (orjsonb) expressions:

-- Simple scalar/primitive value-- Primitive values can be numbers, quoted strings, true, false, or nullSELECT '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 stringsSELECT '{"bar": "baz", "balance": 7.77, "active": false}'::json;-- Arrays and objects can be nested arbitrarilySELECT '{"foo": [true, "bar"], "tags": {"a": 1, "b": null}}'::json;

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

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)

One semantically-insignificant detail worth noting is that injsonb, numbers will be printed according to the behavior of the underlyingnumeric type. In practice this means that numbers entered withE notation will be printed without it, for example:

SELECT '{"reading": 1.230e-5}'::json, '{"reading": 1.230e-5}'::jsonb;         json          |          jsonb-----------------------+------------------------- {"reading": 1.230e-5} | {"reading": 0.00001230}(1 row)

However,jsonb will preserve trailing fractional zeroes, as seen in this example, even though those are semantically insignificant for purposes such as equality checks.

For the list of built-in functions and operators available for constructing and processing JSON values, seeSection 9.16.

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.

Testingcontainment is an important capability ofjsonb. There is no parallel set of facilities for thejson type. Containment tests whether onejsonb 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 isnot 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-- A top-level key and an empty object is contained:SELECT '{"foo": {"bar": "baz"}}'::jsonb @> '{"foo": {}}'::jsonb;

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:

-- 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

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

-- 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';

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.

SELECT doc->'site_name' FROM websites  WHERE doc @> '{"tags":[{"term":"paris"}, {"term":"food"}]}';

SELECT doc->'site_name' FROM websites  WHERE doc->'tags' @> '[{"term":"paris"}, {"term":"food"}]';

The various containment and existence operators, along with all other JSON operators and functions are documented inSection 9.16.

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

The default GIN operator class forjsonb supports queries with the key-exists operators?,?| and?&, the containment operator@>, and thejsonpath match operators@? and@@. (For details of the semantics that these operators implement, seeTable 9.46.) An example of creating an index with this operator class is:

CREATE INDEX idxgin ON api USING GIN (jdoc);

The non-default GIN operator classjsonb_path_ops does not support the key-exists operators, but it does support@>,@? and@@. 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:

{    "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"    ]}

We store these documents in a table namedapi, in ajsonb column namedjdoc. If a GIN index is created on this column, queries like the following can make use of the index:

-- Find documents in which the key "company" has value "Magnafone"SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"company": "Magnafone"}';

However, the index could not be used for queries like the following, because though the operator? is indexable, it is not applied directly to the indexed columnjdoc:

-- Find documents in which the key "tags" contains key or array element "qui"SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc -> 'tags' ? 'qui';

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, theWHERE clausejdoc -> 'tags' ? 'qui' will be recognized as an application of the indexable operator? to the indexed expressionjdoc -> 'tags'. (More information on expression indexes can be found inSection 11.7.)

Another approach to querying is to exploit containment, for example:

-- Find documents in which the key "tags" contains array element "qui"SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"tags": ["qui"]}';

A simple GIN index on thejdoc column can support this query. But note that such an index will store copies of every key and value in thejdoc column, whereas the expression index of the previous example stores only data found under thetags 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.

GIN indexes also support the@? and@@ operators, which performjsonpath matching. Examples are

SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @? '$.tags[*] ? (@ == "qui")';

SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @@ '$.tags[*] == "qui"';

For these operators, a GIN index extracts clauses of the formaccessors_chain ==constant out of thejsonpath pattern, and does the index search based on the keys and values mentioned in these clauses. The accessors chain may include.key,[*], and[index] accessors. Thejsonb_ops operator class also supports.* and.** accessors, but thejsonb_path_ops operator class does not.

Although thejsonb_path_ops operator class supports only queries with the@>,@? and@@ operators, it has notable performance advantages over the default operator classjsonb_ops. Ajsonb_path_ops index is usually much smaller than ajsonb_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 ajsonb_ops and ajsonb_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.^[7] Basically, eachjsonb_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 offoo,bar, andbaz 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 whetherfoo appears as a key. On the other hand, ajsonb_ops index would create three index items representingfoo,bar, andbaz 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 equivalentjsonb_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 thejsonb_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 supportsbtree andhash indexes. These are usually useful only if it's important to check equality of complete JSON documents. Thebtree ordering forjsonb datums is seldom of great interest, but for completeness it is:

Object >Array >Boolean >Number >String >nullObject with n pairs >object with n - 1 pairsArray with n elements >array with n - 1 elements

with the exception that (for historical reasons) an empty top level array sorts less thannull. Objects with equal numbers of pairs are compared in the order:

key-1,value-1,key-2 ...

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:

{ "aa": 1, "c": 1} > {"b": 1, "d": 1}

Similarly, arrays with equal numbers of elements are compared in the order:

element-1,element-2 ...

Primitive JSON values are compared using the same comparison rules as for the underlyingPostgres Pro data type. Strings are compared using the default database collation.

Thejsonb data type supports array-style subscripting expressions to extract and modify elements. Nested values can be indicated by chaining subscripting expressions, following the same rules as thepath argument in thejsonb_set function. If ajsonb value is an array, numeric subscripts start at zero, and negative integers count backwards from the last element of the array. Slice expressions are not supported. The result of a subscripting expression is always of the jsonb data type.

UPDATE statements may use subscripting in theSET clause to modifyjsonb values. Subscript paths must be traversable for all affected values insofar as they exist. For instance, the pathval['a']['b']['c'] can be traversed all the way toc if everyval,val['a'], andval['a']['b'] is an object. If anyval['a'] orval['a']['b'] is not defined, it will be created as an empty object and filled as necessary. However, if anyval itself or one of the intermediary values is defined as a non-object such as a string, number, orjsonbnull, traversal cannot proceed so an error is raised and the transaction aborted.

An example of subscripting syntax:

-- Extract object value by keySELECT ('{"a": 1}'::jsonb)['a'];-- Extract nested object value by key pathSELECT ('{"a": {"b": {"c": 1}}}'::jsonb)['a']['b']['c'];-- Extract array element by indexSELECT ('[1, "2", null]'::jsonb)[1];-- Update object value by key. Note the quotes around '1': the assigned-- value must be of the jsonb type as wellUPDATE table_name SET jsonb_field['key'] = '1';-- This will raise an error if any record's jsonb_field['a']['b'] is something-- other than an object. For example, the value {"a": 1} has a numeric value-- of the key 'a'.UPDATE table_name SET jsonb_field['a']['b']['c'] = '1';-- Filter records using a WHERE clause with subscripting. Since the result of-- subscripting is jsonb, the value we compare it against must also be jsonb.-- The double quotes make "value" also a valid jsonb string.SELECT * FROM table_name WHERE jsonb_field['key'] = '"value"';

jsonb assignment via subscripting handles a few edge cases differently fromjsonb_set. When a sourcejsonb value isNULL, assignment via subscripting will proceed as if it was an empty JSON value of the type (object or array) implied by the subscript key:

-- Where jsonb_field was NULL, it is now {"a": 1}UPDATE table_name SET jsonb_field['a'] = '1';-- Where jsonb_field was NULL, it is now [1]UPDATE table_name SET jsonb_field[0] = '1';

If an index is specified for an array containing too few elements,NULL elements will be appended until the index is reachable and the value can be set.

-- Where jsonb_field was [], it is now [null, null, 2];-- where jsonb_field was [0], it is now [0, null, 2]UPDATE table_name SET jsonb_field[2] = '2';

Ajsonb value will accept assignments to nonexistent subscript paths as long as the last existing element to be traversed is an object or array, as implied by the corresponding subscript (the element indicated by the last subscript in the path is not traversed and may be anything). Nested array and object structures will be created, and in the former casenull-padded, as specified by the subscript path until the assigned value can be placed.

-- Where jsonb_field was {}, it is now {"a": [{"b": 1}]}UPDATE table_name SET jsonb_field['a'][0]['b'] = '1';-- Where jsonb_field was [], it is now [null, {"a": 1}]UPDATE table_name SET jsonb_field[1]['a'] = '1';

Additional extensions are available that implement transforms for thejsonb type for different procedural languages.

The extensions for PL/Perl are calledjsonb_plperl andjsonb_plperlu. If you use them,jsonb values are mapped to Perl arrays, hashes, and scalars, as appropriate.

The extension for PL/Python is calledjsonb_plpython3u. If you use it,jsonb values are mapped to Python dictionaries, lists, and scalars, as appropriate.

Of these extensions,jsonb_plperl is considered“trusted”, that is, it can be installed by non-superusers who haveCREATE privilege on the current database. The rest require superuser privilege to install.

Thejsonpath type implements support for the SQL/JSON path language inPostgres Pro to efficiently query JSON data. It provides a binary representation of the parsed SQL/JSON path expression that specifies the items to be retrieved by the path engine from the JSON data for further processing with the SQL/JSON query functions.

The semantics of SQL/JSON path predicates and operators generally follow SQL. At the same time, to provide a natural way of working with JSON data, SQL/JSON path syntax uses some JavaScript conventions:

Numeric literals in SQL/JSON path expressions follow JavaScript rules, which are different from both SQL and JSON in some minor details. For example, SQL/JSON path allows.1 and1., which are invalid in JSON. Non-decimal integer literals and underscore separators are supported, for example,1_000_000,0x1EEE_FFFF,0o273,0b100101. In SQL/JSON path (and in JavaScript, but not in SQL proper), there must not be an underscore separator directly after the radix prefix.

An SQL/JSON path expression is typically written in an SQL query as an SQL character string literal, so it must be enclosed in single quotes, and any single quotes desired within the value must be doubled (seeSection 4.1.2.1). Some forms of path expressions require string literals within them. These embedded string literals follow JavaScript/ECMAScript conventions: they must be surrounded by double quotes, and backslash escapes may be used within them to represent otherwise-hard-to-type characters. In particular, the way to write a double quote within an embedded string literal is\", and to write a backslash itself, you must write\\. Other special backslash sequences include those recognized in JavaScript strings:\b,\f,\n,\r,\t,\v for various ASCII control characters,\xNN for a character code written with only two hex digits,\uNNNN for a Unicode character identified by its 4-hex-digit code point, and\u{N...} for a Unicode character code point written with 1 to 6 hex digits.

A path expression consists of a sequence of path elements, which can be any of the following:

For details on usingjsonpath expressions with SQL/JSON query functions, seeSection 9.16.2.