| 1 |
<!-- doc/src/sgml/json.sgml --> |
| 2 |
|
| 3 |
<sect1 id="datatype-json"> |
| 4 |
<title><acronym>JSON</acronym> Types</title> |
| 5 |
|
| 6 |
<indexterm zone="datatype-json"> |
| 7 |
<primary>JSON</primary> |
| 8 |
</indexterm> |
| 9 |
|
| 10 |
<indexterm zone="datatype-json"> |
| 11 |
<primary>JSONB</primary> |
| 12 |
</indexterm> |
| 13 |
|
| 14 |
<para> |
| 15 |
JSON data types are for storing JSON (JavaScript Object Notation) |
| 16 |
data, as specified in <ulink url="https://datatracker.ietf.org/doc/html/rfc7159">RFC |
| 17 |
7159</ulink>. Such data can also be stored as <type>text</type>, but |
| 18 |
the JSON data types have the advantage of enforcing that each |
| 19 |
stored value is valid according to the JSON rules. There are also |
| 20 |
assorted JSON-specific functions and operators available for data stored |
| 21 |
in these data types; see <xref linkend="functions-json"/>. |
| 22 |
</para> |
| 23 |
|
| 24 |
<para> |
| 25 |
<productname>PostgreSQL</productname> offers two types for storing JSON |
| 26 |
data: <type>json</type> and <type>jsonb</type>. To implement efficient query |
| 27 |
mechanisms for these data types, <productname>PostgreSQL</productname> |
| 28 |
also provides the <type>jsonpath</type> data type described in |
| 29 |
<xref linkend="datatype-jsonpath"/>. |
| 30 |
</para> |
| 31 |
|
| 32 |
<para> |
| 33 |
The <type>json</type> and <type>jsonb</type> data types |
| 34 |
accept <emphasis>almost</emphasis> identical sets of values as |
| 35 |
input. The major practical difference is one of efficiency. The |
| 36 |
<type>json</type> data type stores an exact copy of the input text, |
| 37 |
which processing functions must reparse on each execution; while |
| 38 |
<type>jsonb</type> data is stored in a decomposed binary format that |
| 39 |
makes it slightly slower to input due to added conversion |
| 40 |
overhead, but significantly faster to process, since no reparsing |
| 41 |
is needed. <type>jsonb</type> also supports indexing, which can be a |
| 42 |
significant advantage. |
| 43 |
</para> |
| 44 |
|
| 45 |
<para> |
| 46 |
Because the <type>json</type> type stores an exact copy of the input text, it |
| 47 |
will preserve semantically-insignificant white space between tokens, as |
| 48 |
well as the order of keys within JSON objects. Also, if a JSON object |
| 49 |
within the value contains the same key more than once, all the key/value |
| 50 |
pairs are kept. (The processing functions consider the last value as the |
| 51 |
operative one.) By contrast, <type>jsonb</type> does not preserve white |
| 52 |
space, does not preserve the order of object keys, and does not keep |
| 53 |
duplicate object keys. If duplicate keys are specified in the input, |
| 54 |
only the last value is kept. |
| 55 |
</para> |
| 56 |
|
| 57 |
<para> |
| 58 |
In general, most applications should prefer to store JSON data as |
| 59 |
<type>jsonb</type>, unless there are quite specialized needs, such as |
| 60 |
legacy assumptions about ordering of object keys. |
| 61 |
</para> |
| 62 |
|
| 63 |
<para> |
| 64 |
<acronym>RFC</acronym> 7159 specifies that JSON strings should be encoded in UTF8. |
| 65 |
It is therefore not possible for the JSON |
| 66 |
types to conform rigidly to the JSON specification unless the database |
| 67 |
encoding is UTF8. Attempts to directly include characters that |
| 68 |
cannot be represented in the database encoding will fail; conversely, |
| 69 |
characters that can be represented in the database encoding but not |
| 70 |
in UTF8 will be allowed. |
| 71 |
</para> |
| 72 |
|
| 73 |
<para> |
| 74 |
<acronym>RFC</acronym> 7159 permits JSON strings to contain Unicode escape sequences |
| 75 |
denoted by <literal>\u<replaceable>XXXX</replaceable></literal>. In the input |
| 76 |
function for the <type>json</type> type, Unicode escapes are allowed |
| 77 |
regardless of the database encoding, and are checked only for syntactic |
| 78 |
correctness (that is, that four hex digits follow <literal>\u</literal>). |
| 79 |
However, the input function for <type>jsonb</type> is stricter: it disallows |
| 80 |
Unicode escapes for characters that cannot be represented in the database |
| 81 |
encoding. The <type>jsonb</type> type also |
| 82 |
rejects <literal>\u0000</literal> (because that cannot be represented in |
| 83 |
<productname>PostgreSQL</productname>'s <type>text</type> type), and it insists |
| 84 |
that any use of Unicode surrogate pairs to designate characters outside |
| 85 |
the Unicode Basic Multilingual Plane be correct. Valid Unicode escapes |
| 86 |
are converted to the equivalent single character for storage; |
| 87 |
this includes folding surrogate pairs into a single character. |
| 88 |
</para> |
| 89 |
|
| 90 |
<note> |
| 91 |
<para> |
| 92 |
Many of the JSON processing functions described |
| 93 |
in <xref linkend="functions-json"/> will convert Unicode escapes to |
| 94 |
regular characters, and will therefore throw the same types of errors |
| 95 |
just described even if their input is of type <type>json</type> |
| 96 |
not <type>jsonb</type>. The fact that the <type>json</type> input function does |
| 97 |
not make these checks may be considered a historical artifact, although |
| 98 |
it does allow for simple storage (without processing) of JSON Unicode |
| 99 |
escapes in a database encoding that does not support the represented |
| 100 |
characters. |
| 101 |
</para> |
| 102 |
</note> |
| 103 |
|
| 104 |
<para> |
| 105 |
When converting textual JSON input into <type>jsonb</type>, the primitive |
| 106 |
types described by <acronym>RFC</acronym> 7159 are effectively mapped onto |
| 107 |
native <productname>PostgreSQL</productname> types, as shown |
| 108 |
in <xref linkend="json-type-mapping-table"/>. |
| 109 |
Therefore, there are some minor additional constraints on what |
| 110 |
constitutes valid <type>jsonb</type> data that do not apply to |
| 111 |
the <type>json</type> type, nor to JSON in the abstract, corresponding |
| 112 |
to limits on what can be represented by the underlying data type. |
| 113 |
Notably, <type>jsonb</type> will reject numbers that are outside the |
| 114 |
range of the <productname>PostgreSQL</productname> <type>numeric</type> data |
| 115 |
type, while <type>json</type> will not. Such implementation-defined |
| 116 |
restrictions are permitted by <acronym>RFC</acronym> 7159. However, in |
| 117 |
practice such problems are far more likely to occur in other |
| 118 |
implementations, as it is common to represent JSON's <type>number</type> |
| 119 |
primitive type as IEEE 754 double precision floating point |
| 120 |
(which <acronym>RFC</acronym> 7159 explicitly anticipates and allows for). |
| 121 |
When using JSON as an interchange format with such systems, the danger |
| 122 |
of losing numeric precision compared to data originally stored |
| 123 |
by <productname>PostgreSQL</productname> should be considered. |
| 124 |
</para> |
| 125 |
|
| 126 |
<para> |
| 127 |
Conversely, as noted in the table there are some minor restrictions on |
| 128 |
the input format of JSON primitive types that do not apply to |
| 129 |
the corresponding <productname>PostgreSQL</productname> types. |
| 130 |
</para> |
| 131 |
|
| 132 |
<table id="json-type-mapping-table"> |
| 133 |
<title>JSON Primitive Types and Corresponding <productname>PostgreSQL</productname> Types</title> |
| 134 |
<tgroup cols="3"> |
| 135 |
<colspec colname="col1" colwidth="1*"/> |
| 136 |
<colspec colname="col2" colwidth="1*"/> |
| 137 |
<colspec colname="col3" colwidth="2*"/> |
| 138 |
<thead> |
| 139 |
<row> |
| 140 |
<entry>JSON primitive type</entry> |
| 141 |
<entry><productname>PostgreSQL</productname> type</entry> |
| 142 |
<entry>Notes</entry> |
| 143 |
</row> |
| 144 |
</thead> |
| 145 |
<tbody> |
| 146 |
<row> |
| 147 |
<entry><type>string</type></entry> |
| 148 |
<entry><type>text</type></entry> |
| 149 |
<entry><literal>\u0000</literal> is disallowed, as are Unicode escapes |
| 150 |
representing characters not available in the database encoding</entry> |
| 151 |
</row> |
| 152 |
<row> |
| 153 |
<entry><type>number</type></entry> |
| 154 |
<entry><type>numeric</type></entry> |
| 155 |
<entry><literal>NaN</literal> and <literal>infinity</literal> values are disallowed</entry> |
| 156 |
</row> |
| 157 |
<row> |
| 158 |
<entry><type>boolean</type></entry> |
| 159 |
<entry><type>boolean</type></entry> |
| 160 |
<entry>Only lowercase <literal>true</literal> and <literal>false</literal> spellings are accepted</entry> |
| 161 |
</row> |
| 162 |
<row> |
| 163 |
<entry><type>null</type></entry> |
| 164 |
<entry>(none)</entry> |
| 165 |
<entry>SQL <literal>NULL</literal> is a different concept</entry> |
| 166 |
</row> |
| 167 |
</tbody> |
| 168 |
</tgroup> |
| 169 |
</table> |
| 170 |
|
| 171 |
<sect2 id="json-keys-elements"> |
| 172 |
<title>JSON Input and Output Syntax</title> |
| 173 |
<para> |
| 174 |
The input/output syntax for the JSON data types is as specified in |
| 175 |
<acronym>RFC</acronym> 7159. |
| 176 |
</para> |
| 177 |
<para> |
| 178 |
The following are all valid <type>json</type> (or <type>jsonb</type>) expressions: |
| 179 |
<programlisting> |
| 180 |
-- Simple scalar/primitive value |
| 181 |
-- Primitive values can be numbers, quoted strings, true, false, or null |
| 182 |
SELECT '5'::json; |
| 183 |
|
| 184 |
-- Array of zero or more elements (elements need not be of same type) |
| 185 |
SELECT '[1, 2, "foo", null]'::json; |
| 186 |
|
| 187 |
-- Object containing pairs of keys and values |
| 188 |
-- Note that object keys must always be quoted strings |
| 189 |
SELECT '{"bar": "baz", "balance": 7.77, "active": false}'::json; |
| 190 |
|
| 191 |
-- Arrays and objects can be nested arbitrarily |
| 192 |
SELECT '{"foo": [true, "bar"], "tags": {"a": 1, "b": null}}'::json; |
| 193 |
</programlisting> |
| 194 |
</para> |
| 195 |
|
| 196 |
<para> |
| 197 |
As previously stated, when a JSON value is input and then printed without |
| 198 |
any additional processing, <type>json</type> outputs the same text that was |
| 199 |
input, while <type>jsonb</type> does not preserve semantically-insignificant |
| 200 |
details such as whitespace. For example, note the differences here: |
| 201 |
<programlisting> |
| 202 |
SELECT '{"bar": "baz", "balance": 7.77, "active":false}'::json; |
| 203 |
json |
| 204 |
------------------------------------------------- |
| 205 |
{"bar": "baz", "balance": 7.77, "active":false} |
| 206 |
(1 row) |
| 207 |
|
| 208 |
SELECT '{"bar": "baz", "balance": 7.77, "active":false}'::jsonb; |
| 209 |
jsonb |
| 210 |
-------------------------------------------------- |
| 211 |
{"bar": "baz", "active": false, "balance": 7.77} |
| 212 |
(1 row) |
| 213 |
</programlisting> |
| 214 |
One semantically-insignificant detail worth noting is that |
| 215 |
in <type>jsonb</type>, numbers will be printed according to the behavior of the |
| 216 |
underlying <type>numeric</type> type. In practice this means that numbers |
| 217 |
entered with <literal>E</literal> notation will be printed without it, for |
| 218 |
example: |
| 219 |
<programlisting> |
| 220 |
SELECT '{"reading": 1.230e-5}'::json, '{"reading": 1.230e-5}'::jsonb; |
| 221 |
json | jsonb |
| 222 |
-----------------------+------------------------- |
| 223 |
{"reading": 1.230e-5} | {"reading": 0.00001230} |
| 224 |
(1 row) |
| 225 |
</programlisting> |
| 226 |
However, <type>jsonb</type> will preserve trailing fractional zeroes, as seen |
| 227 |
in this example, even though those are semantically insignificant for |
| 228 |
purposes such as equality checks. |
| 229 |
</para> |
| 230 |
|
| 231 |
<para> |
| 232 |
For the list of built-in functions and operators available for |
| 233 |
constructing and processing JSON values, see <xref linkend="functions-json"/>. |
| 234 |
</para> |
| 235 |
</sect2> |
| 236 |
|
| 237 |
<sect2 id="json-doc-design"> |
| 238 |
<title>Designing JSON Documents</title> |
| 239 |
<para> |
| 240 |
Representing data as JSON can be considerably more flexible than |
| 241 |
the traditional relational data model, which is compelling in |
| 242 |
environments where requirements are fluid. It is quite possible |
| 243 |
for both approaches to co-exist and complement each other within |
| 244 |
the same application. However, even for applications where maximal |
| 245 |
flexibility is desired, it is still recommended that JSON documents |
| 246 |
have a somewhat fixed structure. The structure is typically |
| 247 |
unenforced (though enforcing some business rules declaratively is |
| 248 |
possible), but having a predictable structure makes it easier to write |
| 249 |
queries that usefully summarize a set of <quote>documents</quote> (datums) |
| 250 |
in a table. |
| 251 |
</para> |
| 252 |
<para> |
| 253 |
JSON data is subject to the same concurrency-control |
| 254 |
considerations as any other data type when stored in a table. |
| 255 |
Although storing large documents is practicable, keep in mind that |
| 256 |
any update acquires a row-level lock on the whole row. |
| 257 |
Consider limiting JSON documents to a |
| 258 |
manageable size in order to decrease lock contention among updating |
| 259 |
transactions. Ideally, JSON documents should each |
| 260 |
represent an atomic datum that business rules dictate cannot |
| 261 |
reasonably be further subdivided into smaller datums that |
| 262 |
could be modified independently. |
| 263 |
</para> |
| 264 |
</sect2> |
| 265 |
|
| 266 |
<sect2 id="json-containment"> |
| 267 |
<title><type>jsonb</type> Containment and Existence</title> |
| 268 |
<indexterm> |
| 269 |
<primary>jsonb</primary> |
| 270 |
<secondary>containment</secondary> |
| 271 |
</indexterm> |
| 272 |
<indexterm> |
| 273 |
<primary>jsonb</primary> |
| 274 |
<secondary>existence</secondary> |
| 275 |
</indexterm> |
| 276 |
<para> |
| 277 |
Testing <firstterm>containment</firstterm> is an important capability of |
| 278 |
<type>jsonb</type>. There is no parallel set of facilities for the |
| 279 |
<type>json</type> type. Containment tests whether |
| 280 |
one <type>jsonb</type> document has contained within it another one. |
| 281 |
These examples return true except as noted: |
| 282 |
</para> |
| 283 |
<programlisting> |
| 284 |
-- Simple scalar/primitive values contain only the identical value: |
| 285 |
SELECT '"foo"'::jsonb @> '"foo"'::jsonb; |
| 286 |
|
| 287 |
-- The array on the right side is contained within the one on the left: |
| 288 |
SELECT '[1, 2, 3]'::jsonb @> '[1, 3]'::jsonb; |
| 289 |
|
| 290 |
-- Order of array elements is not significant, so this is also true: |
| 291 |
SELECT '[1, 2, 3]'::jsonb @> '[3, 1]'::jsonb; |
| 292 |
|
| 293 |
-- Duplicate array elements don't matter either: |
| 294 |
SELECT '[1, 2, 3]'::jsonb @> '[1, 2, 2]'::jsonb; |
| 295 |
|
| 296 |
-- The object with a single pair on the right side is contained |
| 297 |
-- within the object on the left side: |
| 298 |
SELECT '{"product": "PostgreSQL", "version": 9.4, "jsonb": true}'::jsonb @> '{"version": 9.4}'::jsonb; |
| 299 |
|
| 300 |
-- The array on the right side is <emphasis>not</emphasis> considered contained within the |
| 301 |
-- array on the left, even though a similar array is nested within it: |
| 302 |
SELECT '[1, 2, [1, 3]]'::jsonb @> '[1, 3]'::jsonb; -- yields false |
| 303 |
|
| 304 |
-- But with a layer of nesting, it is contained: |
| 305 |
SELECT '[1, 2, [1, 3]]'::jsonb @> '[[1, 3]]'::jsonb; |
| 306 |
|
| 307 |
-- Similarly, containment is not reported here: |
| 308 |
SELECT '{"foo": {"bar": "baz"}}'::jsonb @> '{"bar": "baz"}'::jsonb; -- yields false |
| 309 |
|
| 310 |
-- A top-level key and an empty object is contained: |
| 311 |
SELECT '{"foo": {"bar": "baz"}}'::jsonb @> '{"foo": {}}'::jsonb; |
| 312 |
</programlisting> |
| 313 |
|
| 314 |
<para> |
| 315 |
The general principle is that the contained object must match the |
| 316 |
containing object as to structure and data contents, possibly after |
| 317 |
discarding some non-matching array elements or object key/value pairs |
| 318 |
from the containing object. |
| 319 |
But remember that the order of array elements is not significant when |
| 320 |
doing a containment match, and duplicate array elements are effectively |
| 321 |
considered only once. |
| 322 |
</para> |
| 323 |
|
| 324 |
<para> |
| 325 |
As a special exception to the general principle that the structures |
| 326 |
must match, an array may contain a primitive value: |
| 327 |
</para> |
| 328 |
<programlisting> |
| 329 |
-- This array contains the primitive string value: |
| 330 |
SELECT '["foo", "bar"]'::jsonb @> '"bar"'::jsonb; |
| 331 |
|
| 332 |
-- This exception is not reciprocal -- non-containment is reported here: |
| 333 |
SELECT '"bar"'::jsonb @> '["bar"]'::jsonb; -- yields false |
| 334 |
</programlisting> |
| 335 |
|
| 336 |
<para> |
| 337 |
<type>jsonb</type> also has an <firstterm>existence</firstterm> operator, which is |
| 338 |
a variation on the theme of containment: it tests whether a string |
| 339 |
(given as a <type>text</type> value) appears as an object key or array |
| 340 |
element at the top level of the <type>jsonb</type> value. |
| 341 |
These examples return true except as noted: |
| 342 |
</para> |
| 343 |
<programlisting> |
| 344 |
-- String exists as array element: |
| 345 |
SELECT '["foo", "bar", "baz"]'::jsonb ? 'bar'; |
| 346 |
|
| 347 |
-- String exists as object key: |
| 348 |
SELECT '{"foo": "bar"}'::jsonb ? 'foo'; |
| 349 |
|
| 350 |
-- Object values are not considered: |
| 351 |
SELECT '{"foo": "bar"}'::jsonb ? 'bar'; -- yields false |
| 352 |
|
| 353 |
-- As with containment, existence must match at the top level: |
| 354 |
SELECT '{"foo": {"bar": "baz"}}'::jsonb ? 'bar'; -- yields false |
| 355 |
|
| 356 |
-- A string is considered to exist if it matches a primitive JSON string: |
| 357 |
SELECT '"foo"'::jsonb ? 'foo'; |
| 358 |
</programlisting> |
| 359 |
|
| 360 |
<para> |
| 361 |
JSON objects are better suited than arrays for testing containment or |
| 362 |
existence when there are many keys or elements involved, because |
| 363 |
unlike arrays they are internally optimized for searching, and do not |
| 364 |
need to be searched linearly. |
| 365 |
</para> |
| 366 |
|
| 367 |
<tip> |
| 368 |
<para> |
| 369 |
Because JSON containment is nested, an appropriate query can skip |
| 370 |
explicit selection of sub-objects. As an example, suppose that we have |
| 371 |
a <structfield>doc</structfield> column containing objects at the top level, with |
| 372 |
most objects containing <literal>tags</literal> fields that contain arrays of |
| 373 |
sub-objects. This query finds entries in which sub-objects containing |
| 374 |
both <literal>"term":"paris"</literal> and <literal>"term":"food"</literal> appear, |
| 375 |
while ignoring any such keys outside the <literal>tags</literal> array: |
| 376 |
<programlisting> |
| 377 |
SELECT doc->'site_name' FROM websites |
| 378 |
WHERE doc @> '{"tags":[{"term":"paris"}, {"term":"food"}]}'; |
| 379 |
</programlisting> |
| 380 |
One could accomplish the same thing with, say, |
| 381 |
<programlisting> |
| 382 |
SELECT doc->'site_name' FROM websites |
| 383 |
WHERE doc->'tags' @> '[{"term":"paris"}, {"term":"food"}]'; |
| 384 |
</programlisting> |
| 385 |
but that approach is less flexible, and often less efficient as well. |
| 386 |
</para> |
| 387 |
|
| 388 |
<para> |
| 389 |
On the other hand, the JSON existence operator is not nested: it will |
| 390 |
only look for the specified key or array element at top level of the |
| 391 |
JSON value. |
| 392 |
</para> |
| 393 |
</tip> |
| 394 |
|
| 395 |
<para> |
| 396 |
The various containment and existence operators, along with all other |
| 397 |
JSON operators and functions are documented |
| 398 |
in <xref linkend="functions-json"/>. |
| 399 |
</para> |
| 400 |
</sect2> |
| 401 |
|
| 402 |
<sect2 id="json-indexing"> |
| 403 |
<title><type>jsonb</type> Indexing</title> |
| 404 |
<indexterm> |
| 405 |
<primary>jsonb</primary> |
| 406 |
<secondary>indexes on</secondary> |
| 407 |
</indexterm> |
| 408 |
|
| 409 |
<para> |
| 410 |
GIN indexes can be used to efficiently search for |
| 411 |
keys or key/value pairs occurring within a large number of |
| 412 |
<type>jsonb</type> documents (datums). |
| 413 |
Two GIN <quote>operator classes</quote> are provided, offering different |
| 414 |
performance and flexibility trade-offs. |
| 415 |
</para> |
| 416 |
<para> |
| 417 |
The default GIN operator class for <type>jsonb</type> supports queries with |
| 418 |
the key-exists operators <literal>?</literal>, <literal>?|</literal> |
| 419 |
and <literal>?&</literal>, the containment operator |
| 420 |
<literal>@></literal>, and the <type>jsonpath</type> match |
| 421 |
operators <literal>@?</literal> and <literal>@@</literal>. |
| 422 |
(For details of the semantics that these operators |
| 423 |
implement, see <xref linkend="functions-jsonb-op-table"/>.) |
| 424 |
An example of creating an index with this operator class is: |
| 425 |
<programlisting> |
| 426 |
CREATE INDEX idxgin ON api USING GIN (jdoc); |
| 427 |
</programlisting> |
| 428 |
The non-default GIN operator class <literal>jsonb_path_ops</literal> |
| 429 |
does not support the key-exists operators, but it does support |
| 430 |
<literal>@></literal>, <literal>@?</literal> and <literal>@@</literal>. |
| 431 |
An example of creating an index with this operator class is: |
| 432 |
<programlisting> |
| 433 |
CREATE INDEX idxginp ON api USING GIN (jdoc jsonb_path_ops); |
| 434 |
</programlisting> |
| 435 |
</para> |
| 436 |
|
| 437 |
<para> |
| 438 |
Consider the example of a table that stores JSON documents |
| 439 |
retrieved from a third-party web service, with a documented schema |
| 440 |
definition. A typical document is: |
| 441 |
<programlisting> |
| 442 |
{ |
| 443 |
"guid": "9c36adc1-7fb5-4d5b-83b4-90356a46061a", |
| 444 |
"name": "Angela Barton", |
| 445 |
"is_active": true, |
| 446 |
"company": "Magnafone", |
| 447 |
"address": "178 Howard Place, Gulf, Washington, 702", |
| 448 |
"registered": "2009-11-07T08:53:22 +08:00", |
| 449 |
"latitude": 19.793713, |
| 450 |
"longitude": 86.513373, |
| 451 |
"tags": [ |
| 452 |
"enim", |
| 453 |
"aliquip", |
| 454 |
"qui" |
| 455 |
] |
| 456 |
} |
| 457 |
</programlisting> |
| 458 |
We store these documents in a table named <structname>api</structname>, |
| 459 |
in a <type>jsonb</type> column named <structfield>jdoc</structfield>. |
| 460 |
If a GIN index is created on this column, |
| 461 |
queries like the following can make use of the index: |
| 462 |
<programlisting> |
| 463 |
-- Find documents in which the key "company" has value "Magnafone" |
| 464 |
SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"company": "Magnafone"}'; |
| 465 |
</programlisting> |
| 466 |
However, the index could not be used for queries like the |
| 467 |
following, because though the operator <literal>?</literal> is indexable, |
| 468 |
it is not applied directly to the indexed column <structfield>jdoc</structfield>: |
| 469 |
<programlisting> |
| 470 |
-- Find documents in which the key "tags" contains key or array element "qui" |
| 471 |
SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc -> 'tags' ? 'qui'; |
| 472 |
</programlisting> |
| 473 |
Still, with appropriate use of expression indexes, the above |
| 474 |
query can use an index. If querying for particular items within |
| 475 |
the <literal>"tags"</literal> key is common, defining an index like this |
| 476 |
may be worthwhile: |
| 477 |
<programlisting> |
| 478 |
CREATE INDEX idxgintags ON api USING GIN ((jdoc -> 'tags')); |
| 479 |
</programlisting> |
| 480 |
Now, the <literal>WHERE</literal> clause <literal>jdoc -> 'tags' ? 'qui'</literal> |
| 481 |
will be recognized as an application of the indexable |
| 482 |
operator <literal>?</literal> to the indexed |
| 483 |
expression <literal>jdoc -> 'tags'</literal>. |
| 484 |
(More information on expression indexes can be found in <xref |
| 485 |
linkend="indexes-expressional"/>.) |
| 486 |
</para> |
| 487 |
|
| 488 |
<para> |
| 489 |
Another approach to querying is to exploit containment, for example: |
| 490 |
<programlisting> |
| 491 |
-- Find documents in which the key "tags" contains array element "qui" |
| 492 |
SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"tags": ["qui"]}'; |
| 493 |
</programlisting> |
| 494 |
A simple GIN index on the <structfield>jdoc</structfield> column can support this |
| 495 |
query. But note that such an index will store copies of every key and |
| 496 |
value in the <structfield>jdoc</structfield> column, whereas the expression index |
| 497 |
of the previous example stores only data found under |
| 498 |
the <literal>tags</literal> key. While the simple-index approach is far more |
| 499 |
flexible (since it supports queries about any key), targeted expression |
| 500 |
indexes are likely to be smaller and faster to search than a simple |
| 501 |
index. |
| 502 |
</para> |
| 503 |
|
| 504 |
<para> |
| 505 |
GIN indexes also support the <literal>@?</literal> |
| 506 |
and <literal>@@</literal> operators, which |
| 507 |
perform <type>jsonpath</type> matching. Examples are |
| 508 |
<programlisting> |
| 509 |
SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @? '$.tags[*] ? (@ == "qui")'; |
| 510 |
</programlisting> |
| 511 |
<programlisting> |
| 512 |
SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @@ '$.tags[*] == "qui"'; |
| 513 |
</programlisting> |
| 514 |
For these operators, a GIN index extracts clauses of the form |
| 515 |
<literal><replaceable>accessors_chain</replaceable> |
| 516 |
== <replaceable>constant</replaceable></literal> out of |
| 517 |
the <type>jsonpath</type> pattern, and does the index search based on |
| 518 |
the keys and values mentioned in these clauses. The accessors chain |
| 519 |
may include <literal>.<replaceable>key</replaceable></literal>, |
| 520 |
<literal>[*]</literal>, |
| 521 |
and <literal>[<replaceable>index</replaceable>]</literal> accessors. |
| 522 |
The <literal>jsonb_ops</literal> operator class also |
| 523 |
supports <literal>.*</literal> and <literal>.**</literal> accessors, |
| 524 |
but the <literal>jsonb_path_ops</literal> operator class does not. |
| 525 |
</para> |
| 526 |
|
| 527 |
<para> |
| 528 |
Although the <literal>jsonb_path_ops</literal> operator class supports |
| 529 |
only queries with the <literal>@></literal>, <literal>@?</literal> |
| 530 |
and <literal>@@</literal> operators, it has notable |
| 531 |
performance advantages over the default operator |
| 532 |
class <literal>jsonb_ops</literal>. A <literal>jsonb_path_ops</literal> |
| 533 |
index is usually much smaller than a <literal>jsonb_ops</literal> |
| 534 |
index over the same data, and the specificity of searches is better, |
| 535 |
particularly when queries contain keys that appear frequently in the |
| 536 |
data. Therefore search operations typically perform better |
| 537 |
than with the default operator class. |
| 538 |
</para> |
| 539 |
|
| 540 |
<para> |
| 541 |
The technical difference between a <literal>jsonb_ops</literal> |
| 542 |
and a <literal>jsonb_path_ops</literal> GIN index is that the former |
| 543 |
creates independent index items for each key and value in the data, |
| 544 |
while the latter creates index items only for each value in the |
| 545 |
data. |
| 546 |
<footnote> |
| 547 |
<para> |
| 548 |
For this purpose, the term <quote>value</quote> includes array elements, |
| 549 |
though JSON terminology sometimes considers array elements distinct |
| 550 |
from values within objects. |
| 551 |
</para> |
| 552 |
</footnote> |
| 553 |
Basically, each <literal>jsonb_path_ops</literal> index item is |
| 554 |
a hash of the value and the key(s) leading to it; for example to index |
| 555 |
<literal>{"foo": {"bar": "baz"}}</literal>, a single index item would |
| 556 |
be created incorporating all three of <literal>foo</literal>, <literal>bar</literal>, |
| 557 |
and <literal>baz</literal> into the hash value. Thus a containment query |
| 558 |
looking for this structure would result in an extremely specific index |
| 559 |
search; but there is no way at all to find out whether <literal>foo</literal> |
| 560 |
appears as a key. On the other hand, a <literal>jsonb_ops</literal> |
| 561 |
index would create three index items representing <literal>foo</literal>, |
| 562 |
<literal>bar</literal>, and <literal>baz</literal> separately; then to do the |
| 563 |
containment query, it would look for rows containing all three of |
| 564 |
these items. While GIN indexes can perform such an AND search fairly |
| 565 |
efficiently, it will still be less specific and slower than the |
| 566 |
equivalent <literal>jsonb_path_ops</literal> search, especially if |
| 567 |
there are a very large number of rows containing any single one of the |
| 568 |
three index items. |
| 569 |
</para> |
| 570 |
|
| 571 |
<para> |
| 572 |
A disadvantage of the <literal>jsonb_path_ops</literal> approach is |
| 573 |
that it produces no index entries for JSON structures not containing |
| 574 |
any values, such as <literal>{"a": {}}</literal>. If a search for |
| 575 |
documents containing such a structure is requested, it will require a |
| 576 |
full-index scan, which is quite slow. <literal>jsonb_path_ops</literal> is |
| 577 |
therefore ill-suited for applications that often perform such searches. |
| 578 |
</para> |
| 579 |
|
| 580 |
<para> |
| 581 |
<type>jsonb</type> also supports <literal>btree</literal> and <literal>hash</literal> |
| 582 |
indexes. These are usually useful only if it's important to check |
| 583 |
equality of complete JSON documents. |
| 584 |
The <literal>btree</literal> ordering for <type>jsonb</type> datums is seldom |
| 585 |
of great interest, but for completeness it is: |
| 586 |
<synopsis> |
| 587 |
<replaceable>Object</replaceable> > <replaceable>Array</replaceable> > <replaceable>Boolean</replaceable> > <replaceable>Number</replaceable> > <replaceable>String</replaceable> > <replaceable>null</replaceable> |
| 588 |
|
| 589 |
<replaceable>Object with n pairs</replaceable> > <replaceable>object with n - 1 pairs</replaceable> |
| 590 |
|
| 591 |
<replaceable>Array with n elements</replaceable> > <replaceable>array with n - 1 elements</replaceable> |
| 592 |
</synopsis> |
| 593 |
with the exception that (for historical reasons) an empty top level array sorts less than <replaceable>null</replaceable>. |
| 594 |
Objects with equal numbers of pairs are compared in the order: |
| 595 |
<synopsis> |
| 596 |
<replaceable>key-1</replaceable>, <replaceable>value-1</replaceable>, <replaceable>key-2</replaceable> ... |
| 597 |
</synopsis> |
| 598 |
Note that object keys are compared in their storage order; |
| 599 |
in particular, since shorter keys are stored before longer keys, this |
| 600 |
can lead to results that might be unintuitive, such as: |
| 601 |
<programlisting> |
| 602 |
{ "aa": 1, "c": 1} > {"b": 1, "d": 1} |
| 603 |
</programlisting> |
| 604 |
Similarly, arrays with equal numbers of elements are compared in the |
| 605 |
order: |
| 606 |
<synopsis> |
| 607 |
<replaceable>element-1</replaceable>, <replaceable>element-2</replaceable> ... |
| 608 |
</synopsis> |
| 609 |
Primitive JSON values are compared using the same |
| 610 |
comparison rules as for the underlying |
| 611 |
<productname>PostgreSQL</productname> data type. Strings are |
| 612 |
compared using the default database collation. |
| 613 |
</para> |
| 614 |
</sect2> |
| 615 |
|
| 616 |
<sect2 id="jsonb-subscripting"> |
| 617 |
<title><type>jsonb</type> Subscripting</title> |
| 618 |
<para> |
| 619 |
The <type>jsonb</type> data type supports array-style subscripting expressions |
| 620 |
to extract and modify elements. Nested values can be indicated by chaining |
| 621 |
subscripting expressions, following the same rules as the <literal>path</literal> |
| 622 |
argument in the <literal>jsonb_set</literal> function. If a <type>jsonb</type> |
| 623 |
value is an array, numeric subscripts start at zero, and negative integers count |
| 624 |
backwards from the last element of the array. Slice expressions are not supported. |
| 625 |
The result of a subscripting expression is always of the jsonb data type. |
| 626 |
</para> |
| 627 |
|
| 628 |
<para> |
| 629 |
<command>UPDATE</command> statements may use subscripting in the |
| 630 |
<literal>SET</literal> clause to modify <type>jsonb</type> values. Subscript |
| 631 |
paths must be traversable for all affected values insofar as they exist. For |
| 632 |
instance, the path <literal>val['a']['b']['c']</literal> can be traversed all |
| 633 |
the way to <literal>c</literal> if every <literal>val</literal>, |
| 634 |
<literal>val['a']</literal>, and <literal>val['a']['b']</literal> is an |
| 635 |
object. If any <literal>val['a']</literal> or <literal>val['a']['b']</literal> |
| 636 |
is not defined, it will be created as an empty object and filled as |
| 637 |
necessary. However, if any <literal>val</literal> itself or one of the |
| 638 |
intermediary values is defined as a non-object such as a string, number, or |
| 639 |
<literal>jsonb</literal> <literal>null</literal>, traversal cannot proceed so |
| 640 |
an error is raised and the transaction aborted. |
| 641 |
</para> |
| 642 |
|
| 643 |
<para> |
| 644 |
An example of subscripting syntax: |
| 645 |
|
| 646 |
<programlisting> |
| 647 |
|
| 648 |
-- Extract object value by key |
| 649 |
SELECT ('{"a": 1}'::jsonb)['a']; |
| 650 |
|
| 651 |
-- Extract nested object value by key path |
| 652 |
SELECT ('{"a": {"b": {"c": 1}}}'::jsonb)['a']['b']['c']; |
| 653 |
|
| 654 |
-- Extract array element by index |
| 655 |
SELECT ('[1, "2", null]'::jsonb)[1]; |
| 656 |
|
| 657 |
-- Update object value by key. Note the quotes around '1': the assigned |
| 658 |
-- value must be of the jsonb type as well |
| 659 |
UPDATE table_name SET jsonb_field['key'] = '1'; |
| 660 |
|
| 661 |
-- This will raise an error if any record's jsonb_field['a']['b'] is something |
| 662 |
-- other than an object. For example, the value {"a": 1} has a numeric value |
| 663 |
-- of the key 'a'. |
| 664 |
UPDATE table_name SET jsonb_field['a']['b']['c'] = '1'; |
| 665 |
|
| 666 |
-- Filter records using a WHERE clause with subscripting. Since the result of |
| 667 |
-- subscripting is jsonb, the value we compare it against must also be jsonb. |
| 668 |
-- The double quotes make "value" also a valid jsonb string. |
| 669 |
SELECT * FROM table_name WHERE jsonb_field['key'] = '"value"'; |
| 670 |
</programlisting> |
| 671 |
|
| 672 |
<type>jsonb</type> assignment via subscripting handles a few edge cases |
| 673 |
differently from <literal>jsonb_set</literal>. When a source <type>jsonb</type> |
| 674 |
value is <literal>NULL</literal>, assignment via subscripting will proceed |
| 675 |
as if it was an empty JSON value of the type (object or array) implied by the |
| 676 |
subscript key: |
| 677 |
|
| 678 |
<programlisting> |
| 679 |
-- Where jsonb_field was NULL, it is now {"a": 1} |
| 680 |
UPDATE table_name SET jsonb_field['a'] = '1'; |
| 681 |
|
| 682 |
-- Where jsonb_field was NULL, it is now [1] |
| 683 |
UPDATE table_name SET jsonb_field[0] = '1'; |
| 684 |
</programlisting> |
| 685 |
|
| 686 |
If an index is specified for an array containing too few elements, |
| 687 |
<literal>NULL</literal> elements will be appended until the index is reachable |
| 688 |
and the value can be set. |
| 689 |
|
| 690 |
<programlisting> |
| 691 |
-- Where jsonb_field was [], it is now [null, null, 2]; |
| 692 |
-- where jsonb_field was [0], it is now [0, null, 2] |
| 693 |
UPDATE table_name SET jsonb_field[2] = '2'; |
| 694 |
</programlisting> |
| 695 |
|
| 696 |
A <type>jsonb</type> value will accept assignments to nonexistent subscript |
| 697 |
paths as long as the last existing element to be traversed is an object or |
| 698 |
array, as implied by the corresponding subscript (the element indicated by |
| 699 |
the last subscript in the path is not traversed and may be anything). Nested |
| 700 |
array and object structures will be created, and in the former case |
| 701 |
<literal>null</literal>-padded, as specified by the subscript path until the |
| 702 |
assigned value can be placed. |
| 703 |
|
| 704 |
<programlisting> |
| 705 |
-- Where jsonb_field was {}, it is now {"a": [{"b": 1}]} |
| 706 |
UPDATE table_name SET jsonb_field['a'][0]['b'] = '1'; |
| 707 |
|
| 708 |
-- Where jsonb_field was [], it is now [null, {"a": 1}] |
| 709 |
UPDATE table_name SET jsonb_field[1]['a'] = '1'; |
| 710 |
</programlisting> |
| 711 |
|
| 712 |
</para> |
| 713 |
</sect2> |
| 714 |
|
| 715 |
<sect2 id="datatype-json-transforms"> |
| 716 |
<title>Transforms</title> |
| 717 |
|
| 718 |
<para> |
| 719 |
Additional extensions are available that implement transforms for the |
| 720 |
<type>jsonb</type> type for different procedural languages. |
| 721 |
</para> |
| 722 |
|
| 723 |
<para> |
| 724 |
The extensions for PL/Perl are called <literal>jsonb_plperl</literal> and |
| 725 |
<literal>jsonb_plperlu</literal>. If you use them, <type>jsonb</type> |
| 726 |
values are mapped to Perl arrays, hashes, and scalars, as appropriate. |
| 727 |
</para> |
| 728 |
|
| 729 |
<para> |
| 730 |
The extension for PL/Python is called <literal>jsonb_plpython3u</literal>. |
| 731 |
If you use it, <type>jsonb</type> values are mapped to Python |
| 732 |
dictionaries, lists, and scalars, as appropriate. |
| 733 |
</para> |
| 734 |
|
| 735 |
<para> |
| 736 |
Of these extensions, <literal>jsonb_plperl</literal> is |
| 737 |
considered <quote>trusted</quote>, that is, it can be installed by |
| 738 |
non-superusers who have <literal>CREATE</literal> privilege on the |
| 739 |
current database. The rest require superuser privilege to install. |
| 740 |
</para> |
| 741 |
</sect2> |
| 742 |
|
| 743 |
<sect2 id="datatype-jsonpath"> |
| 744 |
<title>jsonpath Type</title> |
| 745 |
|
| 746 |
<indexterm zone="datatype-jsonpath"> |
| 747 |
<primary>jsonpath</primary> |
| 748 |
</indexterm> |
| 749 |
|
| 750 |
<para> |
| 751 |
The <type>jsonpath</type> type implements support for the SQL/JSON path language |
| 752 |
in <productname>PostgreSQL</productname> to efficiently query JSON data. |
| 753 |
It provides a binary representation of the parsed SQL/JSON path |
| 754 |
expression that specifies the items to be retrieved by the path |
| 755 |
engine from the JSON data for further processing with the |
| 756 |
SQL/JSON query functions. |
| 757 |
</para> |
| 758 |
|
| 759 |
<para> |
| 760 |
The semantics of SQL/JSON path predicates and operators generally follow SQL. |
| 761 |
At the same time, to provide a natural way of working with JSON data, |
| 762 |
SQL/JSON path syntax uses some JavaScript conventions: |
| 763 |
</para> |
| 764 |
|
| 765 |
<itemizedlist> |
| 766 |
<listitem> |
| 767 |
<para> |
| 768 |
Dot (<literal>.</literal>) is used for member access. |
| 769 |
</para> |
| 770 |
</listitem> |
| 771 |
<listitem> |
| 772 |
<para> |
| 773 |
Square brackets (<literal>[]</literal>) are used for array access. |
| 774 |
</para> |
| 775 |
</listitem> |
| 776 |
<listitem> |
| 777 |
<para> |
| 778 |
SQL/JSON arrays are 0-relative, unlike regular SQL arrays that start from 1. |
| 779 |
</para> |
| 780 |
</listitem> |
| 781 |
</itemizedlist> |
| 782 |
|
| 783 |
<para> |
| 784 |
Numeric literals in SQL/JSON path expressions follow JavaScript rules, |
| 785 |
which are different from both SQL and JSON in some minor details. For |
| 786 |
example, SQL/JSON path allows <literal>.1</literal> and |
| 787 |
<literal>1.</literal>, which are invalid in JSON. Non-decimal integer |
| 788 |
literals and underscore separators are supported, for example, |
| 789 |
<literal>1_000_000</literal>, <literal>0x1EEE_FFFF</literal>, |
| 790 |
<literal>0o273</literal>, <literal>0b100101</literal>. In SQL/JSON path |
| 791 |
(and in JavaScript, but not in SQL proper), there must not be an underscore |
| 792 |
separator directly after the radix prefix. |
| 793 |
</para> |
| 794 |
|
| 795 |
<para> |
| 796 |
An SQL/JSON path expression is typically written in an SQL query as an |
| 797 |
SQL character string literal, so it must be enclosed in single quotes, |
| 798 |
and any single quotes desired within the value must be doubled |
| 799 |
(see <xref linkend="sql-syntax-strings"/>). |
| 800 |
Some forms of path expressions require string literals within them. |
| 801 |
These embedded string literals follow JavaScript/ECMAScript conventions: |
| 802 |
they must be surrounded by double quotes, and backslash escapes may be |
| 803 |
used within them to represent otherwise-hard-to-type characters. |
| 804 |
In particular, the way to write a double quote within an embedded string |
| 805 |
literal is <literal>\"</literal>, and to write a backslash itself, you |
| 806 |
must write <literal>\\</literal>. Other special backslash sequences |
| 807 |
include those recognized in JavaScript strings: |
| 808 |
<literal>\b</literal>, |
| 809 |
<literal>\f</literal>, |
| 810 |
<literal>\n</literal>, |
| 811 |
<literal>\r</literal>, |
| 812 |
<literal>\t</literal>, |
| 813 |
<literal>\v</literal> |
| 814 |
for various ASCII control characters, |
| 815 |
<literal>\x<replaceable>NN</replaceable></literal> for a character code |
| 816 |
written with only two hex digits, |
| 817 |
<literal>\u<replaceable>NNNN</replaceable></literal> for a Unicode |
| 818 |
character identified by its 4-hex-digit code point, and |
| 819 |
<literal>\u{<replaceable>N...</replaceable>}</literal> for a Unicode |
| 820 |
character code point written with 1 to 6 hex digits. |
| 821 |
</para> |
| 822 |
|
| 823 |
<para> |
| 824 |
A path expression consists of a sequence of path elements, |
| 825 |
which can be any of the following: |
| 826 |
<itemizedlist> |
| 827 |
<listitem> |
| 828 |
<para> |
| 829 |
Path literals of JSON primitive types: |
| 830 |
Unicode text, numeric, true, false, or null. |
| 831 |
</para> |
| 832 |
</listitem> |
| 833 |
<listitem> |
| 834 |
<para> |
| 835 |
Path variables listed in <xref linkend="type-jsonpath-variables"/>. |
| 836 |
</para> |
| 837 |
</listitem> |
| 838 |
<listitem> |
| 839 |
<para> |
| 840 |
Accessor operators listed in <xref linkend="type-jsonpath-accessors"/>. |
| 841 |
</para> |
| 842 |
</listitem> |
| 843 |
<listitem> |
| 844 |
<para> |
| 845 |
<type>jsonpath</type> operators and methods listed |
| 846 |
in <xref linkend="functions-sqljson-path-operators"/>. |
| 847 |
</para> |
| 848 |
</listitem> |
| 849 |
<listitem> |
| 850 |
<para> |
| 851 |
Parentheses, which can be used to provide filter expressions |
| 852 |
or define the order of path evaluation. |
| 853 |
</para> |
| 854 |
</listitem> |
| 855 |
</itemizedlist> |
| 856 |
</para> |
| 857 |
|
| 858 |
<para> |
| 859 |
For details on using <type>jsonpath</type> expressions with SQL/JSON |
| 860 |
query functions, see <xref linkend="functions-sqljson-path"/>. |
| 861 |
</para> |
| 862 |
|
| 863 |
<table id="type-jsonpath-variables"> |
| 864 |
<title><type>jsonpath</type> Variables</title> |
| 865 |
<tgroup cols="2"> |
| 866 |
<colspec colname="col1" colwidth="1*"/> |
| 867 |
<colspec colname="col2" colwidth="2*"/> |
| 868 |
<thead> |
| 869 |
<row> |
| 870 |
<entry>Variable</entry> |
| 871 |
<entry>Description</entry> |
| 872 |
</row> |
| 873 |
</thead> |
| 874 |
<tbody> |
| 875 |
<row> |
| 876 |
<entry><literal>$</literal></entry> |
| 877 |
<entry>A variable representing the JSON value being queried |
| 878 |
(the <firstterm>context item</firstterm>). |
| 879 |
</entry> |
| 880 |
</row> |
| 881 |
<row> |
| 882 |
<entry><literal>$varname</literal></entry> |
| 883 |
<entry> |
| 884 |
A named variable. Its value can be set by the parameter |
| 885 |
<parameter>vars</parameter> of several JSON processing functions; |
| 886 |
see <xref linkend="functions-json-processing-table"/> for details. |
| 887 |
<!-- TODO: describe PASSING clause once implemented !--> |
| 888 |
</entry> |
| 889 |
</row> |
| 890 |
<row> |
| 891 |
<entry><literal>@</literal></entry> |
| 892 |
<entry>A variable representing the result of path evaluation |
| 893 |
in filter expressions. |
| 894 |
</entry> |
| 895 |
</row> |
| 896 |
</tbody> |
| 897 |
</tgroup> |
| 898 |
</table> |
| 899 |
|
| 900 |
<table id="type-jsonpath-accessors"> |
| 901 |
<title><type>jsonpath</type> Accessors</title> |
| 902 |
<tgroup cols="2"> |
| 903 |
<colspec colname="col1" colwidth="1*"/> |
| 904 |
<colspec colname="col2" colwidth="2*"/> |
| 905 |
<thead> |
| 906 |
<row> |
| 907 |
<entry>Accessor Operator</entry> |
| 908 |
<entry>Description</entry> |
| 909 |
</row> |
| 910 |
</thead> |
| 911 |
<tbody> |
| 912 |
<row> |
| 913 |
<entry> |
| 914 |
<para> |
| 915 |
<literal>.<replaceable>key</replaceable></literal> |
| 916 |
</para> |
| 917 |
<para> |
| 918 |
<literal>."$<replaceable>varname</replaceable>"</literal> |
| 919 |
</para> |
| 920 |
</entry> |
| 921 |
<entry> |
| 922 |
<para> |
| 923 |
Member accessor that returns an object member with |
| 924 |
the specified key. If the key name matches some named variable |
| 925 |
starting with <literal>$</literal> or does not meet the |
| 926 |
JavaScript rules for an identifier, it must be enclosed in |
| 927 |
double quotes to make it a string literal. |
| 928 |
</para> |
| 929 |
</entry> |
| 930 |
</row> |
| 931 |
<row> |
| 932 |
<entry> |
| 933 |
<para> |
| 934 |
<literal>.*</literal> |
| 935 |
</para> |
| 936 |
</entry> |
| 937 |
<entry> |
| 938 |
<para> |
| 939 |
Wildcard member accessor that returns the values of all |
| 940 |
members located at the top level of the current object. |
| 941 |
</para> |
| 942 |
</entry> |
| 943 |
</row> |
| 944 |
<row> |
| 945 |
<entry> |
| 946 |
<para> |
| 947 |
<literal>.**</literal> |
| 948 |
</para> |
| 949 |
</entry> |
| 950 |
<entry> |
| 951 |
<para> |
| 952 |
Recursive wildcard member accessor that processes all levels |
| 953 |
of the JSON hierarchy of the current object and returns all |
| 954 |
the member values, regardless of their nesting level. This |
| 955 |
is a <productname>PostgreSQL</productname> extension of |
| 956 |
the SQL/JSON standard. |
| 957 |
</para> |
| 958 |
</entry> |
| 959 |
</row> |
| 960 |
<row> |
| 961 |
<entry> |
| 962 |
<para> |
| 963 |
<literal>.**{<replaceable>level</replaceable>}</literal> |
| 964 |
</para> |
| 965 |
<para> |
| 966 |
<literal>.**{<replaceable>start_level</replaceable> to |
| 967 |
<replaceable>end_level</replaceable>}</literal> |
| 968 |
</para> |
| 969 |
</entry> |
| 970 |
<entry> |
| 971 |
<para> |
| 972 |
Like <literal>.**</literal>, but selects only the specified |
| 973 |
levels of the JSON hierarchy. Nesting levels are specified as integers. |
| 974 |
Level zero corresponds to the current object. To access the lowest |
| 975 |
nesting level, you can use the <literal>last</literal> keyword. |
| 976 |
This is a <productname>PostgreSQL</productname> extension of |
| 977 |
the SQL/JSON standard. |
| 978 |
</para> |
| 979 |
</entry> |
| 980 |
</row> |
| 981 |
<row> |
| 982 |
<entry> |
| 983 |
<para> |
| 984 |
<literal>[<replaceable>subscript</replaceable>, ...]</literal> |
| 985 |
</para> |
| 986 |
</entry> |
| 987 |
<entry> |
| 988 |
<para> |
| 989 |
Array element accessor. |
| 990 |
<literal><replaceable>subscript</replaceable></literal> can be |
| 991 |
given in two forms: <literal><replaceable>index</replaceable></literal> |
| 992 |
or <literal><replaceable>start_index</replaceable> to <replaceable>end_index</replaceable></literal>. |
| 993 |
The first form returns a single array element by its index. The second |
| 994 |
form returns an array slice by the range of indexes, including the |
| 995 |
elements that correspond to the provided |
| 996 |
<replaceable>start_index</replaceable> and <replaceable>end_index</replaceable>. |
| 997 |
</para> |
| 998 |
<para> |
| 999 |
The specified <replaceable>index</replaceable> can be an integer, as |
| 1000 |
well as an expression returning a single numeric value, which is |
| 1001 |
automatically cast to integer. Index zero corresponds to the first |
| 1002 |
array element. You can also use the <literal>last</literal> keyword |
| 1003 |
to denote the last array element, which is useful for handling arrays |
| 1004 |
of unknown length. |
| 1005 |
</para> |
| 1006 |
</entry> |
| 1007 |
</row> |
| 1008 |
<row> |
| 1009 |
<entry> |
| 1010 |
<para> |
| 1011 |
<literal>[*]</literal> |
| 1012 |
</para> |
| 1013 |
</entry> |
| 1014 |
<entry> |
| 1015 |
<para> |
| 1016 |
Wildcard array element accessor that returns all array elements. |
| 1017 |
</para> |
| 1018 |
</entry> |
| 1019 |
</row> |
| 1020 |
</tbody> |
| 1021 |
</tgroup> |
| 1022 |
</table> |
| 1023 |
|
| 1024 |
</sect2> |
| 1025 |
</sect1> |