1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
|
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!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 15.6 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</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#id-1.5.7.22.20">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>
JSON data types are for storing JSON (JavaScript Object Notation)
data, as specified in <a class="ulink" href="https://tools.ietf.org/html/rfc7159" target="_top">RFC
7159</a>. Such data can also be stored as <code class="type">text</code>, 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 class="xref" href="functions-json.html" title="9.16. JSON Functions and Operators">Section 9.16</a>.
</p><p>
<span class="productname">PostgreSQL</span> offers two types for storing JSON
data: <code class="type">json</code> and <code class="type">jsonb</code>. To implement efficient query
mechanisms for these data types, <span class="productname">PostgreSQL</span>
also provides the <code class="type">jsonpath</code> data type described in
<a class="xref" href="datatype-json.html#DATATYPE-JSONPATH" title="8.14.7. jsonpath Type">Section 8.14.7</a>.
</p><p>
The <code class="type">json</code> and <code class="type">jsonb</code> data types
accept <span class="emphasis"><em>almost</em></span> identical sets of values as
input. The major practical difference is one of efficiency. The
<code class="type">json</code> data type stores an exact copy of the input text,
which processing functions must reparse on each execution; while
<code class="type">jsonb</code> 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. <code class="type">jsonb</code> also supports indexing, which can be a
significant advantage.
</p><p>
Because the <code class="type">json</code> 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, <code class="type">jsonb</code> 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
<code class="type">jsonb</code>, unless there are quite specialized needs, such as
legacy assumptions about ordering of object keys.
</p><p>
<acronym class="acronym">RFC</acronym> 7159 specifies that JSON strings should be encoded in UTF8.
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>
<acronym class="acronym">RFC</acronym> 7159 permits JSON strings to contain Unicode escape sequences
denoted by <code class="literal">\u<em class="replaceable"><code>XXXX</code></em></code>. In the input
function for the <code class="type">json</code> type, Unicode escapes are allowed
regardless of the database encoding, and are checked only for syntactic
correctness (that is, that four hex digits follow <code class="literal">\u</code>).
However, the input function for <code class="type">jsonb</code> is stricter: it disallows
Unicode escapes for characters that cannot be represented in the database
encoding. The <code class="type">jsonb</code> type also
rejects <code class="literal">\u0000</code> (because that cannot be represented in
<span class="productname">PostgreSQL</span>'s <code class="type">text</code> 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 single character for storage;
this includes folding surrogate pairs into a single character.
</p><div class="note"><h3 class="title">Note</h3><p>
Many of the JSON processing functions described
in <a class="xref" href="functions-json.html" title="9.16. JSON Functions and Operators">Section 9.16</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 <code class="type">json</code>
not <code class="type">jsonb</code>. The fact that the <code class="type">json</code> 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.
</p></div><p>
When converting textual JSON input into <code class="type">jsonb</code>, 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 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>.
Therefore, there are some minor additional constraints on what
constitutes valid <code class="type">jsonb</code> data that do not apply to
the <code class="type">json</code> type, nor to JSON in the abstract, corresponding
to limits on what can be represented by the underlying data type.
Notably, <code class="type">jsonb</code> will reject numbers that are outside the
range of the <span class="productname">PostgreSQL</span> <code class="type">numeric</code> data
type, while <code class="type">json</code> 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 <code class="type">number</code>
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" 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
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</h3></div></div></div><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 <code class="type">json</code> (or <code class="type">jsonb</code>) 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, <code class="type">json</code> outputs the same text that was
input, while <code class="type">jsonb</code> 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 <code class="type">jsonb</code>, numbers will be printed according to the behavior of the
underlying <code class="type">numeric</code> type. In practice this means that numbers
entered with <code class="literal">E</code> 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, <code class="type">jsonb</code> will preserve trailing fractional zeroes, as seen
in this example, even though those are semantically insignificant for
purposes such as equality checks.
</p><p>
For the list of built-in functions and operators available for
constructing and processing JSON values, see <a class="xref" href="functions-json.html" title="9.16. JSON Functions and Operators">Section 9.16</a>.
</p></div><div class="sect2" id="JSON-DOC-DESIGN"><div class="titlepage"><div><div><h3 class="title">8.14.2. Designing JSON Documents</h3></div></div></div><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">“<span class="quote">documents</span>”</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" id="JSON-CONTAINMENT"><div class="titlepage"><div><div><h3 class="title">8.14.3. <code class="type">jsonb</code> Containment and Existence</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>
Testing <em class="firstterm">containment</em> is an important capability of
<code class="type">jsonb</code>. There is no parallel set of facilities for the
<code class="type">json</code> type. Containment tests whether
one <code class="type">jsonb</code> 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 <span class="emphasis"><strong>not</strong></span> 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;
</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>
<code class="type">jsonb</code> also has an <em class="firstterm">existence</em> operator, which is
a variation on the theme of containment: it tests whether a string
(given as a <code class="type">text</code> value) appears as an object key or array
element at the top level of the <code class="type">jsonb</code> 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"><h3 class="title">Tip</h3><p>
Because JSON containment is nested, an appropriate query can skip
explicit selection of sub-objects. As an example, suppose that we have
a <code class="structfield">doc</code> column containing objects at the top level, with
most objects containing <code class="literal">tags</code> fields that contain arrays of
sub-objects. This query finds entries in which sub-objects containing
both <code class="literal">"term":"paris"</code> and <code class="literal">"term":"food"</code> appear,
while ignoring any such keys outside the <code class="literal">tags</code> 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></div><p>
The various containment and existence operators, along with all other
JSON operators and functions are documented
in <a class="xref" href="functions-json.html" title="9.16. JSON Functions and Operators">Section 9.16</a>.
</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</h3></div></div></div><a id="id-1.5.7.22.18.2" class="indexterm"></a><p>
GIN indexes can be used to efficiently search for
keys or key/value pairs occurring within a large number of
<code class="type">jsonb</code> documents (datums).
Two GIN <span class="quote">“<span class="quote">operator classes</span>”</span> are provided, offering different
performance and flexibility trade-offs.
</p><p>
The default GIN operator class for <code class="type">jsonb</code> supports queries with
the key-exists operators <code class="literal">?</code>, <code class="literal">?|</code>
and <code class="literal">?&</code>, the containment operator
<code class="literal">@></code>, and the <code class="type">jsonpath</code> match
operators <code class="literal">@?</code> and <code class="literal">@@</code>.
(For details of the semantics that these operators
implement, see <a class="xref" href="functions-json.html#FUNCTIONS-JSONB-OP-TABLE" title="Table 9.46. Additional jsonb Operators">Table 9.46</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 <code class="literal">jsonb_path_ops</code>
does not support the key-exists operators, but it does support
<code class="literal">@></code>, <code class="literal">@?</code> and <code class="literal">@@</code>.
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 <code class="structname">api</code>,
in a <code class="type">jsonb</code> column named <code class="structfield">jdoc</code>.
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 <code class="literal">?</code> is indexable,
it is not applied directly to the indexed column <code class="structfield">jdoc</code>:
</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 <code class="literal">"tags"</code> 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 <code class="literal">WHERE</code> clause <code class="literal">jdoc -> 'tags' ? 'qui'</code>
will be recognized as an application of the indexable
operator <code class="literal">?</code> to the indexed
expression <code class="literal">jdoc -> 'tags'</code>.
(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>.)
</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 <code class="structfield">jdoc</code> column can support this
query. But note that such an index will store copies of every key and
value in the <code class="structfield">jdoc</code> column, whereas the expression index
of the previous example stores only data found under
the <code class="literal">tags</code> 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>
GIN indexes also support the <code class="literal">@?</code>
and <code class="literal">@@</code> operators, which
perform <code class="type">jsonpath</code> matching. Examples are
</p><pre class="programlisting">
SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @? '$.tags[*] ? (@ == "qui")';
</pre><p>
</p><pre class="programlisting">
SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @@ '$.tags[*] == "qui"';
</pre><p>
For these operators, a GIN index extracts clauses of the form
<code class="literal"><em class="replaceable"><code>accessors_chain</code></em>
= <em class="replaceable"><code>constant</code></em></code> out of
the <code class="type">jsonpath</code> pattern, and does the index search based on
the keys and values mentioned in these clauses. The accessors chain
may include <code class="literal">.<em class="replaceable"><code>key</code></em></code>,
<code class="literal">[*]</code>,
and <code class="literal">[<em class="replaceable"><code>index</code></em>]</code> accessors.
The <code class="literal">jsonb_ops</code> operator class also
supports <code class="literal">.*</code> and <code class="literal">.**</code> accessors,
but the <code class="literal">jsonb_path_ops</code> operator class does not.
</p><p>
Although the <code class="literal">jsonb_path_ops</code> operator class supports
only queries with the <code class="literal">@></code>, <code class="literal">@?</code>
and <code class="literal">@@</code> operators, it has notable
performance advantages over the default operator
class <code class="literal">jsonb_ops</code>. A <code class="literal">jsonb_path_ops</code>
index is usually much smaller than a <code class="literal">jsonb_ops</code>
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 <code class="literal">jsonb_ops</code>
and a <code class="literal">jsonb_path_ops</code> 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 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>
Basically, each <code class="literal">jsonb_path_ops</code> index item is
a hash of the value and the key(s) leading to it; for example to index
<code class="literal">{"foo": {"bar": "baz"}}</code>, a single index item would
be created incorporating all three of <code class="literal">foo</code>, <code class="literal">bar</code>,
and <code class="literal">baz</code> 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 <code class="literal">foo</code>
appears as a key. On the other hand, a <code class="literal">jsonb_ops</code>
index would create three index items representing <code class="literal">foo</code>,
<code class="literal">bar</code>, and <code class="literal">baz</code> 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 <code class="literal">jsonb_path_ops</code> 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 <code class="literal">jsonb_path_ops</code> approach is
that it produces no index entries for JSON structures not containing
any values, such as <code class="literal">{"a": {}}</code>. If a search for
documents containing such a structure is requested, it will require a
full-index scan, which is quite slow. <code class="literal">jsonb_path_ops</code> is
therefore ill-suited for applications that often perform such searches.
</p><p>
<code class="type">jsonb</code> also supports <code class="literal">btree</code> and <code class="literal">hash</code>
indexes. These are usually useful only if it's important to check
equality of complete JSON documents.
The <code class="literal">btree</code> ordering for <code class="type">jsonb</code> datums is seldom
of great interest, but for completeness it is:
</p><pre class="synopsis">
<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>
<em class="replaceable"><code>Object with n pairs</code></em> > <em class="replaceable"><code>object with n - 1 pairs</code></em>
<em class="replaceable"><code>Array with n elements</code></em> > <em class="replaceable"><code>array with n - 1 elements</code></em>
</pre><p>
Objects with equal numbers of pairs are compared in the order:
</p><pre class="synopsis">
<em class="replaceable"><code>key-1</code></em>, <em class="replaceable"><code>value-1</code></em>, <em class="replaceable"><code>key-2</code></em> ...
</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">
<em class="replaceable"><code>element-1</code></em>, <em class="replaceable"><code>element-2</code></em> ...
</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 class="sect2" id="JSONB-SUBSCRIPTING"><div class="titlepage"><div><div><h3 class="title">8.14.5. <code class="type">jsonb</code> Subscripting</h3></div></div></div><p>
The <code class="type">jsonb</code> 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 the <code class="literal">path</code>
argument in the <code class="literal">jsonb_set</code> function. If a <code class="type">jsonb</code>
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.
</p><p>
<code class="command">UPDATE</code> statements may use subscripting in the
<code class="literal">SET</code> clause to modify <code class="type">jsonb</code> values. Subscript
paths must be traversable for all affected values insofar as they exist. For
instance, the path <code class="literal">val['a']['b']['c']</code> can be traversed all
the way to <code class="literal">c</code> if every <code class="literal">val</code>,
<code class="literal">val['a']</code>, and <code class="literal">val['a']['b']</code> is an
object. If any <code class="literal">val['a']</code> or <code class="literal">val['a']['b']</code>
is not defined, it will be created as an empty object and filled as
necessary. However, if any <code class="literal">val</code> itself or one of the
intermediary values is defined as a non-object such as a string, number, or
<code class="literal">jsonb</code> <code class="literal">null</code>, traversal cannot proceed so
an error is raised and the transaction aborted.
</p><p>
An example of subscripting syntax:
</p><pre class="programlisting">
-- Extract object value by key
SELECT ('{"a": 1}'::jsonb)['a'];
-- Extract nested object value by key path
SELECT ('{"a": {"b": {"c": 1}}}'::jsonb)['a']['b']['c'];
-- Extract array element by index
SELECT ('[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 well
UPDATE 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"';
</pre><p>
<code class="type">jsonb</code> assignment via subscripting handles a few edge cases
differently from <code class="literal">jsonb_set</code>. When a source <code class="type">jsonb</code>
value is <code class="literal">NULL</code>, assignment via subscripting will proceed
as if it was an empty JSON value of the type (object or array) implied by the
subscript key:
</p><pre class="programlisting">
-- 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';
</pre><p>
If an index is specified for an array containing too few elements,
<code class="literal">NULL</code> elements will be appended until the index is reachable
and the value can be set.
</p><pre class="programlisting">
-- 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';
</pre><p>
A <code class="type">jsonb</code> 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 case
<code class="literal">null</code>-padded, as specified by the subscript path until the
assigned value can be placed.
</p><pre class="programlisting">
-- 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';
</pre><p>
</p></div><div class="sect2" id="id-1.5.7.22.20"><div class="titlepage"><div><div><h3 class="title">8.14.6. Transforms</h3></div></div></div><p>
Additional extensions are available that implement transforms for the
<code class="type">jsonb</code> type for different procedural languages.
</p><p>
The extensions for PL/Perl are called <code class="literal">jsonb_plperl</code> and
<code class="literal">jsonb_plperlu</code>. If you use them, <code class="type">jsonb</code>
values are mapped to Perl arrays, hashes, and scalars, as appropriate.
</p><p>
The extension for PL/Python is called <code class="literal">jsonb_plpython3u</code>.
If you use it, <code class="type">jsonb</code> values are mapped to Python
dictionaries, lists, and scalars, as appropriate.
</p><p>
Of these extensions, <code class="literal">jsonb_plperl</code> is
considered <span class="quote">“<span class="quote">trusted</span>”</span>, that is, it can be installed by
non-superusers who have <code class="literal">CREATE</code> privilege on the
current database. The rest require superuser privilege to install.
</p></div><div class="sect2" id="DATATYPE-JSONPATH"><div class="titlepage"><div><div><h3 class="title">8.14.7. jsonpath Type</h3></div></div></div><a id="id-1.5.7.22.21.2" class="indexterm"></a><p>
The <code class="type">jsonpath</code> type implements support for the SQL/JSON path language
in <span class="productname">PostgreSQL</span> 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.
</p><p>
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:
</p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
Dot (<code class="literal">.</code>) is used for member access.
</p></li><li class="listitem"><p>
Square brackets (<code class="literal">[]</code>) are used for array access.
</p></li><li class="listitem"><p>
SQL/JSON arrays are 0-relative, unlike regular SQL arrays that start from 1.
</p></li></ul></div><p>
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
(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>).
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 <code class="literal">\"</code>, and to write a backslash itself, you
must write <code class="literal">\\</code>. Other special backslash sequences
include those recognized in JSON strings:
<code class="literal">\b</code>,
<code class="literal">\f</code>,
<code class="literal">\n</code>,
<code class="literal">\r</code>,
<code class="literal">\t</code>,
<code class="literal">\v</code>
for various ASCII control characters, and
<code class="literal">\u<em class="replaceable"><code>NNNN</code></em></code> for a Unicode
character identified by its 4-hex-digit code point. The backslash
syntax also includes two cases not allowed by JSON:
<code class="literal">\x<em class="replaceable"><code>NN</code></em></code> for a character code
written with only two hex digits, and
<code class="literal">\u{<em class="replaceable"><code>N...</code></em>}</code> for a character
code written with 1 to 6 hex digits.
</p><p>
A path expression consists of a sequence of path elements,
which can be any of the following:
</p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
Path literals of JSON primitive types:
Unicode text, numeric, true, false, or null.
</p></li><li class="listitem"><p>
Path variables listed in <a class="xref" href="datatype-json.html#TYPE-JSONPATH-VARIABLES" title="Table 8.24. jsonpath Variables">Table 8.24</a>.
</p></li><li class="listitem"><p>
Accessor operators listed in <a class="xref" href="datatype-json.html#TYPE-JSONPATH-ACCESSORS" title="Table 8.25. jsonpath Accessors">Table 8.25</a>.
</p></li><li class="listitem"><p>
<code class="type">jsonpath</code> operators and methods listed
in <a class="xref" href="functions-json.html#FUNCTIONS-SQLJSON-PATH-OPERATORS" title="9.16.2.2. SQL/JSON Path Operators and Methods">Section 9.16.2.2</a>.
</p></li><li class="listitem"><p>
Parentheses, which can be used to provide filter expressions
or define the order of path evaluation.
</p></li></ul></div><p>
</p><p>
For details on using <code class="type">jsonpath</code> expressions with SQL/JSON
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>.
</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
(the <em class="firstterm">context item</em>).
</td></tr><tr><td><code class="literal">$varname</code></td><td>
A named variable. Its value can be set by the parameter
<em class="parameter"><code>vars</code></em> of several JSON processing functions;
see <a class="xref" href="functions-json.html#FUNCTIONS-JSON-PROCESSING-TABLE" title="Table 9.48. JSON Processing Functions">Table 9.48</a> for details.
</td></tr><tr><td><code class="literal">@</code></td><td>A variable representing the result of path evaluation
in filter expressions.
</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>
<p>
<code class="literal">.<em class="replaceable"><code>key</code></em></code>
</p>
<p>
<code class="literal">."$<em class="replaceable"><code>varname</code></em>"</code>
</p>
</td><td>
<p>
Member accessor that returns an object member with
the specified key. If the key name matches some named variable
starting with <code class="literal">$</code> or does not meet the
JavaScript rules for an identifier, it must be enclosed in
double quotes to make it a string literal.
</p>
</td></tr><tr><td>
<p>
<code class="literal">.*</code>
</p>
</td><td>
<p>
Wildcard member accessor that returns the values of all
members located at the top level of the current object.
</p>
</td></tr><tr><td>
<p>
<code class="literal">.**</code>
</p>
</td><td>
<p>
Recursive wildcard member accessor that processes all levels
of the JSON hierarchy of the current object and returns all
the member values, regardless of their nesting level. This
is a <span class="productname">PostgreSQL</span> extension of
the SQL/JSON standard.
</p>
</td></tr><tr><td>
<p>
<code class="literal">.**{<em class="replaceable"><code>level</code></em>}</code>
</p>
<p>
<code class="literal">.**{<em class="replaceable"><code>start_level</code></em> to
<em class="replaceable"><code>end_level</code></em>}</code>
</p>
</td><td>
<p>
Like <code class="literal">.**</code>, but selects only the specified
levels of the JSON hierarchy. Nesting levels are specified as integers.
Level zero corresponds to the current object. To access the lowest
nesting level, you can use the <code class="literal">last</code> keyword.
This is a <span class="productname">PostgreSQL</span> extension of
the SQL/JSON standard.
</p>
</td></tr><tr><td>
<p>
<code class="literal">[<em class="replaceable"><code>subscript</code></em>, ...]</code>
</p>
</td><td>
<p>
Array element accessor.
<code class="literal"><em class="replaceable"><code>subscript</code></em></code> can be
given in two forms: <code class="literal"><em class="replaceable"><code>index</code></em></code>
or <code class="literal"><em class="replaceable"><code>start_index</code></em> to <em class="replaceable"><code>end_index</code></em></code>.
The first form returns a single array element by its index. The second
form returns an array slice by the range of indexes, including the
elements that correspond to the provided
<em class="replaceable"><code>start_index</code></em> and <em class="replaceable"><code>end_index</code></em>.
</p>
<p>
The specified <em class="replaceable"><code>index</code></em> can be an integer, as
well as an expression returning a single numeric value, which is
automatically cast to integer. Index zero corresponds to the first
array element. You can also use the <code class="literal">last</code> keyword
to denote the last array element, which is useful for handling arrays
of unknown length.
</p>
</td></tr><tr><td>
<p>
<code class="literal">[*]</code>
</p>
</td><td>
<p>
Wildcard array element accessor that returns all array elements.
</p>
</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>
For this purpose, the term <span class="quote">“<span class="quote">value</span>”</span> includes array elements,
though JSON terminology sometimes considers array elements distinct
from values within objects.
</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 15.6 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 8.15. Arrays</td></tr></table></div></body></html>
|
