1 <?xml version="1.0" encoding="UTF-8" standalone="no"?>
2 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>8.14. JSON Types</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="datatype-xml.html" title="8.13. XML Type" /><link rel="next" href="arrays.html" title="8.15. Arrays" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">8.14. <acronym class="acronym">JSON</acronym> Types</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="datatype-xml.html" title="8.13. XML Type">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="datatype.html" title="Chapter 8. Data Types">Up</a></td><th width="60%" align="center">Chapter 8. Data Types</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="arrays.html" title="8.15. Arrays">Next</a></td></tr></table><hr /></div><div class="sect1" id="DATATYPE-JSON"><div class="titlepage"><div><div><h2 class="title" style="clear: both">8.14. <acronym class="acronym">JSON</acronym> Types <a href="#DATATYPE-JSON" class="id_link">#</a></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="datatype-json.html#JSON-KEYS-ELEMENTS">8.14.1. JSON Input and Output Syntax</a></span></dt><dt><span class="sect2"><a href="datatype-json.html#JSON-DOC-DESIGN">8.14.2. Designing JSON Documents</a></span></dt><dt><span class="sect2"><a href="datatype-json.html#JSON-CONTAINMENT">8.14.3. <code class="type">jsonb</code> Containment and Existence</a></span></dt><dt><span class="sect2"><a href="datatype-json.html#JSON-INDEXING">8.14.4. <code class="type">jsonb</code> Indexing</a></span></dt><dt><span class="sect2"><a href="datatype-json.html#JSONB-SUBSCRIPTING">8.14.5. <code class="type">jsonb</code> Subscripting</a></span></dt><dt><span class="sect2"><a href="datatype-json.html#DATATYPE-JSON-TRANSFORMS">8.14.6. Transforms</a></span></dt><dt><span class="sect2"><a href="datatype-json.html#DATATYPE-JSONPATH">8.14.7. jsonpath Type</a></span></dt></dl></div><a id="id-1.5.7.22.2" class="indexterm"></a><a id="id-1.5.7.22.3" class="indexterm"></a><p>
3 JSON data types are for storing JSON (JavaScript Object Notation)
4 data, as specified in <a class="ulink" href="https://datatracker.ietf.org/doc/html/rfc7159" target="_top">RFC
5 7159</a>. Such data can also be stored as <code class="type">text</code>, but
6 the JSON data types have the advantage of enforcing that each
7 stored value is valid according to the JSON rules. There are also
8 assorted JSON-specific functions and operators available for data stored
9 in these data types; see <a class="xref" href="functions-json.html" title="9.16. JSON Functions and Operators">Section 9.16</a>.
11 <span class="productname">PostgreSQL</span> offers two types for storing JSON
12 data: <code class="type">json</code> and <code class="type">jsonb</code>. To implement efficient query
13 mechanisms for these data types, <span class="productname">PostgreSQL</span>
14 also provides the <code class="type">jsonpath</code> data type described in
15 <a class="xref" href="datatype-json.html#DATATYPE-JSONPATH" title="8.14.7. jsonpath Type">Section 8.14.7</a>.
17 The <code class="type">json</code> and <code class="type">jsonb</code> data types
18 accept <span class="emphasis"><em>almost</em></span> identical sets of values as
19 input. The major practical difference is one of efficiency. The
20 <code class="type">json</code> data type stores an exact copy of the input text,
21 which processing functions must reparse on each execution; while
22 <code class="type">jsonb</code> data is stored in a decomposed binary format that
23 makes it slightly slower to input due to added conversion
24 overhead, but significantly faster to process, since no reparsing
25 is needed. <code class="type">jsonb</code> also supports indexing, which can be a
26 significant advantage.
28 Because the <code class="type">json</code> type stores an exact copy of the input text, it
29 will preserve semantically-insignificant white space between tokens, as
30 well as the order of keys within JSON objects. Also, if a JSON object
31 within the value contains the same key more than once, all the key/value
32 pairs are kept. (The processing functions consider the last value as the
33 operative one.) By contrast, <code class="type">jsonb</code> does not preserve white
34 space, does not preserve the order of object keys, and does not keep
35 duplicate object keys. If duplicate keys are specified in the input,
36 only the last value is kept.
38 In general, most applications should prefer to store JSON data as
39 <code class="type">jsonb</code>, unless there are quite specialized needs, such as
40 legacy assumptions about ordering of object keys.
42 <acronym class="acronym">RFC</acronym> 7159 specifies that JSON strings should be encoded in UTF8.
43 It is therefore not possible for the JSON
44 types to conform rigidly to the JSON specification unless the database
45 encoding is UTF8. Attempts to directly include characters that
46 cannot be represented in the database encoding will fail; conversely,
47 characters that can be represented in the database encoding but not
48 in UTF8 will be allowed.
50 <acronym class="acronym">RFC</acronym> 7159 permits JSON strings to contain Unicode escape sequences
51 denoted by <code class="literal">\u<em class="replaceable"><code>XXXX</code></em></code>. In the input
52 function for the <code class="type">json</code> type, Unicode escapes are allowed
53 regardless of the database encoding, and are checked only for syntactic
54 correctness (that is, that four hex digits follow <code class="literal">\u</code>).
55 However, the input function for <code class="type">jsonb</code> is stricter: it disallows
56 Unicode escapes for characters that cannot be represented in the database
57 encoding. The <code class="type">jsonb</code> type also
58 rejects <code class="literal">\u0000</code> (because that cannot be represented in
59 <span class="productname">PostgreSQL</span>'s <code class="type">text</code> type), and it insists
60 that any use of Unicode surrogate pairs to designate characters outside
61 the Unicode Basic Multilingual Plane be correct. Valid Unicode escapes
62 are converted to the equivalent single character for storage;
63 this includes folding surrogate pairs into a single character.
64 </p><div class="note"><h3 class="title">Note</h3><p>
65 Many of the JSON processing functions described
66 in <a class="xref" href="functions-json.html" title="9.16. JSON Functions and Operators">Section 9.16</a> will convert Unicode escapes to
67 regular characters, and will therefore throw the same types of errors
68 just described even if their input is of type <code class="type">json</code>
69 not <code class="type">jsonb</code>. The fact that the <code class="type">json</code> input function does
70 not make these checks may be considered a historical artifact, although
71 it does allow for simple storage (without processing) of JSON Unicode
72 escapes in a database encoding that does not support the represented
75 When converting textual JSON input into <code class="type">jsonb</code>, the primitive
76 types described by <acronym class="acronym">RFC</acronym> 7159 are effectively mapped onto
77 native <span class="productname">PostgreSQL</span> types, as shown
78 in <a class="xref" href="datatype-json.html#JSON-TYPE-MAPPING-TABLE" title="Table 8.23. JSON Primitive Types and Corresponding PostgreSQL Types">Table 8.23</a>.
79 Therefore, there are some minor additional constraints on what
80 constitutes valid <code class="type">jsonb</code> data that do not apply to
81 the <code class="type">json</code> type, nor to JSON in the abstract, corresponding
82 to limits on what can be represented by the underlying data type.
83 Notably, <code class="type">jsonb</code> will reject numbers that are outside the
84 range of the <span class="productname">PostgreSQL</span> <code class="type">numeric</code> data
85 type, while <code class="type">json</code> will not. Such implementation-defined
86 restrictions are permitted by <acronym class="acronym">RFC</acronym> 7159. However, in
87 practice such problems are far more likely to occur in other
88 implementations, as it is common to represent JSON's <code class="type">number</code>
89 primitive type as IEEE 754 double precision floating point
90 (which <acronym class="acronym">RFC</acronym> 7159 explicitly anticipates and allows for).
91 When using JSON as an interchange format with such systems, the danger
92 of losing numeric precision compared to data originally stored
93 by <span class="productname">PostgreSQL</span> should be considered.
95 Conversely, as noted in the table there are some minor restrictions on
96 the input format of JSON primitive types that do not apply to
97 the corresponding <span class="productname">PostgreSQL</span> types.
98 </p><div class="table" id="JSON-TYPE-MAPPING-TABLE"><p class="title"><strong>Table 8.23. JSON Primitive Types and Corresponding <span class="productname">PostgreSQL</span> Types</strong></p><div class="table-contents"><table class="table" summary="JSON Primitive Types and Corresponding PostgreSQL Types" border="1"><colgroup><col class="col1" /><col class="col2" /><col class="col3" /></colgroup><thead><tr><th>JSON primitive type</th><th><span class="productname">PostgreSQL</span> type</th><th>Notes</th></tr></thead><tbody><tr><td><code class="type">string</code></td><td><code class="type">text</code></td><td><code class="literal">\u0000</code> is disallowed, as are Unicode escapes
99 representing characters not available in the database encoding</td></tr><tr><td><code class="type">number</code></td><td><code class="type">numeric</code></td><td><code class="literal">NaN</code> and <code class="literal">infinity</code> values are disallowed</td></tr><tr><td><code class="type">boolean</code></td><td><code class="type">boolean</code></td><td>Only lowercase <code class="literal">true</code> and <code class="literal">false</code> spellings are accepted</td></tr><tr><td><code class="type">null</code></td><td>(none)</td><td>SQL <code class="literal">NULL</code> is a different concept</td></tr></tbody></table></div></div><br class="table-break" /><div class="sect2" id="JSON-KEYS-ELEMENTS"><div class="titlepage"><div><div><h3 class="title">8.14.1. JSON Input and Output Syntax <a href="#JSON-KEYS-ELEMENTS" class="id_link">#</a></h3></div></div></div><p>
100 The input/output syntax for the JSON data types is as specified in
101 <acronym class="acronym">RFC</acronym> 7159.
103 The following are all valid <code class="type">json</code> (or <code class="type">jsonb</code>) expressions:
104 </p><pre class="programlisting">
105 -- Simple scalar/primitive value
106 -- Primitive values can be numbers, quoted strings, true, false, or null
109 -- Array of zero or more elements (elements need not be of same type)
110 SELECT '[1, 2, "foo", null]'::json;
112 -- Object containing pairs of keys and values
113 -- Note that object keys must always be quoted strings
114 SELECT '{"bar": "baz", "balance": 7.77, "active": false}'::json;
116 -- Arrays and objects can be nested arbitrarily
117 SELECT '{"foo": [true, "bar"], "tags": {"a": 1, "b": null}}'::json;
120 As previously stated, when a JSON value is input and then printed without
121 any additional processing, <code class="type">json</code> outputs the same text that was
122 input, while <code class="type">jsonb</code> does not preserve semantically-insignificant
123 details such as whitespace. For example, note the differences here:
124 </p><pre class="programlisting">
125 SELECT '{"bar": "baz", "balance": 7.77, "active":false}'::json;
127 -------------------------------------------------
128 {"bar": "baz", "balance": 7.77, "active":false}
131 SELECT '{"bar": "baz", "balance": 7.77, "active":false}'::jsonb;
133 --------------------------------------------------
134 {"bar": "baz", "active": false, "balance": 7.77}
137 One semantically-insignificant detail worth noting is that
138 in <code class="type">jsonb</code>, numbers will be printed according to the behavior of the
139 underlying <code class="type">numeric</code> type. In practice this means that numbers
140 entered with <code class="literal">E</code> notation will be printed without it, for
142 </p><pre class="programlisting">
143 SELECT '{"reading": 1.230e-5}'::json, '{"reading": 1.230e-5}'::jsonb;
145 -----------------------+-------------------------
146 {"reading": 1.230e-5} | {"reading": 0.00001230}
149 However, <code class="type">jsonb</code> will preserve trailing fractional zeroes, as seen
150 in this example, even though those are semantically insignificant for
151 purposes such as equality checks.
153 For the list of built-in functions and operators available for
154 constructing and processing JSON values, see <a class="xref" href="functions-json.html" title="9.16. JSON Functions and Operators">Section 9.16</a>.
155 </p></div><div class="sect2" id="JSON-DOC-DESIGN"><div class="titlepage"><div><div><h3 class="title">8.14.2. Designing JSON Documents <a href="#JSON-DOC-DESIGN" class="id_link">#</a></h3></div></div></div><p>
156 Representing data as JSON can be considerably more flexible than
157 the traditional relational data model, which is compelling in
158 environments where requirements are fluid. It is quite possible
159 for both approaches to co-exist and complement each other within
160 the same application. However, even for applications where maximal
161 flexibility is desired, it is still recommended that JSON documents
162 have a somewhat fixed structure. The structure is typically
163 unenforced (though enforcing some business rules declaratively is
164 possible), but having a predictable structure makes it easier to write
165 queries that usefully summarize a set of <span class="quote">“<span class="quote">documents</span>”</span> (datums)
168 JSON data is subject to the same concurrency-control
169 considerations as any other data type when stored in a table.
170 Although storing large documents is practicable, keep in mind that
171 any update acquires a row-level lock on the whole row.
172 Consider limiting JSON documents to a
173 manageable size in order to decrease lock contention among updating
174 transactions. Ideally, JSON documents should each
175 represent an atomic datum that business rules dictate cannot
176 reasonably be further subdivided into smaller datums that
177 could be modified independently.
178 </p></div><div class="sect2" id="JSON-CONTAINMENT"><div class="titlepage"><div><div><h3 class="title">8.14.3. <code class="type">jsonb</code> Containment and Existence <a href="#JSON-CONTAINMENT" class="id_link">#</a></h3></div></div></div><a id="id-1.5.7.22.17.2" class="indexterm"></a><a id="id-1.5.7.22.17.3" class="indexterm"></a><p>
179 Testing <em class="firstterm">containment</em> is an important capability of
180 <code class="type">jsonb</code>. There is no parallel set of facilities for the
181 <code class="type">json</code> type. Containment tests whether
182 one <code class="type">jsonb</code> document has contained within it another one.
183 These examples return true except as noted:
184 </p><pre class="programlisting">
185 -- Simple scalar/primitive values contain only the identical value:
186 SELECT '"foo"'::jsonb @> '"foo"'::jsonb;
188 -- The array on the right side is contained within the one on the left:
189 SELECT '[1, 2, 3]'::jsonb @> '[1, 3]'::jsonb;
191 -- Order of array elements is not significant, so this is also true:
192 SELECT '[1, 2, 3]'::jsonb @> '[3, 1]'::jsonb;
194 -- Duplicate array elements don't matter either:
195 SELECT '[1, 2, 3]'::jsonb @> '[1, 2, 2]'::jsonb;
197 -- The object with a single pair on the right side is contained
198 -- within the object on the left side:
199 SELECT '{"product": "PostgreSQL", "version": 9.4, "jsonb": true}'::jsonb @> '{"version": 9.4}'::jsonb;
201 -- The array on the right side is <span class="emphasis"><strong>not</strong></span> considered contained within the
202 -- array on the left, even though a similar array is nested within it:
203 SELECT '[1, 2, [1, 3]]'::jsonb @> '[1, 3]'::jsonb; -- yields false
205 -- But with a layer of nesting, it is contained:
206 SELECT '[1, 2, [1, 3]]'::jsonb @> '[[1, 3]]'::jsonb;
208 -- Similarly, containment is not reported here:
209 SELECT '{"foo": {"bar": "baz"}}'::jsonb @> '{"bar": "baz"}'::jsonb; -- yields false
211 -- A top-level key and an empty object is contained:
212 SELECT '{"foo": {"bar": "baz"}}'::jsonb @> '{"foo": {}}'::jsonb;
214 The general principle is that the contained object must match the
215 containing object as to structure and data contents, possibly after
216 discarding some non-matching array elements or object key/value pairs
217 from the containing object.
218 But remember that the order of array elements is not significant when
219 doing a containment match, and duplicate array elements are effectively
220 considered only once.
222 As a special exception to the general principle that the structures
223 must match, an array may contain a primitive value:
224 </p><pre class="programlisting">
225 -- This array contains the primitive string value:
226 SELECT '["foo", "bar"]'::jsonb @> '"bar"'::jsonb;
228 -- This exception is not reciprocal -- non-containment is reported here:
229 SELECT '"bar"'::jsonb @> '["bar"]'::jsonb; -- yields false
231 <code class="type">jsonb</code> also has an <em class="firstterm">existence</em> operator, which is
232 a variation on the theme of containment: it tests whether a string
233 (given as a <code class="type">text</code> value) appears as an object key or array
234 element at the top level of the <code class="type">jsonb</code> value.
235 These examples return true except as noted:
236 </p><pre class="programlisting">
237 -- String exists as array element:
238 SELECT '["foo", "bar", "baz"]'::jsonb ? 'bar';
240 -- String exists as object key:
241 SELECT '{"foo": "bar"}'::jsonb ? 'foo';
243 -- Object values are not considered:
244 SELECT '{"foo": "bar"}'::jsonb ? 'bar'; -- yields false
246 -- As with containment, existence must match at the top level:
247 SELECT '{"foo": {"bar": "baz"}}'::jsonb ? 'bar'; -- yields false
249 -- A string is considered to exist if it matches a primitive JSON string:
250 SELECT '"foo"'::jsonb ? 'foo';
252 JSON objects are better suited than arrays for testing containment or
253 existence when there are many keys or elements involved, because
254 unlike arrays they are internally optimized for searching, and do not
255 need to be searched linearly.
256 </p><div class="tip"><h3 class="title">Tip</h3><p>
257 Because JSON containment is nested, an appropriate query can skip
258 explicit selection of sub-objects. As an example, suppose that we have
259 a <code class="structfield">doc</code> column containing objects at the top level, with
260 most objects containing <code class="literal">tags</code> fields that contain arrays of
261 sub-objects. This query finds entries in which sub-objects containing
262 both <code class="literal">"term":"paris"</code> and <code class="literal">"term":"food"</code> appear,
263 while ignoring any such keys outside the <code class="literal">tags</code> array:
264 </p><pre class="programlisting">
265 SELECT doc->'site_name' FROM websites
266 WHERE doc @> '{"tags":[{"term":"paris"}, {"term":"food"}]}';
268 One could accomplish the same thing with, say,
269 </p><pre class="programlisting">
270 SELECT doc->'site_name' FROM websites
271 WHERE doc->'tags' @> '[{"term":"paris"}, {"term":"food"}]';
273 but that approach is less flexible, and often less efficient as well.
275 On the other hand, the JSON existence operator is not nested: it will
276 only look for the specified key or array element at top level of the
279 The various containment and existence operators, along with all other
280 JSON operators and functions are documented
281 in <a class="xref" href="functions-json.html" title="9.16. JSON Functions and Operators">Section 9.16</a>.
282 </p></div><div class="sect2" id="JSON-INDEXING"><div class="titlepage"><div><div><h3 class="title">8.14.4. <code class="type">jsonb</code> Indexing <a href="#JSON-INDEXING" class="id_link">#</a></h3></div></div></div><a id="id-1.5.7.22.18.2" class="indexterm"></a><p>
283 GIN indexes can be used to efficiently search for
284 keys or key/value pairs occurring within a large number of
285 <code class="type">jsonb</code> documents (datums).
286 Two GIN <span class="quote">“<span class="quote">operator classes</span>”</span> are provided, offering different
287 performance and flexibility trade-offs.
289 The default GIN operator class for <code class="type">jsonb</code> supports queries with
290 the key-exists operators <code class="literal">?</code>, <code class="literal">?|</code>
291 and <code class="literal">?&</code>, the containment operator
292 <code class="literal">@></code>, and the <code class="type">jsonpath</code> match
293 operators <code class="literal">@?</code> and <code class="literal">@@</code>.
294 (For details of the semantics that these operators
295 implement, see <a class="xref" href="functions-json.html#FUNCTIONS-JSONB-OP-TABLE" title="Table 9.48. Additional jsonb Operators">Table 9.48</a>.)
296 An example of creating an index with this operator class is:
297 </p><pre class="programlisting">
298 CREATE INDEX idxgin ON api USING GIN (jdoc);
300 The non-default GIN operator class <code class="literal">jsonb_path_ops</code>
301 does not support the key-exists operators, but it does support
302 <code class="literal">@></code>, <code class="literal">@?</code> and <code class="literal">@@</code>.
303 An example of creating an index with this operator class is:
304 </p><pre class="programlisting">
305 CREATE INDEX idxginp ON api USING GIN (jdoc jsonb_path_ops);
308 Consider the example of a table that stores JSON documents
309 retrieved from a third-party web service, with a documented schema
310 definition. A typical document is:
311 </p><pre class="programlisting">
313 "guid": "9c36adc1-7fb5-4d5b-83b4-90356a46061a",
314 "name": "Angela Barton",
316 "company": "Magnafone",
317 "address": "178 Howard Place, Gulf, Washington, 702",
318 "registered": "2009-11-07T08:53:22 +08:00",
319 "latitude": 19.793713,
320 "longitude": 86.513373,
328 We store these documents in a table named <code class="structname">api</code>,
329 in a <code class="type">jsonb</code> column named <code class="structfield">jdoc</code>.
330 If a GIN index is created on this column,
331 queries like the following can make use of the index:
332 </p><pre class="programlisting">
333 -- Find documents in which the key "company" has value "Magnafone"
334 SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"company": "Magnafone"}';
336 However, the index could not be used for queries like the
337 following, because though the operator <code class="literal">?</code> is indexable,
338 it is not applied directly to the indexed column <code class="structfield">jdoc</code>:
339 </p><pre class="programlisting">
340 -- Find documents in which the key "tags" contains key or array element "qui"
341 SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc -> 'tags' ? 'qui';
343 Still, with appropriate use of expression indexes, the above
344 query can use an index. If querying for particular items within
345 the <code class="literal">"tags"</code> key is common, defining an index like this
347 </p><pre class="programlisting">
348 CREATE INDEX idxgintags ON api USING GIN ((jdoc -> 'tags'));
350 Now, the <code class="literal">WHERE</code> clause <code class="literal">jdoc -> 'tags' ? 'qui'</code>
351 will be recognized as an application of the indexable
352 operator <code class="literal">?</code> to the indexed
353 expression <code class="literal">jdoc -> 'tags'</code>.
354 (More information on expression indexes can be found in <a class="xref" href="indexes-expressional.html" title="11.7. Indexes on Expressions">Section 11.7</a>.)
356 Another approach to querying is to exploit containment, for example:
357 </p><pre class="programlisting">
358 -- Find documents in which the key "tags" contains array element "qui"
359 SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"tags": ["qui"]}';
361 A simple GIN index on the <code class="structfield">jdoc</code> column can support this
362 query. But note that such an index will store copies of every key and
363 value in the <code class="structfield">jdoc</code> column, whereas the expression index
364 of the previous example stores only data found under
365 the <code class="literal">tags</code> key. While the simple-index approach is far more
366 flexible (since it supports queries about any key), targeted expression
367 indexes are likely to be smaller and faster to search than a simple
370 GIN indexes also support the <code class="literal">@?</code>
371 and <code class="literal">@@</code> operators, which
372 perform <code class="type">jsonpath</code> matching. Examples are
373 </p><pre class="programlisting">
374 SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @? '$.tags[*] ? (@ == "qui")';
376 </p><pre class="programlisting">
377 SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @@ '$.tags[*] == "qui"';
379 For these operators, a GIN index extracts clauses of the form
380 <code class="literal"><em class="replaceable"><code>accessors_chain</code></em>
381 == <em class="replaceable"><code>constant</code></em></code> out of
382 the <code class="type">jsonpath</code> pattern, and does the index search based on
383 the keys and values mentioned in these clauses. The accessors chain
384 may include <code class="literal">.<em class="replaceable"><code>key</code></em></code>,
385 <code class="literal">[*]</code>,
386 and <code class="literal">[<em class="replaceable"><code>index</code></em>]</code> accessors.
387 The <code class="literal">jsonb_ops</code> operator class also
388 supports <code class="literal">.*</code> and <code class="literal">.**</code> accessors,
389 but the <code class="literal">jsonb_path_ops</code> operator class does not.
391 Although the <code class="literal">jsonb_path_ops</code> operator class supports
392 only queries with the <code class="literal">@></code>, <code class="literal">@?</code>
393 and <code class="literal">@@</code> operators, it has notable
394 performance advantages over the default operator
395 class <code class="literal">jsonb_ops</code>. A <code class="literal">jsonb_path_ops</code>
396 index is usually much smaller than a <code class="literal">jsonb_ops</code>
397 index over the same data, and the specificity of searches is better,
398 particularly when queries contain keys that appear frequently in the
399 data. Therefore search operations typically perform better
400 than with the default operator class.
402 The technical difference between a <code class="literal">jsonb_ops</code>
403 and a <code class="literal">jsonb_path_ops</code> GIN index is that the former
404 creates independent index items for each key and value in the data,
405 while the latter creates index items only for each value in the
407 <a href="#ftn.id-1.5.7.22.18.9.3" class="footnote"><sup class="footnote" id="id-1.5.7.22.18.9.3">[7]</sup></a>
408 Basically, each <code class="literal">jsonb_path_ops</code> index item is
409 a hash of the value and the key(s) leading to it; for example to index
410 <code class="literal">{"foo": {"bar": "baz"}}</code>, a single index item would
411 be created incorporating all three of <code class="literal">foo</code>, <code class="literal">bar</code>,
412 and <code class="literal">baz</code> into the hash value. Thus a containment query
413 looking for this structure would result in an extremely specific index
414 search; but there is no way at all to find out whether <code class="literal">foo</code>
415 appears as a key. On the other hand, a <code class="literal">jsonb_ops</code>
416 index would create three index items representing <code class="literal">foo</code>,
417 <code class="literal">bar</code>, and <code class="literal">baz</code> separately; then to do the
418 containment query, it would look for rows containing all three of
419 these items. While GIN indexes can perform such an AND search fairly
420 efficiently, it will still be less specific and slower than the
421 equivalent <code class="literal">jsonb_path_ops</code> search, especially if
422 there are a very large number of rows containing any single one of the
425 A disadvantage of the <code class="literal">jsonb_path_ops</code> approach is
426 that it produces no index entries for JSON structures not containing
427 any values, such as <code class="literal">{"a": {}}</code>. If a search for
428 documents containing such a structure is requested, it will require a
429 full-index scan, which is quite slow. <code class="literal">jsonb_path_ops</code> is
430 therefore ill-suited for applications that often perform such searches.
432 <code class="type">jsonb</code> also supports <code class="literal">btree</code> and <code class="literal">hash</code>
433 indexes. These are usually useful only if it's important to check
434 equality of complete JSON documents.
435 The <code class="literal">btree</code> ordering for <code class="type">jsonb</code> datums is seldom
436 of great interest, but for completeness it is:
437 </p><pre class="synopsis">
438 <em class="replaceable"><code>Object</code></em> > <em class="replaceable"><code>Array</code></em> > <em class="replaceable"><code>Boolean</code></em> > <em class="replaceable"><code>Number</code></em> > <em class="replaceable"><code>String</code></em> > <em class="replaceable"><code>null</code></em>
440 <em class="replaceable"><code>Object with n pairs</code></em> > <em class="replaceable"><code>object with n - 1 pairs</code></em>
442 <em class="replaceable"><code>Array with n elements</code></em> > <em class="replaceable"><code>array with n - 1 elements</code></em>
444 with the exception that (for historical reasons) an empty top level array sorts less than <em class="replaceable"><code>null</code></em>.
445 Objects with equal numbers of pairs are compared in the order:
446 </p><pre class="synopsis">
447 <em class="replaceable"><code>key-1</code></em>, <em class="replaceable"><code>value-1</code></em>, <em class="replaceable"><code>key-2</code></em> ...
449 Note that object keys are compared in their storage order;
450 in particular, since shorter keys are stored before longer keys, this
451 can lead to results that might be unintuitive, such as:
452 </p><pre class="programlisting">
453 { "aa": 1, "c": 1} > {"b": 1, "d": 1}
455 Similarly, arrays with equal numbers of elements are compared in the
457 </p><pre class="synopsis">
458 <em class="replaceable"><code>element-1</code></em>, <em class="replaceable"><code>element-2</code></em> ...
460 Primitive JSON values are compared using the same
461 comparison rules as for the underlying
462 <span class="productname">PostgreSQL</span> data type. Strings are
463 compared using the default database collation.
464 </p></div><div class="sect2" id="JSONB-SUBSCRIPTING"><div class="titlepage"><div><div><h3 class="title">8.14.5. <code class="type">jsonb</code> Subscripting <a href="#JSONB-SUBSCRIPTING" class="id_link">#</a></h3></div></div></div><p>
465 The <code class="type">jsonb</code> data type supports array-style subscripting expressions
466 to extract and modify elements. Nested values can be indicated by chaining
467 subscripting expressions, following the same rules as the <code class="literal">path</code>
468 argument in the <code class="literal">jsonb_set</code> function. If a <code class="type">jsonb</code>
469 value is an array, numeric subscripts start at zero, and negative integers count
470 backwards from the last element of the array. Slice expressions are not supported.
471 The result of a subscripting expression is always of the jsonb data type.
473 <code class="command">UPDATE</code> statements may use subscripting in the
474 <code class="literal">SET</code> clause to modify <code class="type">jsonb</code> values. Subscript
475 paths must be traversable for all affected values insofar as they exist. For
476 instance, the path <code class="literal">val['a']['b']['c']</code> can be traversed all
477 the way to <code class="literal">c</code> if every <code class="literal">val</code>,
478 <code class="literal">val['a']</code>, and <code class="literal">val['a']['b']</code> is an
479 object. If any <code class="literal">val['a']</code> or <code class="literal">val['a']['b']</code>
480 is not defined, it will be created as an empty object and filled as
481 necessary. However, if any <code class="literal">val</code> itself or one of the
482 intermediary values is defined as a non-object such as a string, number, or
483 <code class="literal">jsonb</code> <code class="literal">null</code>, traversal cannot proceed so
484 an error is raised and the transaction aborted.
486 An example of subscripting syntax:
488 </p><pre class="programlisting">
490 -- Extract object value by key
491 SELECT ('{"a": 1}'::jsonb)['a'];
493 -- Extract nested object value by key path
494 SELECT ('{"a": {"b": {"c": 1}}}'::jsonb)['a']['b']['c'];
496 -- Extract array element by index
497 SELECT ('[1, "2", null]'::jsonb)[1];
499 -- Update object value by key. Note the quotes around '1': the assigned
500 -- value must be of the jsonb type as well
501 UPDATE table_name SET jsonb_field['key'] = '1';
503 -- This will raise an error if any record's jsonb_field['a']['b'] is something
504 -- other than an object. For example, the value {"a": 1} has a numeric value
506 UPDATE table_name SET jsonb_field['a']['b']['c'] = '1';
508 -- Filter records using a WHERE clause with subscripting. Since the result of
509 -- subscripting is jsonb, the value we compare it against must also be jsonb.
510 -- The double quotes make "value" also a valid jsonb string.
511 SELECT * FROM table_name WHERE jsonb_field['key'] = '"value"';
514 <code class="type">jsonb</code> assignment via subscripting handles a few edge cases
515 differently from <code class="literal">jsonb_set</code>. When a source <code class="type">jsonb</code>
516 value is <code class="literal">NULL</code>, assignment via subscripting will proceed
517 as if it was an empty JSON value of the type (object or array) implied by the
520 </p><pre class="programlisting">
521 -- Where jsonb_field was NULL, it is now {"a": 1}
522 UPDATE table_name SET jsonb_field['a'] = '1';
524 -- Where jsonb_field was NULL, it is now [1]
525 UPDATE table_name SET jsonb_field[0] = '1';
528 If an index is specified for an array containing too few elements,
529 <code class="literal">NULL</code> elements will be appended until the index is reachable
530 and the value can be set.
532 </p><pre class="programlisting">
533 -- Where jsonb_field was [], it is now [null, null, 2];
534 -- where jsonb_field was [0], it is now [0, null, 2]
535 UPDATE table_name SET jsonb_field[2] = '2';
538 A <code class="type">jsonb</code> value will accept assignments to nonexistent subscript
539 paths as long as the last existing element to be traversed is an object or
540 array, as implied by the corresponding subscript (the element indicated by
541 the last subscript in the path is not traversed and may be anything). Nested
542 array and object structures will be created, and in the former case
543 <code class="literal">null</code>-padded, as specified by the subscript path until the
544 assigned value can be placed.
546 </p><pre class="programlisting">
547 -- Where jsonb_field was {}, it is now {"a": [{"b": 1}]}
548 UPDATE table_name SET jsonb_field['a'][0]['b'] = '1';
550 -- Where jsonb_field was [], it is now [null, {"a": 1}]
551 UPDATE table_name SET jsonb_field[1]['a'] = '1';
554 </p></div><div class="sect2" id="DATATYPE-JSON-TRANSFORMS"><div class="titlepage"><div><div><h3 class="title">8.14.6. Transforms <a href="#DATATYPE-JSON-TRANSFORMS" class="id_link">#</a></h3></div></div></div><p>
555 Additional extensions are available that implement transforms for the
556 <code class="type">jsonb</code> type for different procedural languages.
558 The extensions for PL/Perl are called <code class="literal">jsonb_plperl</code> and
559 <code class="literal">jsonb_plperlu</code>. If you use them, <code class="type">jsonb</code>
560 values are mapped to Perl arrays, hashes, and scalars, as appropriate.
562 The extension for PL/Python is called <code class="literal">jsonb_plpython3u</code>.
563 If you use it, <code class="type">jsonb</code> values are mapped to Python
564 dictionaries, lists, and scalars, as appropriate.
566 Of these extensions, <code class="literal">jsonb_plperl</code> is
567 considered <span class="quote">“<span class="quote">trusted</span>”</span>, that is, it can be installed by
568 non-superusers who have <code class="literal">CREATE</code> privilege on the
569 current database. The rest require superuser privilege to install.
570 </p></div><div class="sect2" id="DATATYPE-JSONPATH"><div class="titlepage"><div><div><h3 class="title">8.14.7. jsonpath Type <a href="#DATATYPE-JSONPATH" class="id_link">#</a></h3></div></div></div><a id="id-1.5.7.22.21.2" class="indexterm"></a><p>
571 The <code class="type">jsonpath</code> type implements support for the SQL/JSON path language
572 in <span class="productname">PostgreSQL</span> to efficiently query JSON data.
573 It provides a binary representation of the parsed SQL/JSON path
574 expression that specifies the items to be retrieved by the path
575 engine from the JSON data for further processing with the
576 SQL/JSON query functions.
578 The semantics of SQL/JSON path predicates and operators generally follow SQL.
579 At the same time, to provide a natural way of working with JSON data,
580 SQL/JSON path syntax uses some JavaScript conventions:
581 </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
582 Dot (<code class="literal">.</code>) is used for member access.
583 </p></li><li class="listitem"><p>
584 Square brackets (<code class="literal">[]</code>) are used for array access.
585 </p></li><li class="listitem"><p>
586 SQL/JSON arrays are 0-relative, unlike regular SQL arrays that start from 1.
587 </p></li></ul></div><p>
588 Numeric literals in SQL/JSON path expressions follow JavaScript rules,
589 which are different from both SQL and JSON in some minor details. For
590 example, SQL/JSON path allows <code class="literal">.1</code> and
591 <code class="literal">1.</code>, which are invalid in JSON. Non-decimal integer
592 literals and underscore separators are supported, for example,
593 <code class="literal">1_000_000</code>, <code class="literal">0x1EEE_FFFF</code>,
594 <code class="literal">0o273</code>, <code class="literal">0b100101</code>. In SQL/JSON path
595 (and in JavaScript, but not in SQL proper), there must not be an underscore
596 separator directly after the radix prefix.
598 An SQL/JSON path expression is typically written in an SQL query as an
599 SQL character string literal, so it must be enclosed in single quotes,
600 and any single quotes desired within the value must be doubled
601 (see <a class="xref" href="sql-syntax-lexical.html#SQL-SYNTAX-STRINGS" title="4.1.2.1. String Constants">Section 4.1.2.1</a>).
602 Some forms of path expressions require string literals within them.
603 These embedded string literals follow JavaScript/ECMAScript conventions:
604 they must be surrounded by double quotes, and backslash escapes may be
605 used within them to represent otherwise-hard-to-type characters.
606 In particular, the way to write a double quote within an embedded string
607 literal is <code class="literal">\"</code>, and to write a backslash itself, you
608 must write <code class="literal">\\</code>. Other special backslash sequences
609 include those recognized in JavaScript strings:
610 <code class="literal">\b</code>,
611 <code class="literal">\f</code>,
612 <code class="literal">\n</code>,
613 <code class="literal">\r</code>,
614 <code class="literal">\t</code>,
615 <code class="literal">\v</code>
616 for various ASCII control characters,
617 <code class="literal">\x<em class="replaceable"><code>NN</code></em></code> for a character code
618 written with only two hex digits,
619 <code class="literal">\u<em class="replaceable"><code>NNNN</code></em></code> for a Unicode
620 character identified by its 4-hex-digit code point, and
621 <code class="literal">\u{<em class="replaceable"><code>N...</code></em>}</code> for a Unicode
622 character code point written with 1 to 6 hex digits.
624 A path expression consists of a sequence of path elements,
625 which can be any of the following:
626 </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
627 Path literals of JSON primitive types:
628 Unicode text, numeric, true, false, or null.
629 </p></li><li class="listitem"><p>
630 Path variables listed in <a class="xref" href="datatype-json.html#TYPE-JSONPATH-VARIABLES" title="Table 8.24. jsonpath Variables">Table 8.24</a>.
631 </p></li><li class="listitem"><p>
632 Accessor operators listed in <a class="xref" href="datatype-json.html#TYPE-JSONPATH-ACCESSORS" title="Table 8.25. jsonpath Accessors">Table 8.25</a>.
633 </p></li><li class="listitem"><p>
634 <code class="type">jsonpath</code> operators and methods listed
635 in <a class="xref" href="functions-json.html#FUNCTIONS-SQLJSON-PATH-OPERATORS" title="9.16.2.3. SQL/JSON Path Operators and Methods">Section 9.16.2.3</a>.
636 </p></li><li class="listitem"><p>
637 Parentheses, which can be used to provide filter expressions
638 or define the order of path evaluation.
639 </p></li></ul></div><p>
641 For details on using <code class="type">jsonpath</code> expressions with SQL/JSON
642 query functions, see <a class="xref" href="functions-json.html#FUNCTIONS-SQLJSON-PATH" title="9.16.2. The SQL/JSON Path Language">Section 9.16.2</a>.
643 </p><div class="table" id="TYPE-JSONPATH-VARIABLES"><p class="title"><strong>Table 8.24. <code class="type">jsonpath</code> Variables</strong></p><div class="table-contents"><table class="table" summary="jsonpath Variables" border="1"><colgroup><col class="col1" /><col class="col2" /></colgroup><thead><tr><th>Variable</th><th>Description</th></tr></thead><tbody><tr><td><code class="literal">$</code></td><td>A variable representing the JSON value being queried
644 (the <em class="firstterm">context item</em>).
645 </td></tr><tr><td><code class="literal">$varname</code></td><td>
646 A named variable. Its value can be set by the parameter
647 <em class="parameter"><code>vars</code></em> of several JSON processing functions;
648 see <a class="xref" href="functions-json.html#FUNCTIONS-JSON-PROCESSING-TABLE" title="Table 9.51. JSON Processing Functions">Table 9.51</a> for details.
650 </td></tr><tr><td><code class="literal">@</code></td><td>A variable representing the result of path evaluation
651 in filter expressions.
652 </td></tr></tbody></table></div></div><br class="table-break" /><div class="table" id="TYPE-JSONPATH-ACCESSORS"><p class="title"><strong>Table 8.25. <code class="type">jsonpath</code> Accessors</strong></p><div class="table-contents"><table class="table" summary="jsonpath Accessors" border="1"><colgroup><col class="col1" /><col class="col2" /></colgroup><thead><tr><th>Accessor Operator</th><th>Description</th></tr></thead><tbody><tr><td>
654 <code class="literal">.<em class="replaceable"><code>key</code></em></code>
657 <code class="literal">."$<em class="replaceable"><code>varname</code></em>"</code>
661 Member accessor that returns an object member with
662 the specified key. If the key name matches some named variable
663 starting with <code class="literal">$</code> or does not meet the
664 JavaScript rules for an identifier, it must be enclosed in
665 double quotes to make it a string literal.
669 <code class="literal">.*</code>
673 Wildcard member accessor that returns the values of all
674 members located at the top level of the current object.
678 <code class="literal">.**</code>
682 Recursive wildcard member accessor that processes all levels
683 of the JSON hierarchy of the current object and returns all
684 the member values, regardless of their nesting level. This
685 is a <span class="productname">PostgreSQL</span> extension of
686 the SQL/JSON standard.
690 <code class="literal">.**{<em class="replaceable"><code>level</code></em>}</code>
693 <code class="literal">.**{<em class="replaceable"><code>start_level</code></em> to
694 <em class="replaceable"><code>end_level</code></em>}</code>
698 Like <code class="literal">.**</code>, but selects only the specified
699 levels of the JSON hierarchy. Nesting levels are specified as integers.
700 Level zero corresponds to the current object. To access the lowest
701 nesting level, you can use the <code class="literal">last</code> keyword.
702 This is a <span class="productname">PostgreSQL</span> extension of
703 the SQL/JSON standard.
707 <code class="literal">[<em class="replaceable"><code>subscript</code></em>, ...]</code>
711 Array element accessor.
712 <code class="literal"><em class="replaceable"><code>subscript</code></em></code> can be
713 given in two forms: <code class="literal"><em class="replaceable"><code>index</code></em></code>
714 or <code class="literal"><em class="replaceable"><code>start_index</code></em> to <em class="replaceable"><code>end_index</code></em></code>.
715 The first form returns a single array element by its index. The second
716 form returns an array slice by the range of indexes, including the
717 elements that correspond to the provided
718 <em class="replaceable"><code>start_index</code></em> and <em class="replaceable"><code>end_index</code></em>.
721 The specified <em class="replaceable"><code>index</code></em> can be an integer, as
722 well as an expression returning a single numeric value, which is
723 automatically cast to integer. Index zero corresponds to the first
724 array element. You can also use the <code class="literal">last</code> keyword
725 to denote the last array element, which is useful for handling arrays
730 <code class="literal">[*]</code>
734 Wildcard array element accessor that returns all array elements.
736 </td></tr></tbody></table></div></div><br class="table-break" /></div><div class="footnotes"><br /><hr style="width:100; text-align:left;margin-left: 0" /><div id="ftn.id-1.5.7.22.18.9.3" class="footnote"><p><a href="#id-1.5.7.22.18.9.3" class="para"><sup class="para">[7] </sup></a>
737 For this purpose, the term <span class="quote">“<span class="quote">value</span>”</span> includes array elements,
738 though JSON terminology sometimes considers array elements distinct
739 from values within objects.
740 </p></div></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="datatype-xml.html" title="8.13. XML Type">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="datatype.html" title="Chapter 8. Data Types">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="arrays.html" title="8.15. Arrays">Next</a></td></tr><tr><td width="40%" align="left" valign="top">8.13. <acronym class="acronym">XML</acronym> Type </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 8.15. Arrays</td></tr></table></div></body></html>
begriffs.com / projects / ai-pg / blob