{"Entry":{"collection":"type","key":"jsonb","name":"jsonb","aliases":[],"metadata":{"aliases":[],"category":"JSON","content_hash":"38a9c4b5734ad0fb1a39bae4cde6b483544ea0bc640c226c48ed9487ce015035","imported_at":"2026-09-30T00:40:36.085404+08:00","name":"jsonb","name_zh":"","slug":"jsonb","summary":"binary JSON data, decomposed"}},"Definition":{"Collection":"type","Key":"jsonb","SourceDatabase":"center","Version":"18","SourceTable":"data_type","SourceKey":"jsonb","SourceRevision":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","Facts":{"aliases":[],"casts":[{"castcontext":"a","castfunc":"0","castmethod":"i","castsource":"json","casttarget":"jsonb"},{"castcontext":"a","castfunc":"0","castmethod":"i","castsource":"jsonb","casttarget":"json"},{"castcontext":"e","castfunc":"bool(jsonb)","castmethod":"f","castsource":"jsonb","casttarget":"bool"},{"castcontext":"e","castfunc":"numeric(jsonb)","castmethod":"f","castsource":"jsonb","casttarget":"numeric"},{"castcontext":"e","castfunc":"int2(jsonb)","castmethod":"f","castsource":"jsonb","casttarget":"int2"},{"castcontext":"e","castfunc":"int4(jsonb)","castmethod":"f","castsource":"jsonb","casttarget":"int4"},{"castcontext":"e","castfunc":"int8(jsonb)","castmethod":"f","castsource":"jsonb","casttarget":"int8"},{"castcontext":"e","castfunc":"float4(jsonb)","castmethod":"f","castsource":"jsonb","casttarget":"float4"},{"castcontext":"e","castfunc":"float8(jsonb)","castmethod":"f","castsource":"jsonb","casttarget":"float8"}],"catalog":{"array_type_name":"_jsonb","array_type_oid":"3807","descr":"Binary JSON","oid":"3802","typacl":"_null_","typalign":"i","typanalyze":"-","typarray":"0","typbasetype":"0","typbyval":"f","typcategory":"U","typcollation":"0","typdefault":"_null_","typdefaultbin":"_null_","typdelim":"','","typelem":"0","typinput":"jsonb_in","typisdefined":"t","typispreferred":"f","typlen":"-1","typmodin":"-","typmodout":"-","typname":"jsonb","typnamespace":"pg_catalog","typndims":"0","typnotnull":"f","typoutput":"jsonb_out","typowner":"POSTGRES","typreceive":"jsonb_recv","typrelid":"0","typsend":"jsonb_send","typstorage":"x","typsubscript":"jsonb_subscript_handler","typtype":"b","typtypmod":"-1"},"comparison_data":{"aliases":[],"casts":[{"castcontext":"a","castfunc":"0","castmethod":"i","castsource":"json","casttarget":"jsonb"},{"castcontext":"a","castfunc":"0","castmethod":"i","castsource":"jsonb","casttarget":"json"},{"castcontext":"e","castfunc":"bool(jsonb)","castmethod":"f","castsource":"jsonb","casttarget":"bool"},{"castcontext":"e","castfunc":"float4(jsonb)","castmethod":"f","castsource":"jsonb","casttarget":"float4"},{"castcontext":"e","castfunc":"float8(jsonb)","castmethod":"f","castsource":"jsonb","casttarget":"float8"},{"castcontext":"e","castfunc":"int2(jsonb)","castmethod":"f","castsource":"jsonb","casttarget":"int2"},{"castcontext":"e","castfunc":"int4(jsonb)","castmethod":"f","castsource":"jsonb","casttarget":"int4"},{"castcontext":"e","castfunc":"int8(jsonb)","castmethod":"f","castsource":"jsonb","casttarget":"int8"},{"castcontext":"e","castfunc":"numeric(jsonb)","castmethod":"f","castsource":"jsonb","casttarget":"numeric"}],"catalog":{"array_type_name":"_jsonb","typacl":"_null_","typalign":"i","typanalyze":"-","typarray":"_jsonb","typbasetype":"0","typbyval":"f","typcategory":"U","typcollation":"0","typdefault":"_null_","typdefaultbin":"_null_","typdelim":",","typelem":"0","typinput":"jsonb_in","typisdefined":"t","typispreferred":"f","typlen":"-1","typmodin":"-","typmodout":"-","typname":"jsonb","typndims":"0","typnotnull":"f","typoutput":"jsonb_out","typreceive":"jsonb_recv","typrelid":"0","typsend":"jsonb_send","typstorage":"x","typsubscript":"jsonb_subscript_handler","typtype":"b","typtypmod":"-1"},"facts":[{"label":"Catalog name","value":"pg_catalog.jsonb"},{"label":"Declared length","value":"Variable length (varlena)"},{"label":"Input function","value":"jsonb_in"},{"label":"Output function","value":"jsonb_out"},{"label":"Storage strategy","value":"extended"},{"label":"Type OID","value":"3802"},{"label":"Type kind","value":"Base type"}],"operator_classes":[{"opcdefault":"f","opcfamily":"gin/jsonb_path_ops","opcintype":"jsonb","opckeytype":"int4","opcmethod":"gin","opcname":"jsonb_path_ops"},{"opcdefault":"t","opcfamily":"btree/jsonb_ops","opcintype":"jsonb","opckeytype":"0","opcmethod":"btree","opcname":"jsonb_ops"},{"opcdefault":"t","opcfamily":"gin/jsonb_ops","opcintype":"jsonb","opckeytype":"text","opcmethod":"gin","opcname":"jsonb_ops"},{"opcdefault":"t","opcfamily":"hash/jsonb_ops","opcintype":"jsonb","opckeytype":"0","opcmethod":"hash","opcname":"jsonb_ops"}],"operators":[{"oprcanhash":"f","oprcanmerge":"f","oprcode":"jsonb_array_element","oprcom":"0","oprjoin":"-","oprkind":"b","oprleft":"jsonb","oprname":"-\u003e","oprnegate":"0","oprrest":"-","oprresult":"jsonb","oprright":"int4"},{"oprcanhash":"f","oprcanmerge":"f","oprcode":"jsonb_array_element_text","oprcom":"0","oprjoin":"-","oprkind":"b","oprleft":"jsonb","oprname":"-\u003e\u003e","oprnegate":"0","oprrest":"-","oprresult":"text","oprright":"int4"},{"oprcanhash":"f","oprcanmerge":"f","oprcode":"jsonb_concat","oprcom":"0","oprjoin":"-","oprkind":"b","oprleft":"jsonb","oprname":"||","oprnegate":"0","oprrest":"-","oprresult":"jsonb","oprright":"jsonb"},{"oprcanhash":"f","oprcanmerge":"f","oprcode":"jsonb_contained","oprcom":"@\u003e(jsonb,jsonb)","oprjoin":"matchingjoinsel","oprkind":"b","oprleft":"jsonb","oprname":"\u003c@","oprnegate":"0","oprrest":"matchingsel","oprresult":"bool","oprright":"jsonb"},{"oprcanhash":"f","oprcanmerge":"f","oprcode":"jsonb_contains","oprcom":"\u003c@(jsonb,jsonb)","oprjoin":"matchingjoinsel","oprkind":"b","oprleft":"jsonb","oprname":"@\u003e","oprnegate":"0","oprrest":"matchingsel","oprresult":"bool","oprright":"jsonb"},{"oprcanhash":"f","oprcanmerge":"f","oprcode":"jsonb_delete","oprcom":"0","oprjoin":"-","oprkind":"b","oprleft":"jsonb","oprname":"-","oprnegate":"0","oprrest":"-","oprresult":"jsonb","oprright":"_text"},{"oprcanhash":"f","oprcanmerge":"f","oprcode":"jsonb_delete","oprcom":"0","oprjoin":"-","oprkind":"b","oprleft":"jsonb","oprname":"-","oprnegate":"0","oprrest":"-","oprresult":"jsonb","oprright":"int4"},{"oprcanhash":"f","oprcanmerge":"f","oprcode":"jsonb_delete","oprcom":"0","oprjoin":"-","oprkind":"b","oprleft":"jsonb","oprname":"-","oprnegate":"0","oprrest":"-","oprresult":"jsonb","oprright":"text"},{"oprcanhash":"f","oprcanmerge":"f","oprcode":"jsonb_delete_path","oprcom":"0","oprjoin":"-","oprkind":"b","oprleft":"jsonb","oprname":"#-","oprnegate":"0","oprrest":"-","oprresult":"jsonb","oprright":"_text"},{"oprcanhash":"f","oprcanmerge":"f","oprcode":"jsonb_exists","oprcom":"0","oprjoin":"matchingjoinsel","oprkind":"b","oprleft":"jsonb","oprname":"?","oprnegate":"0","oprrest":"matchingsel","oprresult":"bool","oprright":"text"},{"oprcanhash":"f","oprcanmerge":"f","oprcode":"jsonb_exists_all","oprcom":"0","oprjoin":"matchingjoinsel","oprkind":"b","oprleft":"jsonb","oprname":"?\u0026","oprnegate":"0","oprrest":"matchingsel","oprresult":"bool","oprright":"_text"},{"oprcanhash":"f","oprcanmerge":"f","oprcode":"jsonb_exists_any","oprcom":"0","oprjoin":"matchingjoinsel","oprkind":"b","oprleft":"jsonb","oprname":"?|","oprnegate":"0","oprrest":"matchingsel","oprresult":"bool","oprright":"_text"},{"oprcanhash":"f","oprcanmerge":"f","oprcode":"jsonb_extract_path","oprcom":"0","oprjoin":"-","oprkind":"b","oprleft":"jsonb","oprname":"#\u003e","oprnegate":"0","oprrest":"-","oprresult":"jsonb","oprright":"_text"},{"oprcanhash":"f","oprcanmerge":"f","oprcode":"jsonb_extract_path_text","oprcom":"0","oprjoin":"-","oprkind":"b","oprleft":"jsonb","oprname":"#\u003e\u003e","oprnegate":"0","oprrest":"-","oprresult":"text","oprright":"_text"},{"oprcanhash":"f","oprcanmerge":"f","oprcode":"jsonb_ge","oprcom":"\u003c=(jsonb,jsonb)","oprjoin":"scalargejoinsel","oprkind":"b","oprleft":"jsonb","oprname":"\u003e=","oprnegate":"\u003c(jsonb,jsonb)","oprrest":"scalargesel","oprresult":"bool","oprright":"jsonb"},{"oprcanhash":"f","oprcanmerge":"f","oprcode":"jsonb_gt","oprcom":"\u003c(jsonb,jsonb)","oprjoin":"scalargtjoinsel","oprkind":"b","oprleft":"jsonb","oprname":"\u003e","oprnegate":"\u003c=(jsonb,jsonb)","oprrest":"scalargtsel","oprresult":"bool","oprright":"jsonb"},{"oprcanhash":"f","oprcanmerge":"f","oprcode":"jsonb_le","oprcom":"\u003e=(jsonb,jsonb)","oprjoin":"scalarlejoinsel","oprkind":"b","oprleft":"jsonb","oprname":"\u003c=","oprnegate":"\u003e(jsonb,jsonb)","oprrest":"scalarlesel","oprresult":"bool","oprright":"jsonb"},{"oprcanhash":"f","oprcanmerge":"f","oprcode":"jsonb_lt","oprcom":"\u003e(jsonb,jsonb)","oprjoin":"scalarltjoinsel","oprkind":"b","oprleft":"jsonb","oprname":"\u003c","oprnegate":"\u003e=(jsonb,jsonb)","oprrest":"scalarltsel","oprresult":"bool","oprright":"jsonb"},{"oprcanhash":"f","oprcanmerge":"f","oprcode":"jsonb_ne","oprcom":"\u003c\u003e(jsonb,jsonb)","oprjoin":"neqjoinsel","oprkind":"b","oprleft":"jsonb","oprname":"\u003c\u003e","oprnegate":"=(jsonb,jsonb)","oprrest":"neqsel","oprresult":"bool","oprright":"jsonb"},{"oprcanhash":"f","oprcanmerge":"f","oprcode":"jsonb_object_field","oprcom":"0","oprjoin":"-","oprkind":"b","oprleft":"jsonb","oprname":"-\u003e","oprnegate":"0","oprrest":"-","oprresult":"jsonb","oprright":"text"},{"oprcanhash":"f","oprcanmerge":"f","oprcode":"jsonb_object_field_text","oprcom":"0","oprjoin":"-","oprkind":"b","oprleft":"jsonb","oprname":"-\u003e\u003e","oprnegate":"0","oprrest":"-","oprresult":"text","oprright":"text"},{"oprcanhash":"f","oprcanmerge":"f","oprcode":"jsonb_path_exists_opr","oprcom":"0","oprjoin":"matchingjoinsel","oprkind":"b","oprleft":"jsonb","oprname":"@?","oprnegate":"0","oprrest":"matchingsel","oprresult":"bool","oprright":"jsonpath"},{"oprcanhash":"f","oprcanmerge":"f","oprcode":"jsonb_path_match_opr","oprcom":"0","oprjoin":"matchingjoinsel","oprkind":"b","oprleft":"jsonb","oprname":"@@","oprnegate":"0","oprrest":"matchingsel","oprresult":"bool","oprright":"jsonpath"},{"oprcanhash":"t","oprcanmerge":"t","oprcode":"jsonb_eq","oprcom":"=(jsonb,jsonb)","oprjoin":"eqjoinsel","oprkind":"b","oprleft":"jsonb","oprname":"=","oprnegate":"\u003c\u003e(jsonb,jsonb)","oprrest":"eqsel","oprresult":"bool","oprright":"jsonb"}],"ranges":[]},"comparison_hash":"5167ae3268a9532c6f69788d56bf0bf567718f7a2371fb05cb4129f653c23ff6","coverage":"source inventory; exact declared input types for operator classes","description":["binary JSON data, decomposed"],"facts":[{"label":"Catalog name","value":"pg_catalog.jsonb"},{"label":"Type OID","value":"3802"},{"label":"Type kind","value":"Base type"},{"label":"Declared length","value":"Variable length (varlena)"},{"label":"Storage strategy","value":"extended"},{"label":"Input function","value":"jsonb_in"},{"label":"Output function","value":"jsonb_out"}],"manual_documentation":"dedicated family chapter","manual_html":"\u003cdiv class=\"sect1\" id=\"DATATYPE-JSON\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch2 class=\"title\"\u003e8.14. JSON Types \u003c/h2\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\n\u003cp\u003eJSON data types are for storing JSON (JavaScript Object Notation) data, as specified in \u003ca class=\"ulink\" href=\"https://datatracker.ietf.org/doc/html/rfc7159\"\u003eRFC 7159\u003c/a\u003e. Such data can also be stored as \u003ccode class=\"type\"\u003etext\u003c/code\u003e, 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 \u003ca class=\"xref\" href=\"/docs/18/functions-json.html\" title=\"9.16. JSON Functions and Operators\"\u003eSection 9.16\u003c/a\u003e.\u003c/p\u003e\n\u003cp\u003e\u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e offers two types for storing JSON data: \u003ccode class=\"type\"\u003ejson\u003c/code\u003e and \u003ccode class=\"type\"\u003ejsonb\u003c/code\u003e. To implement efficient query mechanisms for these data types, \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e also provides the \u003ccode class=\"type\"\u003ejsonpath\u003c/code\u003e data type described in \u003ca class=\"xref\" href=\"/docs/18/datatype-json.html#DATATYPE-JSONPATH\" title=\"8.14.7. jsonpath Type\"\u003eSection 8.14.7\u003c/a\u003e.\u003c/p\u003e\n\u003cp\u003eThe \u003ccode class=\"type\"\u003ejson\u003c/code\u003e and \u003ccode class=\"type\"\u003ejsonb\u003c/code\u003e data types accept \u003cspan class=\"emphasis\"\u003e\u003cem\u003ealmost\u003c/em\u003e\u003c/span\u003e identical sets of values as input. The major practical difference is one of efficiency. The \u003ccode class=\"type\"\u003ejson\u003c/code\u003e data type stores an exact copy of the input text, which processing functions must reparse on each execution; while \u003ccode class=\"type\"\u003ejsonb\u003c/code\u003e 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. \u003ccode class=\"type\"\u003ejsonb\u003c/code\u003e also supports indexing, which can be a significant advantage.\u003c/p\u003e\n\u003cp\u003eBecause the \u003ccode class=\"type\"\u003ejson\u003c/code\u003e 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, \u003ccode class=\"type\"\u003ejsonb\u003c/code\u003e 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.\u003c/p\u003e\n\u003cp\u003eIn general, most applications should prefer to store JSON data as \u003ccode class=\"type\"\u003ejsonb\u003c/code\u003e, unless there are quite specialized needs, such as legacy assumptions about ordering of object keys.\u003c/p\u003e\n\u003cp\u003eRFC 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.\u003c/p\u003e\n\u003cp\u003eRFC 7159 permits JSON strings to contain Unicode escape sequences denoted by \u003ccode class=\"literal\"\u003e\\u\u003cem class=\"replaceable\"\u003e\u003ccode\u003eXXXX\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e. In the input function for the \u003ccode class=\"type\"\u003ejson\u003c/code\u003e type, Unicode escapes are allowed regardless of the database encoding, and are checked only for syntactic correctness (that is, that four hex digits follow \u003ccode class=\"literal\"\u003e\\u\u003c/code\u003e). However, the input function for \u003ccode class=\"type\"\u003ejsonb\u003c/code\u003e is stricter: it disallows Unicode escapes for characters that cannot be represented in the database encoding. The \u003ccode class=\"type\"\u003ejsonb\u003c/code\u003e type also rejects \u003ccode class=\"literal\"\u003e\\u0000\u003c/code\u003e (because that cannot be represented in \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e's \u003ccode class=\"type\"\u003etext\u003c/code\u003e 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.\u003c/p\u003e\n\u003cdiv class=\"note\"\u003e\n\u003ch3 class=\"title\"\u003eNote\u003c/h3\u003e\n\u003cp\u003eMany of the JSON processing functions described in \u003ca class=\"xref\" href=\"/docs/18/functions-json.html\" title=\"9.16. JSON Functions and Operators\"\u003eSection 9.16\u003c/a\u003e 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 \u003ccode class=\"type\"\u003ejson\u003c/code\u003e not \u003ccode class=\"type\"\u003ejsonb\u003c/code\u003e. The fact that the \u003ccode class=\"type\"\u003ejson\u003c/code\u003e 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.\u003c/p\u003e\n\u003c/div\u003e\n\u003cp\u003eWhen converting textual JSON input into \u003ccode class=\"type\"\u003ejsonb\u003c/code\u003e, the primitive types described by RFC 7159 are effectively mapped onto native \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e types, as shown in \u003ca class=\"xref\" href=\"/docs/18/datatype-json.html#JSON-TYPE-MAPPING-TABLE\" title=\"Table 8.23. JSON Primitive Types and Corresponding PostgreSQL Types\"\u003eTable 8.23\u003c/a\u003e. Therefore, there are some minor additional constraints on what constitutes valid \u003ccode class=\"type\"\u003ejsonb\u003c/code\u003e data that do not apply to the \u003ccode class=\"type\"\u003ejson\u003c/code\u003e type, nor to JSON in the abstract, corresponding to limits on what can be represented by the underlying data type. Notably, \u003ccode class=\"type\"\u003ejsonb\u003c/code\u003e will reject numbers that are outside the range of the \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e \u003ccode class=\"type\"\u003enumeric\u003c/code\u003e data type, while \u003ccode class=\"type\"\u003ejson\u003c/code\u003e will not. Such implementation-defined restrictions are permitted by RFC 7159. However, in practice such problems are far more likely to occur in other implementations, as it is common to represent JSON's \u003ccode class=\"type\"\u003enumber\u003c/code\u003e primitive type as IEEE 754 double precision floating point (which RFC 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 \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e should be considered.\u003c/p\u003e\n\u003cp\u003eConversely, 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 \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e types.\u003c/p\u003e\n\u003cdiv class=\"table\" id=\"JSON-TYPE-MAPPING-TABLE\"\u003e\n\u003cp class=\"title\"\u003e\u003cstrong\u003eTable 8.23. JSON Primitive Types and Corresponding \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e Types\u003c/strong\u003e\u003c/p\u003e\n\u003cdiv class=\"table-contents\"\u003e\n\u003ctable class=\"table\"\u003e\n\n\n\n\n\n\u003cthead\u003e\n\u003ctr\u003e\n\u003cth\u003eJSON primitive type\u003c/th\u003e\n\u003cth\u003e\u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e type\u003c/th\u003e\n\u003cth\u003eNotes\u003c/th\u003e\n\u003c/tr\u003e\n\u003c/thead\u003e\n\u003ctbody\u003e\n\u003ctr\u003e\n\u003ctd\u003e\u003ccode class=\"type\"\u003estring\u003c/code\u003e\u003c/td\u003e\n\u003ctd\u003e\u003ccode class=\"type\"\u003etext\u003c/code\u003e\u003c/td\u003e\n\u003ctd\u003e\u003ccode class=\"literal\"\u003e\\u0000\u003c/code\u003e is disallowed, as are Unicode escapes representing characters not available in the database encoding\u003c/td\u003e\n\u003c/tr\u003e\n\u003ctr\u003e\n\u003ctd\u003e\u003ccode class=\"type\"\u003enumber\u003c/code\u003e\u003c/td\u003e\n\u003ctd\u003e\u003ccode class=\"type\"\u003enumeric\u003c/code\u003e\u003c/td\u003e\n\u003ctd\u003e\u003ccode class=\"literal\"\u003eNaN\u003c/code\u003e and \u003ccode class=\"literal\"\u003einfinity\u003c/code\u003e values are disallowed\u003c/td\u003e\n\u003c/tr\u003e\n\u003ctr\u003e\n\u003ctd\u003e\u003ccode class=\"type\"\u003eboolean\u003c/code\u003e\u003c/td\u003e\n\u003ctd\u003e\u003ccode class=\"type\"\u003eboolean\u003c/code\u003e\u003c/td\u003e\n\u003ctd\u003eOnly lowercase \u003ccode class=\"literal\"\u003etrue\u003c/code\u003e and \u003ccode class=\"literal\"\u003efalse\u003c/code\u003e spellings are accepted\u003c/td\u003e\n\u003c/tr\u003e\n\u003ctr\u003e\n\u003ctd\u003e\u003ccode class=\"type\"\u003enull\u003c/code\u003e\u003c/td\u003e\n\u003ctd\u003e(none)\u003c/td\u003e\n\u003ctd\u003eSQL \u003ccode class=\"literal\"\u003eNULL\u003c/code\u003e is a different concept\u003c/td\u003e\n\u003c/tr\u003e\n\u003c/tbody\u003e\n\u003c/table\u003e\n\u003c/div\u003e\n\u003c/div\u003e\u003cbr class=\"table-break\"\u003e\n\u003cdiv class=\"sect2\" id=\"JSON-KEYS-ELEMENTS\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3 class=\"title\"\u003e8.14.1. JSON Input and Output Syntax \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eThe input/output syntax for the JSON data types is as specified in RFC 7159.\u003c/p\u003e\n\u003cp\u003eThe following are all valid \u003ccode class=\"type\"\u003ejson\u003c/code\u003e (or \u003ccode class=\"type\"\u003ejsonb\u003c/code\u003e) expressions:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003e-- Simple scalar/primitive value\n-- Primitive values can be numbers, quoted strings, true, false, or null\nSELECT '5'::json;\n\n-- Array of zero or more elements (elements need not be of same type)\nSELECT '[1, 2, \"foo\", null]'::json;\n\n-- Object containing pairs of keys and values\n-- Note that object keys must always be quoted strings\nSELECT '{\"bar\": \"baz\", \"balance\": 7.77, \"active\": false}'::json;\n\n-- Arrays and objects can be nested arbitrarily\nSELECT '{\"foo\": [true, \"bar\"], \"tags\": {\"a\": 1, \"b\": null}}'::json;\n\u003c/pre\u003e\n\u003cp\u003eAs previously stated, when a JSON value is input and then printed without any additional processing, \u003ccode class=\"type\"\u003ejson\u003c/code\u003e outputs the same text that was input, while \u003ccode class=\"type\"\u003ejsonb\u003c/code\u003e does not preserve semantically-insignificant details such as whitespace. For example, note the differences here:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT '{\"bar\": \"baz\", \"balance\": 7.77, \"active\":false}'::json;\n                      json\n-------------------------------------------------\n {\"bar\": \"baz\", \"balance\": 7.77, \"active\":false}\n(1 row)\n\nSELECT '{\"bar\": \"baz\", \"balance\": 7.77, \"active\":false}'::jsonb;\n                      jsonb\n--------------------------------------------------\n {\"bar\": \"baz\", \"active\": false, \"balance\": 7.77}\n(1 row)\n\u003c/pre\u003e\n\u003cp\u003eOne semantically-insignificant detail worth noting is that in \u003ccode class=\"type\"\u003ejsonb\u003c/code\u003e, numbers will be printed according to the behavior of the underlying \u003ccode class=\"type\"\u003enumeric\u003c/code\u003e type. In practice this means that numbers entered with \u003ccode class=\"literal\"\u003eE\u003c/code\u003e notation will be printed without it, for example:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT '{\"reading\": 1.230e-5}'::json, '{\"reading\": 1.230e-5}'::jsonb;\n         json          |          jsonb\n-----------------------+-------------------------\n {\"reading\": 1.230e-5} | {\"reading\": 0.00001230}\n(1 row)\n\u003c/pre\u003e\n\u003cp\u003eHowever, \u003ccode class=\"type\"\u003ejsonb\u003c/code\u003e will preserve trailing fractional zeroes, as seen in this example, even though those are semantically insignificant for purposes such as equality checks.\u003c/p\u003e\n\u003cp\u003eFor the list of built-in functions and operators available for constructing and processing JSON values, see \u003ca class=\"xref\" href=\"/docs/18/functions-json.html\" title=\"9.16. JSON Functions and Operators\"\u003eSection 9.16\u003c/a\u003e.\u003c/p\u003e\n\u003c/div\u003e\n\u003cdiv class=\"sect2\" id=\"JSON-DOC-DESIGN\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3 class=\"title\"\u003e8.14.2. Designing JSON Documents \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eRepresenting 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 \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003edocuments\u003c/span\u003e”\u003c/span\u003e (datums) in a table.\u003c/p\u003e\n\u003cp\u003eJSON 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.\u003c/p\u003e\n\u003c/div\u003e\n\u003cdiv class=\"sect2\" id=\"JSON-CONTAINMENT\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3 class=\"title\"\u003e8.14.3. \u003ccode class=\"type\"\u003ejsonb\u003c/code\u003e Containment and Existence \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eTesting \u003cem class=\"firstterm\"\u003econtainment\u003c/em\u003e is an important capability of \u003ccode class=\"type\"\u003ejsonb\u003c/code\u003e. There is no parallel set of facilities for the \u003ccode class=\"type\"\u003ejson\u003c/code\u003e type. Containment tests whether one \u003ccode class=\"type\"\u003ejsonb\u003c/code\u003e document has contained within it another one. These examples return true except as noted:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003e-- Simple scalar/primitive values contain only the identical value:\nSELECT '\"foo\"'::jsonb @\u0026gt; '\"foo\"'::jsonb;\n\n-- The array on the right side is contained within the one on the left:\nSELECT '[1, 2, 3]'::jsonb @\u0026gt; '[1, 3]'::jsonb;\n\n-- Order of array elements is not significant, so this is also true:\nSELECT '[1, 2, 3]'::jsonb @\u0026gt; '[3, 1]'::jsonb;\n\n-- Duplicate array elements don't matter either:\nSELECT '[1, 2, 3]'::jsonb @\u0026gt; '[1, 2, 2]'::jsonb;\n\n-- The object with a single pair on the right side is contained\n-- within the object on the left side:\nSELECT '{\"product\": \"PostgreSQL\", \"version\": 9.4, \"jsonb\": true}'::jsonb @\u0026gt; '{\"version\": 9.4}'::jsonb;\n\n-- The array on the right side is \u003cspan class=\"emphasis\"\u003e\u003cstrong\u003enot\u003c/strong\u003e\u003c/span\u003e considered contained within the\n-- array on the left, even though a similar array is nested within it:\nSELECT '[1, 2, [1, 3]]'::jsonb @\u0026gt; '[1, 3]'::jsonb;  -- yields false\n\n-- But with a layer of nesting, it is contained:\nSELECT '[1, 2, [1, 3]]'::jsonb @\u0026gt; '[[1, 3]]'::jsonb;\n\n-- Similarly, containment is not reported here:\nSELECT '{\"foo\": {\"bar\": \"baz\"}}'::jsonb @\u0026gt; '{\"bar\": \"baz\"}'::jsonb;  -- yields false\n\n-- A top-level key and an empty object is contained:\nSELECT '{\"foo\": {\"bar\": \"baz\"}}'::jsonb @\u0026gt; '{\"foo\": {}}'::jsonb;\n\u003c/pre\u003e\n\u003cp\u003eThe 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.\u003c/p\u003e\n\u003cp\u003eAs a special exception to the general principle that the structures must match, an array may contain a primitive value:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003e-- This array contains the primitive string value:\nSELECT '[\"foo\", \"bar\"]'::jsonb @\u0026gt; '\"bar\"'::jsonb;\n\n-- This exception is not reciprocal -- non-containment is reported here:\nSELECT '\"bar\"'::jsonb @\u0026gt; '[\"bar\"]'::jsonb;  -- yields false\n\u003c/pre\u003e\n\u003cp\u003e\u003ccode class=\"type\"\u003ejsonb\u003c/code\u003e also has an \u003cem class=\"firstterm\"\u003eexistence\u003c/em\u003e operator, which is a variation on the theme of containment: it tests whether a string (given as a \u003ccode class=\"type\"\u003etext\u003c/code\u003e value) appears as an object key or array element at the top level of the \u003ccode class=\"type\"\u003ejsonb\u003c/code\u003e value. These examples return true except as noted:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003e-- String exists as array element:\nSELECT '[\"foo\", \"bar\", \"baz\"]'::jsonb ? 'bar';\n\n-- String exists as object key:\nSELECT '{\"foo\": \"bar\"}'::jsonb ? 'foo';\n\n-- Object values are not considered:\nSELECT '{\"foo\": \"bar\"}'::jsonb ? 'bar';  -- yields false\n\n-- As with containment, existence must match at the top level:\nSELECT '{\"foo\": {\"bar\": \"baz\"}}'::jsonb ? 'bar'; -- yields false\n\n-- A string is considered to exist if it matches a primitive JSON string:\nSELECT '\"foo\"'::jsonb ? 'foo';\n\u003c/pre\u003e\n\u003cp\u003eJSON 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.\u003c/p\u003e\n\u003cdiv class=\"tip\"\u003e\n\u003ch3 class=\"title\"\u003eTip\u003c/h3\u003e\n\u003cp\u003eBecause JSON containment is nested, an appropriate query can skip explicit selection of sub-objects. As an example, suppose that we have a \u003ccode class=\"structfield\"\u003edoc\u003c/code\u003e column containing objects at the top level, with most objects containing \u003ccode class=\"literal\"\u003etags\u003c/code\u003e fields that contain arrays of sub-objects. This query finds entries in which sub-objects containing both \u003ccode class=\"literal\"\u003e\"term\":\"paris\"\u003c/code\u003e and \u003ccode class=\"literal\"\u003e\"term\":\"food\"\u003c/code\u003e appear, while ignoring any such keys outside the \u003ccode class=\"literal\"\u003etags\u003c/code\u003e array:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT doc-\u0026gt;'site_name' FROM websites\n  WHERE doc @\u0026gt; '{\"tags\":[{\"term\":\"paris\"}, {\"term\":\"food\"}]}';\n\u003c/pre\u003e\n\u003cp\u003eOne could accomplish the same thing with, say,\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT doc-\u0026gt;'site_name' FROM websites\n  WHERE doc-\u0026gt;'tags' @\u0026gt; '[{\"term\":\"paris\"}, {\"term\":\"food\"}]';\n\u003c/pre\u003e\n\u003cp\u003ebut that approach is less flexible, and often less efficient as well.\u003c/p\u003e\n\u003cp\u003eOn 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.\u003c/p\u003e\n\u003c/div\u003e\n\u003cp\u003eThe various containment and existence operators, along with all other JSON operators and functions are documented in \u003ca class=\"xref\" href=\"/docs/18/functions-json.html\" title=\"9.16. JSON Functions and Operators\"\u003eSection 9.16\u003c/a\u003e.\u003c/p\u003e\n\u003c/div\u003e\n\u003cdiv class=\"sect2\" id=\"JSON-INDEXING\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3 class=\"title\"\u003e8.14.4. \u003ccode class=\"type\"\u003ejsonb\u003c/code\u003e Indexing \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eGIN indexes can be used to efficiently search for keys or key/value pairs occurring within a large number of \u003ccode class=\"type\"\u003ejsonb\u003c/code\u003e documents (datums). Two GIN \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003eoperator classes\u003c/span\u003e”\u003c/span\u003e are provided, offering different performance and flexibility trade-offs.\u003c/p\u003e\n\u003cp\u003eThe default GIN operator class for \u003ccode class=\"type\"\u003ejsonb\u003c/code\u003e supports queries with the key-exists operators \u003ccode class=\"literal\"\u003e?\u003c/code\u003e, \u003ccode class=\"literal\"\u003e?|\u003c/code\u003e and \u003ccode class=\"literal\"\u003e?\u0026amp;\u003c/code\u003e, the containment operator \u003ccode class=\"literal\"\u003e@\u0026gt;\u003c/code\u003e, and the \u003ccode class=\"type\"\u003ejsonpath\u003c/code\u003e match operators \u003ccode class=\"literal\"\u003e@?\u003c/code\u003e and \u003ccode class=\"literal\"\u003e@@\u003c/code\u003e. (For details of the semantics that these operators implement, see \u003ca class=\"xref\" href=\"/docs/18/functions-json.html#FUNCTIONS-JSONB-OP-TABLE\" title=\"Table 9.48. Additional jsonb Operators\"\u003eTable 9.48\u003c/a\u003e.) An example of creating an index with this operator class is:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eCREATE INDEX idxgin ON api USING GIN (jdoc);\n\u003c/pre\u003e\n\u003cp\u003eThe non-default GIN operator class \u003ccode class=\"literal\"\u003ejsonb_path_ops\u003c/code\u003e does not support the key-exists operators, but it does support \u003ccode class=\"literal\"\u003e@\u0026gt;\u003c/code\u003e, \u003ccode class=\"literal\"\u003e@?\u003c/code\u003e and \u003ccode class=\"literal\"\u003e@@\u003c/code\u003e. An example of creating an index with this operator class is:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eCREATE INDEX idxginp ON api USING GIN (jdoc jsonb_path_ops);\n\u003c/pre\u003e\n\u003cp\u003eConsider 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:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003e{\n    \"guid\": \"9c36adc1-7fb5-4d5b-83b4-90356a46061a\",\n    \"name\": \"Angela Barton\",\n    \"is_active\": true,\n    \"company\": \"Magnafone\",\n    \"address\": \"178 Howard Place, Gulf, Washington, 702\",\n    \"registered\": \"2009-11-07T08:53:22 +08:00\",\n    \"latitude\": 19.793713,\n    \"longitude\": 86.513373,\n    \"tags\": [\n        \"enim\",\n        \"aliquip\",\n        \"qui\"\n    ]\n}\n\u003c/pre\u003e\n\u003cp\u003eWe store these documents in a table named \u003ccode class=\"structname\"\u003eapi\u003c/code\u003e, in a \u003ccode class=\"type\"\u003ejsonb\u003c/code\u003e column named \u003ccode class=\"structfield\"\u003ejdoc\u003c/code\u003e. If a GIN index is created on this column, queries like the following can make use of the index:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003e-- Find documents in which the key \"company\" has value \"Magnafone\"\nSELECT jdoc-\u0026gt;'guid', jdoc-\u0026gt;'name' FROM api WHERE jdoc @\u0026gt; '{\"company\": \"Magnafone\"}';\n\u003c/pre\u003e\n\u003cp\u003eHowever, the index could not be used for queries like the following, because though the operator \u003ccode class=\"literal\"\u003e?\u003c/code\u003e is indexable, it is not applied directly to the indexed column \u003ccode class=\"structfield\"\u003ejdoc\u003c/code\u003e:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003e-- Find documents in which the key \"tags\" contains key or array element \"qui\"\nSELECT jdoc-\u0026gt;'guid', jdoc-\u0026gt;'name' FROM api WHERE jdoc -\u0026gt; 'tags' ? 'qui';\n\u003c/pre\u003e\n\u003cp\u003eStill, with appropriate use of expression indexes, the above query can use an index. If querying for particular items within the \u003ccode class=\"literal\"\u003e\"tags\"\u003c/code\u003e key is common, defining an index like this may be worthwhile:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eCREATE INDEX idxgintags ON api USING GIN ((jdoc -\u0026gt; 'tags'));\n\u003c/pre\u003e\n\u003cp\u003eNow, the \u003ccode class=\"literal\"\u003eWHERE\u003c/code\u003e clause \u003ccode class=\"literal\"\u003ejdoc -\u0026gt; 'tags' ? 'qui'\u003c/code\u003e will be recognized as an application of the indexable operator \u003ccode class=\"literal\"\u003e?\u003c/code\u003e to the indexed expression \u003ccode class=\"literal\"\u003ejdoc -\u0026gt; 'tags'\u003c/code\u003e. (More information on expression indexes can be found in \u003ca class=\"xref\" href=\"/docs/18/indexes-expressional.html\" title=\"11.7. Indexes on Expressions\"\u003eSection 11.7\u003c/a\u003e.)\u003c/p\u003e\n\u003cp\u003eAnother approach to querying is to exploit containment, for example:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003e-- Find documents in which the key \"tags\" contains array element \"qui\"\nSELECT jdoc-\u0026gt;'guid', jdoc-\u0026gt;'name' FROM api WHERE jdoc @\u0026gt; '{\"tags\": [\"qui\"]}';\n\u003c/pre\u003e\n\u003cp\u003eA simple GIN index on the \u003ccode class=\"structfield\"\u003ejdoc\u003c/code\u003e column can support this query. But note that such an index will store copies of every key and value in the \u003ccode class=\"structfield\"\u003ejdoc\u003c/code\u003e column, whereas the expression index of the previous example stores only data found under the \u003ccode class=\"literal\"\u003etags\u003c/code\u003e 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.\u003c/p\u003e\n\u003cp\u003eGIN indexes also support the \u003ccode class=\"literal\"\u003e@?\u003c/code\u003e and \u003ccode class=\"literal\"\u003e@@\u003c/code\u003e operators, which perform \u003ccode class=\"type\"\u003ejsonpath\u003c/code\u003e matching. Examples are\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT jdoc-\u0026gt;'guid', jdoc-\u0026gt;'name' FROM api WHERE jdoc @? '$.tags[*] ? (@ == \"qui\")';\n\u003c/pre\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT jdoc-\u0026gt;'guid', jdoc-\u0026gt;'name' FROM api WHERE jdoc @@ '$.tags[*] == \"qui\"';\n\u003c/pre\u003e\n\u003cp\u003eFor these operators, a GIN index extracts clauses of the form \u003ccode class=\"literal\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eaccessors_chain\u003c/code\u003e\u003c/em\u003e == \u003cem class=\"replaceable\"\u003e\u003ccode\u003econstant\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e out of the \u003ccode class=\"type\"\u003ejsonpath\u003c/code\u003e pattern, and does the index search based on the keys and values mentioned in these clauses. The accessors chain may include \u003ccode class=\"literal\"\u003e.\u003cem class=\"replaceable\"\u003e\u003ccode\u003ekey\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e, \u003ccode class=\"literal\"\u003e[*]\u003c/code\u003e, and \u003ccode class=\"literal\"\u003e[\u003cem class=\"replaceable\"\u003e\u003ccode\u003eindex\u003c/code\u003e\u003c/em\u003e]\u003c/code\u003e accessors. The \u003ccode class=\"literal\"\u003ejsonb_ops\u003c/code\u003e operator class also supports \u003ccode class=\"literal\"\u003e.*\u003c/code\u003e and \u003ccode class=\"literal\"\u003e.**\u003c/code\u003e accessors, but the \u003ccode class=\"literal\"\u003ejsonb_path_ops\u003c/code\u003e operator class does not.\u003c/p\u003e\n\u003cp\u003eAlthough the \u003ccode class=\"literal\"\u003ejsonb_path_ops\u003c/code\u003e operator class supports only queries with the \u003ccode class=\"literal\"\u003e@\u0026gt;\u003c/code\u003e, \u003ccode class=\"literal\"\u003e@?\u003c/code\u003e and \u003ccode class=\"literal\"\u003e@@\u003c/code\u003e operators, it has notable performance advantages over the default operator class \u003ccode class=\"literal\"\u003ejsonb_ops\u003c/code\u003e. A \u003ccode class=\"literal\"\u003ejsonb_path_ops\u003c/code\u003e index is usually much smaller than a \u003ccode class=\"literal\"\u003ejsonb_ops\u003c/code\u003e 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.\u003c/p\u003e\n\u003cp\u003eThe technical difference between a \u003ccode class=\"literal\"\u003ejsonb_ops\u003c/code\u003e and a \u003ccode class=\"literal\"\u003ejsonb_path_ops\u003c/code\u003e 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. \u003ca class=\"footnote\" href=\"/docs/18/datatype-json.html#ftn.id-1.5.7.22.18.9.3\"\u003e\u003csup class=\"footnote\" id=\"id-1.5.7.22.18.9.3\"\u003e[7]\u003c/sup\u003e\u003c/a\u003e Basically, each \u003ccode class=\"literal\"\u003ejsonb_path_ops\u003c/code\u003e index item is a hash of the value and the key(s) leading to it; for example to index \u003ccode class=\"literal\"\u003e{\"foo\": {\"bar\": \"baz\"}}\u003c/code\u003e, a single index item would be created incorporating all three of \u003ccode class=\"literal\"\u003efoo\u003c/code\u003e, \u003ccode class=\"literal\"\u003ebar\u003c/code\u003e, and \u003ccode class=\"literal\"\u003ebaz\u003c/code\u003e 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 \u003ccode class=\"literal\"\u003efoo\u003c/code\u003e appears as a key. On the other hand, a \u003ccode class=\"literal\"\u003ejsonb_ops\u003c/code\u003e index would create three index items representing \u003ccode class=\"literal\"\u003efoo\u003c/code\u003e, \u003ccode class=\"literal\"\u003ebar\u003c/code\u003e, and \u003ccode class=\"literal\"\u003ebaz\u003c/code\u003e 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 \u003ccode class=\"literal\"\u003ejsonb_path_ops\u003c/code\u003e search, especially if there are a very large number of rows containing any single one of the three index items.\u003c/p\u003e\n\u003cp\u003eA disadvantage of the \u003ccode class=\"literal\"\u003ejsonb_path_ops\u003c/code\u003e approach is that it produces no index entries for JSON structures not containing any values, such as \u003ccode class=\"literal\"\u003e{\"a\": {}}\u003c/code\u003e. If a search for documents containing such a structure is requested, it will require a full-index scan, which is quite slow. \u003ccode class=\"literal\"\u003ejsonb_path_ops\u003c/code\u003e is therefore ill-suited for applications that often perform such searches.\u003c/p\u003e\n\u003cp\u003e\u003ccode class=\"type\"\u003ejsonb\u003c/code\u003e also supports \u003ccode class=\"literal\"\u003ebtree\u003c/code\u003e and \u003ccode class=\"literal\"\u003ehash\u003c/code\u003e indexes. These are usually useful only if it's important to check equality of complete JSON documents. The \u003ccode class=\"literal\"\u003ebtree\u003c/code\u003e ordering for \u003ccode class=\"type\"\u003ejsonb\u003c/code\u003e datums is seldom of great interest, but for completeness it is:\u003c/p\u003e\n\u003cpre class=\"synopsis\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eObject\u003c/code\u003e\u003c/em\u003e \u0026gt; \u003cem class=\"replaceable\"\u003e\u003ccode\u003eArray\u003c/code\u003e\u003c/em\u003e \u0026gt; \u003cem class=\"replaceable\"\u003e\u003ccode\u003eBoolean\u003c/code\u003e\u003c/em\u003e \u0026gt; \u003cem class=\"replaceable\"\u003e\u003ccode\u003eNumber\u003c/code\u003e\u003c/em\u003e \u0026gt; \u003cem class=\"replaceable\"\u003e\u003ccode\u003eString\u003c/code\u003e\u003c/em\u003e \u0026gt; \u003cem class=\"replaceable\"\u003e\u003ccode\u003enull\u003c/code\u003e\u003c/em\u003e\n\n\u003cem class=\"replaceable\"\u003e\u003ccode\u003eObject with n pairs\u003c/code\u003e\u003c/em\u003e \u0026gt; \u003cem class=\"replaceable\"\u003e\u003ccode\u003eobject with n - 1 pairs\u003c/code\u003e\u003c/em\u003e\n\n\u003cem class=\"replaceable\"\u003e\u003ccode\u003eArray with n elements\u003c/code\u003e\u003c/em\u003e \u0026gt; \u003cem class=\"replaceable\"\u003e\u003ccode\u003earray with n - 1 elements\u003c/code\u003e\u003c/em\u003e\n\u003c/pre\u003e\n\u003cp\u003ewith the exception that (for historical reasons) an empty top level array sorts less than \u003cem class=\"replaceable\"\u003e\u003ccode\u003enull\u003c/code\u003e\u003c/em\u003e. Objects with equal numbers of pairs are compared in the order:\u003c/p\u003e\n\u003cpre class=\"synopsis\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ekey-1\u003c/code\u003e\u003c/em\u003e, \u003cem class=\"replaceable\"\u003e\u003ccode\u003evalue-1\u003c/code\u003e\u003c/em\u003e, \u003cem class=\"replaceable\"\u003e\u003ccode\u003ekey-2\u003c/code\u003e\u003c/em\u003e ...\n\u003c/pre\u003e\n\u003cp\u003eNote 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:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003e{ \"aa\": 1, \"c\": 1} \u0026gt; {\"b\": 1, \"d\": 1}\n\u003c/pre\u003e\n\u003cp\u003eSimilarly, arrays with equal numbers of elements are compared in the order:\u003c/p\u003e\n\u003cpre class=\"synopsis\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eelement-1\u003c/code\u003e\u003c/em\u003e, \u003cem class=\"replaceable\"\u003e\u003ccode\u003eelement-2\u003c/code\u003e\u003c/em\u003e ...\n\u003c/pre\u003e\n\u003cp\u003ePrimitive JSON values are compared using the same comparison rules as for the underlying \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e data type. Strings are compared using the default database collation.\u003c/p\u003e\n\u003c/div\u003e\n\u003cdiv class=\"sect2\" id=\"JSONB-SUBSCRIPTING\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3 class=\"title\"\u003e8.14.5. \u003ccode class=\"type\"\u003ejsonb\u003c/code\u003e Subscripting \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eThe \u003ccode class=\"type\"\u003ejsonb\u003c/code\u003e 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 \u003ccode class=\"literal\"\u003epath\u003c/code\u003e argument in the \u003ccode class=\"literal\"\u003ejsonb_set\u003c/code\u003e function. If a \u003ccode class=\"type\"\u003ejsonb\u003c/code\u003e 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.\u003c/p\u003e\n\u003cp\u003e\u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e statements may use subscripting in the \u003ccode class=\"literal\"\u003eSET\u003c/code\u003e clause to modify \u003ccode class=\"type\"\u003ejsonb\u003c/code\u003e values. Subscript paths must be traversable for all affected values insofar as they exist. For instance, the path \u003ccode class=\"literal\"\u003eval['a']['b']['c']\u003c/code\u003e can be traversed all the way to \u003ccode class=\"literal\"\u003ec\u003c/code\u003e if every \u003ccode class=\"literal\"\u003eval\u003c/code\u003e, \u003ccode class=\"literal\"\u003eval['a']\u003c/code\u003e, and \u003ccode class=\"literal\"\u003eval['a']['b']\u003c/code\u003e is an object. If any \u003ccode class=\"literal\"\u003eval['a']\u003c/code\u003e or \u003ccode class=\"literal\"\u003eval['a']['b']\u003c/code\u003e is not defined, it will be created as an empty object and filled as necessary. However, if any \u003ccode class=\"literal\"\u003eval\u003c/code\u003e itself or one of the intermediary values is defined as a non-object such as a string, number, or \u003ccode class=\"literal\"\u003ejsonb\u003c/code\u003e \u003ccode class=\"literal\"\u003enull\u003c/code\u003e, traversal cannot proceed so an error is raised and the transaction aborted.\u003c/p\u003e\n\u003cp\u003eAn example of subscripting syntax:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003e\n-- Extract object value by key\nSELECT ('{\"a\": 1}'::jsonb)['a'];\n\n-- Extract nested object value by key path\nSELECT ('{\"a\": {\"b\": {\"c\": 1}}}'::jsonb)['a']['b']['c'];\n\n-- Extract array element by index\nSELECT ('[1, \"2\", null]'::jsonb)[1];\n\n-- Update object value by key. Note the quotes around '1': the assigned\n-- value must be of the jsonb type as well\nUPDATE table_name SET jsonb_field['key'] = '1';\n\n-- This will raise an error if any record's jsonb_field['a']['b'] is something\n-- other than an object. For example, the value {\"a\": 1} has a numeric value\n-- of the key 'a'.\nUPDATE table_name SET jsonb_field['a']['b']['c'] = '1';\n\n-- Filter records using a WHERE clause with subscripting. Since the result of\n-- subscripting is jsonb, the value we compare it against must also be jsonb.\n-- The double quotes make \"value\" also a valid jsonb string.\nSELECT * FROM table_name WHERE jsonb_field['key'] = '\"value\"';\n\u003c/pre\u003e\n\u003cp\u003e\u003ccode class=\"type\"\u003ejsonb\u003c/code\u003e assignment via subscripting handles a few edge cases differently from \u003ccode class=\"literal\"\u003ejsonb_set\u003c/code\u003e. When a source \u003ccode class=\"type\"\u003ejsonb\u003c/code\u003e value is \u003ccode class=\"literal\"\u003eNULL\u003c/code\u003e, assignment via subscripting will proceed as if it was an empty JSON value of the type (object or array) implied by the subscript key:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003e-- Where jsonb_field was NULL, it is now {\"a\": 1}\nUPDATE table_name SET jsonb_field['a'] = '1';\n\n-- Where jsonb_field was NULL, it is now [1]\nUPDATE table_name SET jsonb_field[0] = '1';\n\u003c/pre\u003e\n\u003cp\u003eIf an index is specified for an array containing too few elements, \u003ccode class=\"literal\"\u003eNULL\u003c/code\u003e elements will be appended until the index is reachable and the value can be set.\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003e-- Where jsonb_field was [], it is now [null, null, 2];\n-- where jsonb_field was [0], it is now [0, null, 2]\nUPDATE table_name SET jsonb_field[2] = '2';\n\u003c/pre\u003e\n\u003cp\u003eA \u003ccode class=\"type\"\u003ejsonb\u003c/code\u003e 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 \u003ccode class=\"literal\"\u003enull\u003c/code\u003e-padded, as specified by the subscript path until the assigned value can be placed.\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003e-- Where jsonb_field was {}, it is now {\"a\": [{\"b\": 1}]}\nUPDATE table_name SET jsonb_field['a'][0]['b'] = '1';\n\n-- Where jsonb_field was [], it is now [null, {\"a\": 1}]\nUPDATE table_name SET jsonb_field[1]['a'] = '1';\n\u003c/pre\u003e\n\u003c/div\u003e\n\u003cdiv class=\"sect2\" id=\"DATATYPE-JSON-TRANSFORMS\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3 class=\"title\"\u003e8.14.6. Transforms \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eAdditional extensions are available that implement transforms for the \u003ccode class=\"type\"\u003ejsonb\u003c/code\u003e type for different procedural languages.\u003c/p\u003e\n\u003cp\u003eThe extensions for PL/Perl are called \u003ccode class=\"literal\"\u003ejsonb_plperl\u003c/code\u003e and \u003ccode class=\"literal\"\u003ejsonb_plperlu\u003c/code\u003e. If you use them, \u003ccode class=\"type\"\u003ejsonb\u003c/code\u003e values are mapped to Perl arrays, hashes, and scalars, as appropriate.\u003c/p\u003e\n\u003cp\u003eThe extension for PL/Python is called \u003ccode class=\"literal\"\u003ejsonb_plpython3u\u003c/code\u003e. If you use it, \u003ccode class=\"type\"\u003ejsonb\u003c/code\u003e values are mapped to Python dictionaries, lists, and scalars, as appropriate.\u003c/p\u003e\n\u003cp\u003eOf these extensions, \u003ccode class=\"literal\"\u003ejsonb_plperl\u003c/code\u003e is considered \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003etrusted\u003c/span\u003e”\u003c/span\u003e, that is, it can be installed by non-superusers who have \u003ccode class=\"literal\"\u003eCREATE\u003c/code\u003e privilege on the current database. The rest require superuser privilege to install.\u003c/p\u003e\n\u003c/div\u003e\n\u003cdiv class=\"sect2\" id=\"DATATYPE-JSONPATH\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3 class=\"title\"\u003e8.14.7. jsonpath Type \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eThe \u003ccode class=\"type\"\u003ejsonpath\u003c/code\u003e type implements support for the SQL/JSON path language in \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e 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.\u003c/p\u003e\n\u003cp\u003eThe 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:\u003c/p\u003e\n\u003cdiv class=\"itemizedlist\"\u003e\n\u003cul class=\"itemizedlist\"\u003e\n\u003cli class=\"listitem\"\u003e\n\u003cp\u003eDot (\u003ccode class=\"literal\"\u003e.\u003c/code\u003e) is used for member access.\u003c/p\u003e\n\u003c/li\u003e\n\u003cli class=\"listitem\"\u003e\n\u003cp\u003eSquare brackets (\u003ccode class=\"literal\"\u003e[]\u003c/code\u003e) are used for array access.\u003c/p\u003e\n\u003c/li\u003e\n\u003cli class=\"listitem\"\u003e\n\u003cp\u003eSQL/JSON arrays are 0-relative, unlike regular SQL arrays that start from 1.\u003c/p\u003e\n\u003c/li\u003e\n\u003c/ul\u003e\n\u003c/div\u003e\n\u003cp\u003eNumeric literals in SQL/JSON path expressions follow JavaScript rules, which are different from both SQL and JSON in some minor details. For example, SQL/JSON path allows \u003ccode class=\"literal\"\u003e.1\u003c/code\u003e and \u003ccode class=\"literal\"\u003e1.\u003c/code\u003e, which are invalid in JSON. Non-decimal integer literals and underscore separators are supported, for example, \u003ccode class=\"literal\"\u003e1_000_000\u003c/code\u003e, \u003ccode class=\"literal\"\u003e0x1EEE_FFFF\u003c/code\u003e, \u003ccode class=\"literal\"\u003e0o273\u003c/code\u003e, \u003ccode class=\"literal\"\u003e0b100101\u003c/code\u003e. In SQL/JSON path (and in JavaScript, but not in SQL proper), there must not be an underscore separator directly after the radix prefix.\u003c/p\u003e\n\u003cp\u003eAn 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 \u003ca class=\"xref\" href=\"/docs/18/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS\" title=\"4.1.2.1. String Constants\"\u003eSection 4.1.2.1\u003c/a\u003e). 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 \u003ccode class=\"literal\"\u003e\\\"\u003c/code\u003e, and to write a backslash itself, you must write \u003ccode class=\"literal\"\u003e\\\\\u003c/code\u003e. Other special backslash sequences include those recognized in JavaScript strings: \u003ccode class=\"literal\"\u003e\\b\u003c/code\u003e, \u003ccode class=\"literal\"\u003e\\f\u003c/code\u003e, \u003ccode class=\"literal\"\u003e\\n\u003c/code\u003e, \u003ccode class=\"literal\"\u003e\\r\u003c/code\u003e, \u003ccode class=\"literal\"\u003e\\t\u003c/code\u003e, \u003ccode class=\"literal\"\u003e\\v\u003c/code\u003e for various ASCII control characters, \u003ccode class=\"literal\"\u003e\\x\u003cem class=\"replaceable\"\u003e\u003ccode\u003eNN\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e for a character code written with only two hex digits, \u003ccode class=\"literal\"\u003e\\u\u003cem class=\"replaceable\"\u003e\u003ccode\u003eNNNN\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e for a Unicode character identified by its 4-hex-digit code point, and \u003ccode class=\"literal\"\u003e\\u{\u003cem class=\"replaceable\"\u003e\u003ccode\u003eN...\u003c/code\u003e\u003c/em\u003e}\u003c/code\u003e for a Unicode character code point written with 1 to 6 hex digits.\u003c/p\u003e\n\u003cp\u003eA path expression consists of a sequence of path elements, which can be any of the following:\u003c/p\u003e\n\u003cdiv class=\"itemizedlist\"\u003e\n\u003cul class=\"itemizedlist\"\u003e\n\u003cli class=\"listitem\"\u003e\n\u003cp\u003ePath literals of JSON primitive types: Unicode text, numeric, true, false, or null.\u003c/p\u003e\n\u003c/li\u003e\n\u003cli class=\"listitem\"\u003e\n\u003cp\u003ePath variables listed in \u003ca class=\"xref\" href=\"/docs/18/datatype-json.html#TYPE-JSONPATH-VARIABLES\" title=\"Table 8.24. jsonpath Variables\"\u003eTable 8.24\u003c/a\u003e.\u003c/p\u003e\n\u003c/li\u003e\n\u003cli class=\"listitem\"\u003e\n\u003cp\u003eAccessor operators listed in \u003ca class=\"xref\" href=\"/docs/18/datatype-json.html#TYPE-JSONPATH-ACCESSORS\" title=\"Table 8.25. jsonpath Accessors\"\u003eTable 8.25\u003c/a\u003e.\u003c/p\u003e\n\u003c/li\u003e\n\u003cli class=\"listitem\"\u003e\n\u003cp\u003e\u003ccode class=\"type\"\u003ejsonpath\u003c/code\u003e operators and methods listed in \u003ca class=\"xref\" href=\"/docs/18/functions-json.html#FUNCTIONS-SQLJSON-PATH-OPERATORS\" title=\"9.16.2.3. SQL/JSON Path Operators and Methods\"\u003eSection 9.16.2.3\u003c/a\u003e.\u003c/p\u003e\n\u003c/li\u003e\n\u003cli class=\"listitem\"\u003e\n\u003cp\u003eParentheses, which can be used to provide filter expressions or define the order of path evaluation.\u003c/p\u003e\n\u003c/li\u003e\n\u003c/ul\u003e\n\u003c/div\u003e\n\u003cp\u003eFor details on using \u003ccode class=\"type\"\u003ejsonpath\u003c/code\u003e expressions with SQL/JSON query functions, see \u003ca class=\"xref\" href=\"/docs/18/functions-json.html#FUNCTIONS-SQLJSON-PATH\" title=\"9.16.2. The SQL/JSON Path Language\"\u003eSection 9.16.2\u003c/a\u003e.\u003c/p\u003e\n\u003cdiv class=\"table\" id=\"TYPE-JSONPATH-VARIABLES\"\u003e\n\u003cp class=\"title\"\u003e\u003cstrong\u003eTable 8.24. \u003ccode class=\"type\"\u003ejsonpath\u003c/code\u003e Variables\u003c/strong\u003e\u003c/p\u003e\n\u003cdiv class=\"table-contents\"\u003e\n\u003ctable class=\"table\"\u003e\n\n\n\n\n\u003cthead\u003e\n\u003ctr\u003e\n\u003cth\u003eVariable\u003c/th\u003e\n\u003cth\u003eDescription\u003c/th\u003e\n\u003c/tr\u003e\n\u003c/thead\u003e\n\u003ctbody\u003e\n\u003ctr\u003e\n\u003ctd\u003e\u003ccode class=\"literal\"\u003e$\u003c/code\u003e\u003c/td\u003e\n\u003ctd\u003eA variable representing the JSON value being queried (the \u003cem class=\"firstterm\"\u003econtext item\u003c/em\u003e).\u003c/td\u003e\n\u003c/tr\u003e\n\u003ctr\u003e\n\u003ctd\u003e\u003ccode class=\"literal\"\u003e$varname\u003c/code\u003e\u003c/td\u003e\n\u003ctd\u003eA named variable. Its value can be set by the parameter \u003cem class=\"parameter\"\u003e\u003ccode\u003evars\u003c/code\u003e\u003c/em\u003e of several JSON processing functions; see \u003ca class=\"xref\" href=\"/docs/18/functions-json.html#FUNCTIONS-JSON-PROCESSING-TABLE\" title=\"Table 9.51. JSON Processing Functions\"\u003eTable 9.51\u003c/a\u003e for details.\u003c/td\u003e\n\u003c/tr\u003e\n\u003ctr\u003e\n\u003ctd\u003e\u003ccode class=\"literal\"\u003e@\u003c/code\u003e\u003c/td\u003e\n\u003ctd\u003eA variable representing the result of path evaluation in filter expressions.\u003c/td\u003e\n\u003c/tr\u003e\n\u003c/tbody\u003e\n\u003c/table\u003e\n\u003c/div\u003e\n\u003c/div\u003e\u003cbr class=\"table-break\"\u003e\n\u003cdiv class=\"table\" id=\"TYPE-JSONPATH-ACCESSORS\"\u003e\n\u003cp class=\"title\"\u003e\u003cstrong\u003eTable 8.25. \u003ccode class=\"type\"\u003ejsonpath\u003c/code\u003e Accessors\u003c/strong\u003e\u003c/p\u003e\n\u003cdiv class=\"table-contents\"\u003e\n\u003ctable class=\"table\"\u003e\n\n\n\n\n\u003cthead\u003e\n\u003ctr\u003e\n\u003cth\u003eAccessor Operator\u003c/th\u003e\n\u003cth\u003eDescription\u003c/th\u003e\n\u003c/tr\u003e\n\u003c/thead\u003e\n\u003ctbody\u003e\n\u003ctr\u003e\n\u003ctd\u003e\n\u003cp\u003e\u003ccode class=\"literal\"\u003e.\u003cem class=\"replaceable\"\u003e\u003ccode\u003ekey\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e\u003c/p\u003e\n\u003cp\u003e\u003ccode class=\"literal\"\u003e.\"$\u003cem class=\"replaceable\"\u003e\u003ccode\u003evarname\u003c/code\u003e\u003c/em\u003e\"\u003c/code\u003e\u003c/p\u003e\n\u003c/td\u003e\n\u003ctd\u003e\n\u003cp\u003eMember accessor that returns an object member with the specified key. If the key name matches some named variable starting with \u003ccode class=\"literal\"\u003e$\u003c/code\u003e or does not meet the JavaScript rules for an identifier, it must be enclosed in double quotes to make it a string literal.\u003c/p\u003e\n\u003c/td\u003e\n\u003c/tr\u003e\n\u003ctr\u003e\n\u003ctd\u003e\n\u003cp\u003e\u003ccode class=\"literal\"\u003e.*\u003c/code\u003e\u003c/p\u003e\n\u003c/td\u003e\n\u003ctd\u003e\n\u003cp\u003eWildcard member accessor that returns the values of all members located at the top level of the current object.\u003c/p\u003e\n\u003c/td\u003e\n\u003c/tr\u003e\n\u003ctr\u003e\n\u003ctd\u003e\n\u003cp\u003e\u003ccode class=\"literal\"\u003e.**\u003c/code\u003e\u003c/p\u003e\n\u003c/td\u003e\n\u003ctd\u003e\n\u003cp\u003eRecursive 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 \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e extension of the SQL/JSON standard.\u003c/p\u003e\n\u003c/td\u003e\n\u003c/tr\u003e\n\u003ctr\u003e\n\u003ctd\u003e\n\u003cp\u003e\u003ccode class=\"literal\"\u003e.**{\u003cem class=\"replaceable\"\u003e\u003ccode\u003elevel\u003c/code\u003e\u003c/em\u003e}\u003c/code\u003e\u003c/p\u003e\n\u003cp\u003e\u003ccode class=\"literal\"\u003e.**{\u003cem class=\"replaceable\"\u003e\u003ccode\u003estart_level\u003c/code\u003e\u003c/em\u003e to \u003cem class=\"replaceable\"\u003e\u003ccode\u003eend_level\u003c/code\u003e\u003c/em\u003e}\u003c/code\u003e\u003c/p\u003e\n\u003c/td\u003e\n\u003ctd\u003e\n\u003cp\u003eLike \u003ccode class=\"literal\"\u003e.**\u003c/code\u003e, 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 \u003ccode class=\"literal\"\u003elast\u003c/code\u003e keyword. This is a \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e extension of the SQL/JSON standard.\u003c/p\u003e\n\u003c/td\u003e\n\u003c/tr\u003e\n\u003ctr\u003e\n\u003ctd\u003e\n\u003cp\u003e\u003ccode class=\"literal\"\u003e[\u003cem class=\"replaceable\"\u003e\u003ccode\u003esubscript\u003c/code\u003e\u003c/em\u003e, ...]\u003c/code\u003e\u003c/p\u003e\n\u003c/td\u003e\n\u003ctd\u003e\n\u003cp\u003eArray element accessor. \u003ccode class=\"literal\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003esubscript\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e can be given in two forms: \u003ccode class=\"literal\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eindex\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e or \u003ccode class=\"literal\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003estart_index\u003c/code\u003e\u003c/em\u003e to \u003cem class=\"replaceable\"\u003e\u003ccode\u003eend_index\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e. 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 \u003cem class=\"replaceable\"\u003e\u003ccode\u003estart_index\u003c/code\u003e\u003c/em\u003e and \u003cem class=\"replaceable\"\u003e\u003ccode\u003eend_index\u003c/code\u003e\u003c/em\u003e.\u003c/p\u003e\n\u003cp\u003eThe specified \u003cem class=\"replaceable\"\u003e\u003ccode\u003eindex\u003c/code\u003e\u003c/em\u003e 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 \u003ccode class=\"literal\"\u003elast\u003c/code\u003e keyword to denote the last array element, which is useful for handling arrays of unknown length.\u003c/p\u003e\n\u003c/td\u003e\n\u003c/tr\u003e\n\u003ctr\u003e\n\u003ctd\u003e\n\u003cp\u003e\u003ccode class=\"literal\"\u003e[*]\u003c/code\u003e\u003c/p\u003e\n\u003c/td\u003e\n\u003ctd\u003e\n\u003cp\u003eWildcard array element accessor that returns all array elements.\u003c/p\u003e\n\u003c/td\u003e\n\u003c/tr\u003e\n\u003c/tbody\u003e\n\u003c/table\u003e\n\u003c/div\u003e\n\u003c/div\u003e\u003cbr class=\"table-break\"\u003e\n\u003c/div\u003e\n\u003cdiv class=\"footnotes\"\u003e\n\u003cbr\u003e\n\n\n\u003cdiv class=\"footnote\" id=\"ftn.id-1.5.7.22.18.9.3\"\u003e\n\u003cp\u003e\u003ca class=\"para\" href=\"/docs/18/datatype-json.html#id-1.5.7.22.18.9.3\"\u003e\u003csup class=\"para\"\u003e[7]\u003c/sup\u003e\u003c/a\u003e For this purpose, the term \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003evalue\u003c/span\u003e”\u003c/span\u003e includes array elements, though JSON terminology sometimes considers array elements distinct from values within objects.\u003c/p\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e","manual_path":"datatype-json.html","operator_classes":[{"opcdefault":"t","opcfamily":"btree/jsonb_ops","opcintype":"jsonb","opckeytype":"0","opcmethod":"btree","opcname":"jsonb_ops","opcnamespace":"pg_catalog","opcowner":"POSTGRES"},{"opcdefault":"t","opcfamily":"hash/jsonb_ops","opcintype":"jsonb","opckeytype":"0","opcmethod":"hash","opcname":"jsonb_ops","opcnamespace":"pg_catalog","opcowner":"POSTGRES"},{"opcdefault":"t","opcfamily":"gin/jsonb_ops","opcintype":"jsonb","opckeytype":"text","opcmethod":"gin","opcname":"jsonb_ops","opcnamespace":"pg_catalog","opcowner":"POSTGRES"},{"opcdefault":"f","opcfamily":"gin/jsonb_path_ops","opcintype":"jsonb","opckeytype":"int4","opcmethod":"gin","opcname":"jsonb_path_ops","opcnamespace":"pg_catalog","opcowner":"POSTGRES"}],"operators":[{"descr":"get jsonb object field","oid":"3211","oprcanhash":"f","oprcanmerge":"f","oprcode":"jsonb_object_field","oprcom":"0","oprjoin":"-","oprkind":"b","oprleft":"jsonb","oprname":"-\u003e","oprnamespace":"pg_catalog","oprnegate":"0","oprowner":"POSTGRES","oprrest":"-","oprresult":"jsonb","oprright":"text"},{"descr":"get jsonb object field as text","oid":"3477","oprcanhash":"f","oprcanmerge":"f","oprcode":"jsonb_object_field_text","oprcom":"0","oprjoin":"-","oprkind":"b","oprleft":"jsonb","oprname":"-\u003e\u003e","oprnamespace":"pg_catalog","oprnegate":"0","oprowner":"POSTGRES","oprrest":"-","oprresult":"text","oprright":"text"},{"descr":"get jsonb array element","oid":"3212","oprcanhash":"f","oprcanmerge":"f","oprcode":"jsonb_array_element","oprcom":"0","oprjoin":"-","oprkind":"b","oprleft":"jsonb","oprname":"-\u003e","oprnamespace":"pg_catalog","oprnegate":"0","oprowner":"POSTGRES","oprrest":"-","oprresult":"jsonb","oprright":"int4"},{"descr":"get jsonb array element as text","oid":"3481","oprcanhash":"f","oprcanmerge":"f","oprcode":"jsonb_array_element_text","oprcom":"0","oprjoin":"-","oprkind":"b","oprleft":"jsonb","oprname":"-\u003e\u003e","oprnamespace":"pg_catalog","oprnegate":"0","oprowner":"POSTGRES","oprrest":"-","oprresult":"text","oprright":"int4"},{"descr":"get value from jsonb with path elements","oid":"3213","oprcanhash":"f","oprcanmerge":"f","oprcode":"jsonb_extract_path","oprcom":"0","oprjoin":"-","oprkind":"b","oprleft":"jsonb","oprname":"#\u003e","oprnamespace":"pg_catalog","oprnegate":"0","oprowner":"POSTGRES","oprrest":"-","oprresult":"jsonb","oprright":"_text"},{"descr":"get value from jsonb as text with path elements","oid":"3206","oprcanhash":"f","oprcanmerge":"f","oprcode":"jsonb_extract_path_text","oprcom":"0","oprjoin":"-","oprkind":"b","oprleft":"jsonb","oprname":"#\u003e\u003e","oprnamespace":"pg_catalog","oprnegate":"0","oprowner":"POSTGRES","oprrest":"-","oprresult":"text","oprright":"_text"},{"descr":"equal","oid":"3240","oprcanhash":"t","oprcanmerge":"t","oprcode":"jsonb_eq","oprcom":"=(jsonb,jsonb)","oprjoin":"eqjoinsel","oprkind":"b","oprleft":"jsonb","oprname":"=","oprnamespace":"pg_catalog","oprnegate":"\u003c\u003e(jsonb,jsonb)","oprowner":"POSTGRES","oprrest":"eqsel","oprresult":"bool","oprright":"jsonb"},{"descr":"not equal","oid":"3241","oprcanhash":"f","oprcanmerge":"f","oprcode":"jsonb_ne","oprcom":"\u003c\u003e(jsonb,jsonb)","oprjoin":"neqjoinsel","oprkind":"b","oprleft":"jsonb","oprname":"\u003c\u003e","oprnamespace":"pg_catalog","oprnegate":"=(jsonb,jsonb)","oprowner":"POSTGRES","oprrest":"neqsel","oprresult":"bool","oprright":"jsonb"},{"descr":"less than","oid":"3242","oprcanhash":"f","oprcanmerge":"f","oprcode":"jsonb_lt","oprcom":"\u003e(jsonb,jsonb)","oprjoin":"scalarltjoinsel","oprkind":"b","oprleft":"jsonb","oprname":"\u003c","oprnamespace":"pg_catalog","oprnegate":"\u003e=(jsonb,jsonb)","oprowner":"POSTGRES","oprrest":"scalarltsel","oprresult":"bool","oprright":"jsonb"},{"descr":"greater than","oid":"3243","oprcanhash":"f","oprcanmerge":"f","oprcode":"jsonb_gt","oprcom":"\u003c(jsonb,jsonb)","oprjoin":"scalargtjoinsel","oprkind":"b","oprleft":"jsonb","oprname":"\u003e","oprnamespace":"pg_catalog","oprnegate":"\u003c=(jsonb,jsonb)","oprowner":"POSTGRES","oprrest":"scalargtsel","oprresult":"bool","oprright":"jsonb"},{"descr":"less than or equal","oid":"3244","oprcanhash":"f","oprcanmerge":"f","oprcode":"jsonb_le","oprcom":"\u003e=(jsonb,jsonb)","oprjoin":"scalarlejoinsel","oprkind":"b","oprleft":"jsonb","oprname":"\u003c=","oprnamespace":"pg_catalog","oprnegate":"\u003e(jsonb,jsonb)","oprowner":"POSTGRES","oprrest":"scalarlesel","oprresult":"bool","oprright":"jsonb"},{"descr":"greater than or equal","oid":"3245","oprcanhash":"f","oprcanmerge":"f","oprcode":"jsonb_ge","oprcom":"\u003c=(jsonb,jsonb)","oprjoin":"scalargejoinsel","oprkind":"b","oprleft":"jsonb","oprname":"\u003e=","oprnamespace":"pg_catalog","oprnegate":"\u003c(jsonb,jsonb)","oprowner":"POSTGRES","oprrest":"scalargesel","oprresult":"bool","oprright":"jsonb"},{"descr":"contains","oid":"3246","oprcanhash":"f","oprcanmerge":"f","oprcode":"jsonb_contains","oprcom":"\u003c@(jsonb,jsonb)","oprjoin":"matchingjoinsel","oprkind":"b","oprleft":"jsonb","oprname":"@\u003e","oprnamespace":"pg_catalog","oprnegate":"0","oprowner":"POSTGRES","oprrest":"matchingsel","oprresult":"bool","oprright":"jsonb"},{"descr":"key exists","oid":"3247","oprcanhash":"f","oprcanmerge":"f","oprcode":"jsonb_exists","oprcom":"0","oprjoin":"matchingjoinsel","oprkind":"b","oprleft":"jsonb","oprname":"?","oprnamespace":"pg_catalog","oprnegate":"0","oprowner":"POSTGRES","oprrest":"matchingsel","oprresult":"bool","oprright":"text"},{"descr":"any key exists","oid":"3248","oprcanhash":"f","oprcanmerge":"f","oprcode":"jsonb_exists_any","oprcom":"0","oprjoin":"matchingjoinsel","oprkind":"b","oprleft":"jsonb","oprname":"?|","oprnamespace":"pg_catalog","oprnegate":"0","oprowner":"POSTGRES","oprrest":"matchingsel","oprresult":"bool","oprright":"_text"},{"descr":"all keys exist","oid":"3249","oprcanhash":"f","oprcanmerge":"f","oprcode":"jsonb_exists_all","oprcom":"0","oprjoin":"matchingjoinsel","oprkind":"b","oprleft":"jsonb","oprname":"?\u0026","oprnamespace":"pg_catalog","oprnegate":"0","oprowner":"POSTGRES","oprrest":"matchingsel","oprresult":"bool","oprright":"_text"},{"descr":"is contained by","oid":"3250","oprcanhash":"f","oprcanmerge":"f","oprcode":"jsonb_contained","oprcom":"@\u003e(jsonb,jsonb)","oprjoin":"matchingjoinsel","oprkind":"b","oprleft":"jsonb","oprname":"\u003c@","oprnamespace":"pg_catalog","oprnegate":"0","oprowner":"POSTGRES","oprrest":"matchingsel","oprresult":"bool","oprright":"jsonb"},{"descr":"concatenate","oid":"3284","oprcanhash":"f","oprcanmerge":"f","oprcode":"jsonb_concat","oprcom":"0","oprjoin":"-","oprkind":"b","oprleft":"jsonb","oprname":"||","oprnamespace":"pg_catalog","oprnegate":"0","oprowner":"POSTGRES","oprrest":"-","oprresult":"jsonb","oprright":"jsonb"},{"descr":"delete object field","oid":"3285","oprcanhash":"f","oprcanmerge":"f","oprcode":"jsonb_delete(jsonb,text)","oprcom":"0","oprjoin":"-","oprkind":"b","oprleft":"jsonb","oprname":"-","oprnamespace":"pg_catalog","oprnegate":"0","oprowner":"POSTGRES","oprrest":"-","oprresult":"jsonb","oprright":"text"},{"descr":"delete object fields","oid":"3398","oprcanhash":"f","oprcanmerge":"f","oprcode":"jsonb_delete(jsonb,_text)","oprcom":"0","oprjoin":"-","oprkind":"b","oprleft":"jsonb","oprname":"-","oprnamespace":"pg_catalog","oprnegate":"0","oprowner":"POSTGRES","oprrest":"-","oprresult":"jsonb","oprright":"_text"},{"descr":"delete array element","oid":"3286","oprcanhash":"f","oprcanmerge":"f","oprcode":"jsonb_delete(jsonb,int4)","oprcom":"0","oprjoin":"-","oprkind":"b","oprleft":"jsonb","oprname":"-","oprnamespace":"pg_catalog","oprnegate":"0","oprowner":"POSTGRES","oprrest":"-","oprresult":"jsonb","oprright":"int4"},{"descr":"delete path","oid":"3287","oprcanhash":"f","oprcanmerge":"f","oprcode":"jsonb_delete_path","oprcom":"0","oprjoin":"-","oprkind":"b","oprleft":"jsonb","oprname":"#-","oprnamespace":"pg_catalog","oprnegate":"0","oprowner":"POSTGRES","oprrest":"-","oprresult":"jsonb","oprright":"_text"},{"descr":"jsonpath exists","oid":"4012","oprcanhash":"f","oprcanmerge":"f","oprcode":"jsonb_path_exists_opr(jsonb,jsonpath)","oprcom":"0","oprjoin":"matchingjoinsel","oprkind":"b","oprleft":"jsonb","oprname":"@?","oprnamespace":"pg_catalog","oprnegate":"0","oprowner":"POSTGRES","oprrest":"matchingsel","oprresult":"bool","oprright":"jsonpath"},{"descr":"jsonpath match","oid":"4013","oprcanhash":"f","oprcanmerge":"f","oprcode":"jsonb_path_match_opr(jsonb,jsonpath)","oprcom":"0","oprjoin":"matchingjoinsel","oprkind":"b","oprleft":"jsonb","oprname":"@@","oprnamespace":"pg_catalog","oprnegate":"0","oprowner":"POSTGRES","oprrest":"matchingsel","oprresult":"bool","oprright":"jsonpath"}],"ranges":[],"related":[{"label":"pg_type catalog","url":"/wiki/catalog/pg_type/?v=18"},{"label":"pg_cast catalog","url":"/wiki/catalog/pg_cast/?v=18"},{"label":"pg_operator catalog","url":"/wiki/catalog/pg_operator/?v=18"},{"label":"pg_opclass catalog","url":"/wiki/catalog/pg_opclass/?v=18"},{"label":"Arrays","url":"/wiki/type/arrays/?v=18"},{"label":"btree index access method","url":"/wiki/indexam/btree/?v=18"},{"label":"gin index access method","url":"/wiki/indexam/gin/?v=18"},{"label":"hash index access method","url":"/wiki/indexam/hash/?v=18"}],"release":{"channel":"stable","label":"18.6","major":"18","manual_sha256":{"arrays.html":"0e1d5c5a7b4a949d4439995f44f74ad4264e050d16eb9ad2a850fbaee04032df","catalog-pg-type.html":"ea9c9313bab8e92a7da18ebc50c7ff1f908dd80999a7e0ca3bbc82ba626c58d4","datatype-binary.html":"0c03ec47b76e37888a3da7f5dcfd340128a5d356642a85185cc9ebe17856a904","datatype-bit.html":"c49124aad561636c18080b9e09572fde7785a6b9eae86f5a33283f0fd6774c37","datatype-boolean.html":"b633f663e6c8276e7056287640c413aa79dbc15d5353cee0d41653d58d376956","datatype-character.html":"c73eebe413ea709a7792e4cfc3cfe9fb68cb496ebe83c3ebaa9d09f8c40d0f73","datatype-datetime.html":"e366275d8b13845bfa4ef0d68faa093e5f3ecb708a1ec870cbcbf7e425b85699","datatype-enum.html":"cab38e53222e30cc94b24de4b33634fd22d7c93f58ebcf35f646c9b8da78fae8","datatype-geometric.html":"0eb3053cfe7d6b0a4b5592c5d7af8d97cae527e87f063c5793413bcba5c14cd9","datatype-json.html":"650162a05b660146b500a205d523374d00aee117a6a6240d30262be51b6e33e5","datatype-money.html":"8b29c14b90679683ac5c472b59a0a7e2853f32cc7db43fab417b2697a6879964","datatype-net-types.html":"96846e641cc37727ddcd8b8a1aa0dbe33cd9787760a2b4efb0dcb9403d5be63f","datatype-numeric.html":"b74619f7ac2f1ca9f85c48073df78c33dd30e464e61baba6008634534edcb500","datatype-oid.html":"8b3360302225f08ae72716ce1804596de95b568e5d31f788cb4597427b28cc38","datatype-pg-lsn.html":"339172f5107cc7eb7139bec911277647219e92a532cebd0f3da0a5dacf8195af","datatype-pseudo.html":"c7cf0b8214304bd8702c95f2142671ae5f0d631f83717f9c07d9e1f2579961b4","datatype-textsearch.html":"4b15e05be8b49a71b28aea22f2c46d0dbf639a91e7b4fe129a4c737c6daad47c","datatype-uuid.html":"292b307b3223c6182bdba687e7f69e6dca9038430143ad79d56327f6ed4c3246","datatype-xml.html":"061fc2295b7b69be704f0a741b8decd10ad721059f871fb0243c669bb54d7822","datatype.html":"e581f67c74e42006289638c8659e9b338adbfd7f068bd8bf2b3be3880fcd2254","domains.html":"82d486973ccc35d14276627a67ac76b434452f23f9ed4d8aa3fbb01fdf285fc2","functions-info.html":"78ac80bf81da2e4f4a33f3b850faa86f0f58ec54a083d6c31da557907de0df87","rangetypes.html":"e4960abc7ce8e51d794f94f1e57b099d30b7f75dd7f2b23a960eef91f6291c33","rowtypes.html":"74f99e029c3a9edfd3e18810ba3c8cb66d3aada91eb67b86476560871e6617e9","sql-createdomain.html":"e94f927196de1b0ea1ce35cd4603c1f6f544439adcc0edf1c4222358e832245b"},"ref":"https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2","revision":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","source_files":{"doc/src/sgml/datatype.sgml":"86328daa77e20d81d222376ec0306d9841a17104e436c08d138eb763aefb3700","src/include/catalog/pg_am.dat":"b3cb86b102a42fb0024cbd99779c9897afb40d12a71f5cc5a0e5aee7ff2f9969","src/include/catalog/pg_am.h":"3426799df799f32163fcc0765f43dbdb5e88e626acf31284fef9fc6014b69d8e","src/include/catalog/pg_cast.dat":"97911281ca2c81917394ccb2ec13367e37c6801fd461d5e1a3d2f28b911b46b2","src/include/catalog/pg_cast.h":"de585c7df687d698e7d1d100dce8a794ac9da52414ea48169191019178c8f053","src/include/catalog/pg_opclass.dat":"4ee7d3619a6aa106e1c0db55de903931c7d11b1c60ba053a022519c55f8ee668","src/include/catalog/pg_opclass.h":"9c218537806c8ef0398302faaca7f91315dfc1198c48706cebffb479b68defaf","src/include/catalog/pg_operator.dat":"5d35b9b2ef5f9797cc815263927ef8a12367fedace53e828b4e8b320e2a7f703","src/include/catalog/pg_operator.h":"621b18cfffe102413d79b746ce7a10f77fc60fdd097bac34c22b31070d3ee3c4","src/include/catalog/pg_opfamily.dat":"3b694879027b858f2ecf1fa1152b7e30c6e15a216ba99c2384291c30e9d52311","src/include/catalog/pg_opfamily.h":"e1f5fc8aebd3042df847455bf66bf755f768c2aa8a9b03d22f7ec8563eba59c0","src/include/catalog/pg_proc.dat":"1f934ce80d460159dda137714374a35f9896315ea2476709cbb4da13b38ef95a","src/include/catalog/pg_proc.h":"f7d59f07c5b95e2f3c7141762a0f576e8a3af7581ba54c27537f4d500472bea5","src/include/catalog/pg_range.dat":"5c2271f8e89e9378d1887204e5785060b77405b5f0ff3e6c719250c9c1dcfcc4","src/include/catalog/pg_range.h":"45546d952b5181f989bd9234721897fc2b37005c8e49ce2ddc156f26974c3ca4","src/include/catalog/pg_type.dat":"5f5887b75677cba2d4a1a0cfeb355df5ed91f85d385fac88bd8d7c605b3578f9","src/include/catalog/pg_type.h":"8fb198749fd82b6c1818a3c18455f226f66116fcfeb3d4959c65ce3fda22802e"},"source_sha256":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f"},"sections":[],"signature":"jsonb","sources":[{"label":"PostgreSQL 18 English manual","path":"datatype-json.html","sha256":"650162a05b660146b500a205d523374d00aee117a6a6240d30262be51b6e33e5","url":"/docs/18/datatype-json.html"},{"label":"Matching PostgreSQL source archive","sha256":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","url":"https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2"}]},"ManualEvidence":{"manual_path":"datatype-json.html","release":{"channel":"stable","label":"18.6","major":"18","manual_sha256":{"arrays.html":"0e1d5c5a7b4a949d4439995f44f74ad4264e050d16eb9ad2a850fbaee04032df","catalog-pg-type.html":"ea9c9313bab8e92a7da18ebc50c7ff1f908dd80999a7e0ca3bbc82ba626c58d4","datatype-binary.html":"0c03ec47b76e37888a3da7f5dcfd340128a5d356642a85185cc9ebe17856a904","datatype-bit.html":"c49124aad561636c18080b9e09572fde7785a6b9eae86f5a33283f0fd6774c37","datatype-boolean.html":"b633f663e6c8276e7056287640c413aa79dbc15d5353cee0d41653d58d376956","datatype-character.html":"c73eebe413ea709a7792e4cfc3cfe9fb68cb496ebe83c3ebaa9d09f8c40d0f73","datatype-datetime.html":"e366275d8b13845bfa4ef0d68faa093e5f3ecb708a1ec870cbcbf7e425b85699","datatype-enum.html":"cab38e53222e30cc94b24de4b33634fd22d7c93f58ebcf35f646c9b8da78fae8","datatype-geometric.html":"0eb3053cfe7d6b0a4b5592c5d7af8d97cae527e87f063c5793413bcba5c14cd9","datatype-json.html":"650162a05b660146b500a205d523374d00aee117a6a6240d30262be51b6e33e5","datatype-money.html":"8b29c14b90679683ac5c472b59a0a7e2853f32cc7db43fab417b2697a6879964","datatype-net-types.html":"96846e641cc37727ddcd8b8a1aa0dbe33cd9787760a2b4efb0dcb9403d5be63f","datatype-numeric.html":"b74619f7ac2f1ca9f85c48073df78c33dd30e464e61baba6008634534edcb500","datatype-oid.html":"8b3360302225f08ae72716ce1804596de95b568e5d31f788cb4597427b28cc38","datatype-pg-lsn.html":"339172f5107cc7eb7139bec911277647219e92a532cebd0f3da0a5dacf8195af","datatype-pseudo.html":"c7cf0b8214304bd8702c95f2142671ae5f0d631f83717f9c07d9e1f2579961b4","datatype-textsearch.html":"4b15e05be8b49a71b28aea22f2c46d0dbf639a91e7b4fe129a4c737c6daad47c","datatype-uuid.html":"292b307b3223c6182bdba687e7f69e6dca9038430143ad79d56327f6ed4c3246","datatype-xml.html":"061fc2295b7b69be704f0a741b8decd10ad721059f871fb0243c669bb54d7822","datatype.html":"e581f67c74e42006289638c8659e9b338adbfd7f068bd8bf2b3be3880fcd2254","domains.html":"82d486973ccc35d14276627a67ac76b434452f23f9ed4d8aa3fbb01fdf285fc2","functions-info.html":"78ac80bf81da2e4f4a33f3b850faa86f0f58ec54a083d6c31da557907de0df87","rangetypes.html":"e4960abc7ce8e51d794f94f1e57b099d30b7f75dd7f2b23a960eef91f6291c33","rowtypes.html":"74f99e029c3a9edfd3e18810ba3c8cb66d3aada91eb67b86476560871e6617e9","sql-createdomain.html":"e94f927196de1b0ea1ce35cd4603c1f6f544439adcc0edf1c4222358e832245b"},"ref":"https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2","revision":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","source_files":{"doc/src/sgml/datatype.sgml":"86328daa77e20d81d222376ec0306d9841a17104e436c08d138eb763aefb3700","src/include/catalog/pg_am.dat":"b3cb86b102a42fb0024cbd99779c9897afb40d12a71f5cc5a0e5aee7ff2f9969","src/include/catalog/pg_am.h":"3426799df799f32163fcc0765f43dbdb5e88e626acf31284fef9fc6014b69d8e","src/include/catalog/pg_cast.dat":"97911281ca2c81917394ccb2ec13367e37c6801fd461d5e1a3d2f28b911b46b2","src/include/catalog/pg_cast.h":"de585c7df687d698e7d1d100dce8a794ac9da52414ea48169191019178c8f053","src/include/catalog/pg_opclass.dat":"4ee7d3619a6aa106e1c0db55de903931c7d11b1c60ba053a022519c55f8ee668","src/include/catalog/pg_opclass.h":"9c218537806c8ef0398302faaca7f91315dfc1198c48706cebffb479b68defaf","src/include/catalog/pg_operator.dat":"5d35b9b2ef5f9797cc815263927ef8a12367fedace53e828b4e8b320e2a7f703","src/include/catalog/pg_operator.h":"621b18cfffe102413d79b746ce7a10f77fc60fdd097bac34c22b31070d3ee3c4","src/include/catalog/pg_opfamily.dat":"3b694879027b858f2ecf1fa1152b7e30c6e15a216ba99c2384291c30e9d52311","src/include/catalog/pg_opfamily.h":"e1f5fc8aebd3042df847455bf66bf755f768c2aa8a9b03d22f7ec8563eba59c0","src/include/catalog/pg_proc.dat":"1f934ce80d460159dda137714374a35f9896315ea2476709cbb4da13b38ef95a","src/include/catalog/pg_proc.h":"f7d59f07c5b95e2f3c7141762a0f576e8a3af7581ba54c27537f4d500472bea5","src/include/catalog/pg_range.dat":"5c2271f8e89e9378d1887204e5785060b77405b5f0ff3e6c719250c9c1dcfcc4","src/include/catalog/pg_range.h":"45546d952b5181f989bd9234721897fc2b37005c8e49ce2ddc156f26974c3ca4","src/include/catalog/pg_type.dat":"5f5887b75677cba2d4a1a0cfeb355df5ed91f85d385fac88bd8d7c605b3578f9","src/include/catalog/pg_type.h":"8fb198749fd82b6c1818a3c18455f226f66116fcfeb3d4959c65ce3fda22802e"},"source_sha256":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f"},"sources":[{"label":"PostgreSQL 18 English manual","path":"datatype-json.html","sha256":"650162a05b660146b500a205d523374d00aee117a6a6240d30262be51b6e33e5","url":"/docs/18/datatype-json.html"},{"label":"Matching PostgreSQL source archive","sha256":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","url":"https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2"}]},"MeasuredEvidence":{}},"Text":{"Collection":"type","Key":"jsonb","SourceDatabase":"center","Version":"18","Locale":"en","Title":"jsonb","Summary":"binary JSON data, decomposed","BodyHTML":"\u003cdiv id=\"DATATYPE-JSON\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch2\u003e8.14. JSON Types \u003c/h2\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\n\u003cp\u003eJSON data types are for storing JSON (JavaScript Object Notation) data, as specified in \u003ca href=\"https://datatracker.ietf.org/doc/html/rfc7159\" rel=\"nofollow\"\u003eRFC 7159\u003c/a\u003e. Such data can also be stored as \u003ccode\u003etext\u003c/code\u003e, 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 \u003ca href=\"/docs/18/functions-json.html\" rel=\"nofollow\"\u003eSection 9.16\u003c/a\u003e.\u003c/p\u003e\n\u003cp\u003e\u003cspan\u003ePostgreSQL\u003c/span\u003e offers two types for storing JSON data: \u003ccode\u003ejson\u003c/code\u003e and \u003ccode\u003ejsonb\u003c/code\u003e. To implement efficient query mechanisms for these data types, \u003cspan\u003ePostgreSQL\u003c/span\u003e also provides the \u003ccode\u003ejsonpath\u003c/code\u003e data type described in \u003ca href=\"/docs/18/datatype-json.html#DATATYPE-JSONPATH\" rel=\"nofollow\"\u003eSection 8.14.7\u003c/a\u003e.\u003c/p\u003e\n\u003cp\u003eThe \u003ccode\u003ejson\u003c/code\u003e and \u003ccode\u003ejsonb\u003c/code\u003e data types accept \u003cspan\u003e\u003cem\u003ealmost\u003c/em\u003e\u003c/span\u003e identical sets of values as input. The major practical difference is one of efficiency. The \u003ccode\u003ejson\u003c/code\u003e data type stores an exact copy of the input text, which processing functions must reparse on each execution; while \u003ccode\u003ejsonb\u003c/code\u003e 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. \u003ccode\u003ejsonb\u003c/code\u003e also supports indexing, which can be a significant advantage.\u003c/p\u003e\n\u003cp\u003eBecause the \u003ccode\u003ejson\u003c/code\u003e 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, \u003ccode\u003ejsonb\u003c/code\u003e 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.\u003c/p\u003e\n\u003cp\u003eIn general, most applications should prefer to store JSON data as \u003ccode\u003ejsonb\u003c/code\u003e, unless there are quite specialized needs, such as legacy assumptions about ordering of object keys.\u003c/p\u003e\n\u003cp\u003eRFC 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.\u003c/p\u003e\n\u003cp\u003eRFC 7159 permits JSON strings to contain Unicode escape sequences denoted by \u003ccode\u003e\\u\u003cem\u003e\u003ccode\u003eXXXX\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e. In the input function for the \u003ccode\u003ejson\u003c/code\u003e type, Unicode escapes are allowed regardless of the database encoding, and are checked only for syntactic correctness (that is, that four hex digits follow \u003ccode\u003e\\u\u003c/code\u003e). However, the input function for \u003ccode\u003ejsonb\u003c/code\u003e is stricter: it disallows Unicode escapes for characters that cannot be represented in the database encoding. The \u003ccode\u003ejsonb\u003c/code\u003e type also rejects \u003ccode\u003e\\u0000\u003c/code\u003e (because that cannot be represented in \u003cspan\u003ePostgreSQL\u003c/span\u003e\u0026#39;s \u003ccode\u003etext\u003c/code\u003e 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.\u003c/p\u003e\n\u003cdiv\u003e\n\u003ch3\u003eNote\u003c/h3\u003e\n\u003cp\u003eMany of the JSON processing functions described in \u003ca href=\"/docs/18/functions-json.html\" rel=\"nofollow\"\u003eSection 9.16\u003c/a\u003e 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 \u003ccode\u003ejson\u003c/code\u003e not \u003ccode\u003ejsonb\u003c/code\u003e. The fact that the \u003ccode\u003ejson\u003c/code\u003e 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.\u003c/p\u003e\n\u003c/div\u003e\n\u003cp\u003eWhen converting textual JSON input into \u003ccode\u003ejsonb\u003c/code\u003e, the primitive types described by RFC 7159 are effectively mapped onto native \u003cspan\u003ePostgreSQL\u003c/span\u003e types, as shown in \u003ca href=\"/docs/18/datatype-json.html#JSON-TYPE-MAPPING-TABLE\" rel=\"nofollow\"\u003eTable 8.23\u003c/a\u003e. Therefore, there are some minor additional constraints on what constitutes valid \u003ccode\u003ejsonb\u003c/code\u003e data that do not apply to the \u003ccode\u003ejson\u003c/code\u003e type, nor to JSON in the abstract, corresponding to limits on what can be represented by the underlying data type. Notably, \u003ccode\u003ejsonb\u003c/code\u003e will reject numbers that are outside the range of the \u003cspan\u003ePostgreSQL\u003c/span\u003e \u003ccode\u003enumeric\u003c/code\u003e data type, while \u003ccode\u003ejson\u003c/code\u003e will not. Such implementation-defined restrictions are permitted by RFC 7159. However, in practice such problems are far more likely to occur in other implementations, as it is common to represent JSON\u0026#39;s \u003ccode\u003enumber\u003c/code\u003e primitive type as IEEE 754 double precision floating point (which RFC 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 \u003cspan\u003ePostgreSQL\u003c/span\u003e should be considered.\u003c/p\u003e\n\u003cp\u003eConversely, 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 \u003cspan\u003ePostgreSQL\u003c/span\u003e types.\u003c/p\u003e\n\u003cdiv id=\"JSON-TYPE-MAPPING-TABLE\"\u003e\n\u003cp\u003e\u003cstrong\u003eTable 8.23. JSON Primitive Types and Corresponding \u003cspan\u003ePostgreSQL\u003c/span\u003e Types\u003c/strong\u003e\u003c/p\u003e\n\u003cdiv\u003e\n\u003ctable\u003e\n\n\n\n\n\n\u003cthead\u003e\n\u003ctr\u003e\n\u003cth\u003eJSON primitive type\u003c/th\u003e\n\u003cth\u003e\u003cspan\u003ePostgreSQL\u003c/span\u003e type\u003c/th\u003e\n\u003cth\u003eNotes\u003c/th\u003e\n\u003c/tr\u003e\n\u003c/thead\u003e\n\u003ctbody\u003e\n\u003ctr\u003e\n\u003ctd\u003e\u003ccode\u003estring\u003c/code\u003e\u003c/td\u003e\n\u003ctd\u003e\u003ccode\u003etext\u003c/code\u003e\u003c/td\u003e\n\u003ctd\u003e\u003ccode\u003e\\u0000\u003c/code\u003e is disallowed, as are Unicode escapes representing characters not available in the database encoding\u003c/td\u003e\n\u003c/tr\u003e\n\u003ctr\u003e\n\u003ctd\u003e\u003ccode\u003enumber\u003c/code\u003e\u003c/td\u003e\n\u003ctd\u003e\u003ccode\u003enumeric\u003c/code\u003e\u003c/td\u003e\n\u003ctd\u003e\u003ccode\u003eNaN\u003c/code\u003e and \u003ccode\u003einfinity\u003c/code\u003e values are disallowed\u003c/td\u003e\n\u003c/tr\u003e\n\u003ctr\u003e\n\u003ctd\u003e\u003ccode\u003eboolean\u003c/code\u003e\u003c/td\u003e\n\u003ctd\u003e\u003ccode\u003eboolean\u003c/code\u003e\u003c/td\u003e\n\u003ctd\u003eOnly lowercase \u003ccode\u003etrue\u003c/code\u003e and \u003ccode\u003efalse\u003c/code\u003e spellings are accepted\u003c/td\u003e\n\u003c/tr\u003e\n\u003ctr\u003e\n\u003ctd\u003e\u003ccode\u003enull\u003c/code\u003e\u003c/td\u003e\n\u003ctd\u003e(none)\u003c/td\u003e\n\u003ctd\u003eSQL \u003ccode\u003eNULL\u003c/code\u003e is a different concept\u003c/td\u003e\n\u003c/tr\u003e\n\u003c/tbody\u003e\n\u003c/table\u003e\n\u003c/div\u003e\n\u003c/div\u003e\u003cbr\u003e\n\u003cdiv id=\"JSON-KEYS-ELEMENTS\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3\u003e8.14.1. JSON Input and Output Syntax \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eThe input/output syntax for the JSON data types is as specified in RFC 7159.\u003c/p\u003e\n\u003cp\u003eThe following are all valid \u003ccode\u003ejson\u003c/code\u003e (or \u003ccode\u003ejsonb\u003c/code\u003e) expressions:\u003c/p\u003e\n\u003cpre\u003e-- Simple scalar/primitive value\n-- Primitive values can be numbers, quoted strings, true, false, or null\nSELECT \u0026#39;5\u0026#39;::json;\n\n-- Array of zero or more elements (elements need not be of same type)\nSELECT \u0026#39;[1, 2, \u0026#34;foo\u0026#34;, null]\u0026#39;::json;\n\n-- Object containing pairs of keys and values\n-- Note that object keys must always be quoted strings\nSELECT \u0026#39;{\u0026#34;bar\u0026#34;: \u0026#34;baz\u0026#34;, \u0026#34;balance\u0026#34;: 7.77, \u0026#34;active\u0026#34;: false}\u0026#39;::json;\n\n-- Arrays and objects can be nested arbitrarily\nSELECT \u0026#39;{\u0026#34;foo\u0026#34;: [true, \u0026#34;bar\u0026#34;], \u0026#34;tags\u0026#34;: {\u0026#34;a\u0026#34;: 1, \u0026#34;b\u0026#34;: null}}\u0026#39;::json;\n\u003c/pre\u003e\n\u003cp\u003eAs previously stated, when a JSON value is input and then printed without any additional processing, \u003ccode\u003ejson\u003c/code\u003e outputs the same text that was input, while \u003ccode\u003ejsonb\u003c/code\u003e does not preserve semantically-insignificant details such as whitespace. For example, note the differences here:\u003c/p\u003e\n\u003cpre\u003eSELECT \u0026#39;{\u0026#34;bar\u0026#34;: \u0026#34;baz\u0026#34;, \u0026#34;balance\u0026#34;: 7.77, \u0026#34;active\u0026#34;:false}\u0026#39;::json;\n                      json\n-------------------------------------------------\n {\u0026#34;bar\u0026#34;: \u0026#34;baz\u0026#34;, \u0026#34;balance\u0026#34;: 7.77, \u0026#34;active\u0026#34;:false}\n(1 row)\n\nSELECT \u0026#39;{\u0026#34;bar\u0026#34;: \u0026#34;baz\u0026#34;, \u0026#34;balance\u0026#34;: 7.77, \u0026#34;active\u0026#34;:false}\u0026#39;::jsonb;\n                      jsonb\n--------------------------------------------------\n {\u0026#34;bar\u0026#34;: \u0026#34;baz\u0026#34;, \u0026#34;active\u0026#34;: false, \u0026#34;balance\u0026#34;: 7.77}\n(1 row)\n\u003c/pre\u003e\n\u003cp\u003eOne semantically-insignificant detail worth noting is that in \u003ccode\u003ejsonb\u003c/code\u003e, numbers will be printed according to the behavior of the underlying \u003ccode\u003enumeric\u003c/code\u003e type. In practice this means that numbers entered with \u003ccode\u003eE\u003c/code\u003e notation will be printed without it, for example:\u003c/p\u003e\n\u003cpre\u003eSELECT \u0026#39;{\u0026#34;reading\u0026#34;: 1.230e-5}\u0026#39;::json, \u0026#39;{\u0026#34;reading\u0026#34;: 1.230e-5}\u0026#39;::jsonb;\n         json          |          jsonb\n-----------------------+-------------------------\n {\u0026#34;reading\u0026#34;: 1.230e-5} | {\u0026#34;reading\u0026#34;: 0.00001230}\n(1 row)\n\u003c/pre\u003e\n\u003cp\u003eHowever, \u003ccode\u003ejsonb\u003c/code\u003e will preserve trailing fractional zeroes, as seen in this example, even though those are semantically insignificant for purposes such as equality checks.\u003c/p\u003e\n\u003cp\u003eFor the list of built-in functions and operators available for constructing and processing JSON values, see \u003ca href=\"/docs/18/functions-json.html\" rel=\"nofollow\"\u003eSection 9.16\u003c/a\u003e.\u003c/p\u003e\n\u003c/div\u003e\n\u003cdiv id=\"JSON-DOC-DESIGN\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3\u003e8.14.2. Designing JSON Documents \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eRepresenting 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 \u003cspan\u003e“\u003cspan\u003edocuments\u003c/span\u003e”\u003c/span\u003e (datums) in a table.\u003c/p\u003e\n\u003cp\u003eJSON 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.\u003c/p\u003e\n\u003c/div\u003e\n\u003cdiv id=\"JSON-CONTAINMENT\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3\u003e8.14.3. \u003ccode\u003ejsonb\u003c/code\u003e Containment and Existence \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eTesting \u003cem\u003econtainment\u003c/em\u003e is an important capability of \u003ccode\u003ejsonb\u003c/code\u003e. There is no parallel set of facilities for the \u003ccode\u003ejson\u003c/code\u003e type. Containment tests whether one \u003ccode\u003ejsonb\u003c/code\u003e document has contained within it another one. These examples return true except as noted:\u003c/p\u003e\n\u003cpre\u003e-- Simple scalar/primitive values contain only the identical value:\nSELECT \u0026#39;\u0026#34;foo\u0026#34;\u0026#39;::jsonb @\u0026gt; \u0026#39;\u0026#34;foo\u0026#34;\u0026#39;::jsonb;\n\n-- The array on the right side is contained within the one on the left:\nSELECT \u0026#39;[1, 2, 3]\u0026#39;::jsonb @\u0026gt; \u0026#39;[1, 3]\u0026#39;::jsonb;\n\n-- Order of array elements is not significant, so this is also true:\nSELECT \u0026#39;[1, 2, 3]\u0026#39;::jsonb @\u0026gt; \u0026#39;[3, 1]\u0026#39;::jsonb;\n\n-- Duplicate array elements don\u0026#39;t matter either:\nSELECT \u0026#39;[1, 2, 3]\u0026#39;::jsonb @\u0026gt; \u0026#39;[1, 2, 2]\u0026#39;::jsonb;\n\n-- The object with a single pair on the right side is contained\n-- within the object on the left side:\nSELECT \u0026#39;{\u0026#34;product\u0026#34;: \u0026#34;PostgreSQL\u0026#34;, \u0026#34;version\u0026#34;: 9.4, \u0026#34;jsonb\u0026#34;: true}\u0026#39;::jsonb @\u0026gt; \u0026#39;{\u0026#34;version\u0026#34;: 9.4}\u0026#39;::jsonb;\n\n-- The array on the right side is \u003cspan\u003e\u003cstrong\u003enot\u003c/strong\u003e\u003c/span\u003e considered contained within the\n-- array on the left, even though a similar array is nested within it:\nSELECT \u0026#39;[1, 2, [1, 3]]\u0026#39;::jsonb @\u0026gt; \u0026#39;[1, 3]\u0026#39;::jsonb;  -- yields false\n\n-- But with a layer of nesting, it is contained:\nSELECT \u0026#39;[1, 2, [1, 3]]\u0026#39;::jsonb @\u0026gt; \u0026#39;[[1, 3]]\u0026#39;::jsonb;\n\n-- Similarly, containment is not reported here:\nSELECT \u0026#39;{\u0026#34;foo\u0026#34;: {\u0026#34;bar\u0026#34;: \u0026#34;baz\u0026#34;}}\u0026#39;::jsonb @\u0026gt; \u0026#39;{\u0026#34;bar\u0026#34;: \u0026#34;baz\u0026#34;}\u0026#39;::jsonb;  -- yields false\n\n-- A top-level key and an empty object is contained:\nSELECT \u0026#39;{\u0026#34;foo\u0026#34;: {\u0026#34;bar\u0026#34;: \u0026#34;baz\u0026#34;}}\u0026#39;::jsonb @\u0026gt; \u0026#39;{\u0026#34;foo\u0026#34;: {}}\u0026#39;::jsonb;\n\u003c/pre\u003e\n\u003cp\u003eThe 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.\u003c/p\u003e\n\u003cp\u003eAs a special exception to the general principle that the structures must match, an array may contain a primitive value:\u003c/p\u003e\n\u003cpre\u003e-- This array contains the primitive string value:\nSELECT \u0026#39;[\u0026#34;foo\u0026#34;, \u0026#34;bar\u0026#34;]\u0026#39;::jsonb @\u0026gt; \u0026#39;\u0026#34;bar\u0026#34;\u0026#39;::jsonb;\n\n-- This exception is not reciprocal -- non-containment is reported here:\nSELECT \u0026#39;\u0026#34;bar\u0026#34;\u0026#39;::jsonb @\u0026gt; \u0026#39;[\u0026#34;bar\u0026#34;]\u0026#39;::jsonb;  -- yields false\n\u003c/pre\u003e\n\u003cp\u003e\u003ccode\u003ejsonb\u003c/code\u003e also has an \u003cem\u003eexistence\u003c/em\u003e operator, which is a variation on the theme of containment: it tests whether a string (given as a \u003ccode\u003etext\u003c/code\u003e value) appears as an object key or array element at the top level of the \u003ccode\u003ejsonb\u003c/code\u003e value. These examples return true except as noted:\u003c/p\u003e\n\u003cpre\u003e-- String exists as array element:\nSELECT \u0026#39;[\u0026#34;foo\u0026#34;, \u0026#34;bar\u0026#34;, \u0026#34;baz\u0026#34;]\u0026#39;::jsonb ? \u0026#39;bar\u0026#39;;\n\n-- String exists as object key:\nSELECT \u0026#39;{\u0026#34;foo\u0026#34;: \u0026#34;bar\u0026#34;}\u0026#39;::jsonb ? \u0026#39;foo\u0026#39;;\n\n-- Object values are not considered:\nSELECT \u0026#39;{\u0026#34;foo\u0026#34;: \u0026#34;bar\u0026#34;}\u0026#39;::jsonb ? \u0026#39;bar\u0026#39;;  -- yields false\n\n-- As with containment, existence must match at the top level:\nSELECT \u0026#39;{\u0026#34;foo\u0026#34;: {\u0026#34;bar\u0026#34;: \u0026#34;baz\u0026#34;}}\u0026#39;::jsonb ? \u0026#39;bar\u0026#39;; -- yields false\n\n-- A string is considered to exist if it matches a primitive JSON string:\nSELECT \u0026#39;\u0026#34;foo\u0026#34;\u0026#39;::jsonb ? \u0026#39;foo\u0026#39;;\n\u003c/pre\u003e\n\u003cp\u003eJSON 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.\u003c/p\u003e\n\u003cdiv\u003e\n\u003ch3\u003eTip\u003c/h3\u003e\n\u003cp\u003eBecause JSON containment is nested, an appropriate query can skip explicit selection of sub-objects. As an example, suppose that we have a \u003ccode\u003edoc\u003c/code\u003e column containing objects at the top level, with most objects containing \u003ccode\u003etags\u003c/code\u003e fields that contain arrays of sub-objects. This query finds entries in which sub-objects containing both \u003ccode\u003e\u0026#34;term\u0026#34;:\u0026#34;paris\u0026#34;\u003c/code\u003e and \u003ccode\u003e\u0026#34;term\u0026#34;:\u0026#34;food\u0026#34;\u003c/code\u003e appear, while ignoring any such keys outside the \u003ccode\u003etags\u003c/code\u003e array:\u003c/p\u003e\n\u003cpre\u003eSELECT doc-\u0026gt;\u0026#39;site_name\u0026#39; FROM websites\n  WHERE doc @\u0026gt; \u0026#39;{\u0026#34;tags\u0026#34;:[{\u0026#34;term\u0026#34;:\u0026#34;paris\u0026#34;}, {\u0026#34;term\u0026#34;:\u0026#34;food\u0026#34;}]}\u0026#39;;\n\u003c/pre\u003e\n\u003cp\u003eOne could accomplish the same thing with, say,\u003c/p\u003e\n\u003cpre\u003eSELECT doc-\u0026gt;\u0026#39;site_name\u0026#39; FROM websites\n  WHERE doc-\u0026gt;\u0026#39;tags\u0026#39; @\u0026gt; \u0026#39;[{\u0026#34;term\u0026#34;:\u0026#34;paris\u0026#34;}, {\u0026#34;term\u0026#34;:\u0026#34;food\u0026#34;}]\u0026#39;;\n\u003c/pre\u003e\n\u003cp\u003ebut that approach is less flexible, and often less efficient as well.\u003c/p\u003e\n\u003cp\u003eOn 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.\u003c/p\u003e\n\u003c/div\u003e\n\u003cp\u003eThe various containment and existence operators, along with all other JSON operators and functions are documented in \u003ca href=\"/docs/18/functions-json.html\" rel=\"nofollow\"\u003eSection 9.16\u003c/a\u003e.\u003c/p\u003e\n\u003c/div\u003e\n\u003cdiv id=\"JSON-INDEXING\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3\u003e8.14.4. \u003ccode\u003ejsonb\u003c/code\u003e Indexing \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eGIN indexes can be used to efficiently search for keys or key/value pairs occurring within a large number of \u003ccode\u003ejsonb\u003c/code\u003e documents (datums). Two GIN \u003cspan\u003e“\u003cspan\u003eoperator classes\u003c/span\u003e”\u003c/span\u003e are provided, offering different performance and flexibility trade-offs.\u003c/p\u003e\n\u003cp\u003eThe default GIN operator class for \u003ccode\u003ejsonb\u003c/code\u003e supports queries with the key-exists operators \u003ccode\u003e?\u003c/code\u003e, \u003ccode\u003e?|\u003c/code\u003e and \u003ccode\u003e?\u0026amp;\u003c/code\u003e, the containment operator \u003ccode\u003e@\u0026gt;\u003c/code\u003e, and the \u003ccode\u003ejsonpath\u003c/code\u003e match operators \u003ccode\u003e@?\u003c/code\u003e and \u003ccode\u003e@@\u003c/code\u003e. (For details of the semantics that these operators implement, see \u003ca href=\"/docs/18/functions-json.html#FUNCTIONS-JSONB-OP-TABLE\" rel=\"nofollow\"\u003eTable 9.48\u003c/a\u003e.) An example of creating an index with this operator class is:\u003c/p\u003e\n\u003cpre\u003eCREATE INDEX idxgin ON api USING GIN (jdoc);\n\u003c/pre\u003e\n\u003cp\u003eThe non-default GIN operator class \u003ccode\u003ejsonb_path_ops\u003c/code\u003e does not support the key-exists operators, but it does support \u003ccode\u003e@\u0026gt;\u003c/code\u003e, \u003ccode\u003e@?\u003c/code\u003e and \u003ccode\u003e@@\u003c/code\u003e. An example of creating an index with this operator class is:\u003c/p\u003e\n\u003cpre\u003eCREATE INDEX idxginp ON api USING GIN (jdoc jsonb_path_ops);\n\u003c/pre\u003e\n\u003cp\u003eConsider 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:\u003c/p\u003e\n\u003cpre\u003e{\n    \u0026#34;guid\u0026#34;: \u0026#34;9c36adc1-7fb5-4d5b-83b4-90356a46061a\u0026#34;,\n    \u0026#34;name\u0026#34;: \u0026#34;Angela Barton\u0026#34;,\n    \u0026#34;is_active\u0026#34;: true,\n    \u0026#34;company\u0026#34;: \u0026#34;Magnafone\u0026#34;,\n    \u0026#34;address\u0026#34;: \u0026#34;178 Howard Place, Gulf, Washington, 702\u0026#34;,\n    \u0026#34;registered\u0026#34;: \u0026#34;2009-11-07T08:53:22 +08:00\u0026#34;,\n    \u0026#34;latitude\u0026#34;: 19.793713,\n    \u0026#34;longitude\u0026#34;: 86.513373,\n    \u0026#34;tags\u0026#34;: [\n        \u0026#34;enim\u0026#34;,\n        \u0026#34;aliquip\u0026#34;,\n        \u0026#34;qui\u0026#34;\n    ]\n}\n\u003c/pre\u003e\n\u003cp\u003eWe store these documents in a table named \u003ccode\u003eapi\u003c/code\u003e, in a \u003ccode\u003ejsonb\u003c/code\u003e column named \u003ccode\u003ejdoc\u003c/code\u003e. If a GIN index is created on this column, queries like the following can make use of the index:\u003c/p\u003e\n\u003cpre\u003e-- Find documents in which the key \u0026#34;company\u0026#34; has value \u0026#34;Magnafone\u0026#34;\nSELECT jdoc-\u0026gt;\u0026#39;guid\u0026#39;, jdoc-\u0026gt;\u0026#39;name\u0026#39; FROM api WHERE jdoc @\u0026gt; \u0026#39;{\u0026#34;company\u0026#34;: \u0026#34;Magnafone\u0026#34;}\u0026#39;;\n\u003c/pre\u003e\n\u003cp\u003eHowever, the index could not be used for queries like the following, because though the operator \u003ccode\u003e?\u003c/code\u003e is indexable, it is not applied directly to the indexed column \u003ccode\u003ejdoc\u003c/code\u003e:\u003c/p\u003e\n\u003cpre\u003e-- Find documents in which the key \u0026#34;tags\u0026#34; contains key or array element \u0026#34;qui\u0026#34;\nSELECT jdoc-\u0026gt;\u0026#39;guid\u0026#39;, jdoc-\u0026gt;\u0026#39;name\u0026#39; FROM api WHERE jdoc -\u0026gt; \u0026#39;tags\u0026#39; ? \u0026#39;qui\u0026#39;;\n\u003c/pre\u003e\n\u003cp\u003eStill, with appropriate use of expression indexes, the above query can use an index. If querying for particular items within the \u003ccode\u003e\u0026#34;tags\u0026#34;\u003c/code\u003e key is common, defining an index like this may be worthwhile:\u003c/p\u003e\n\u003cpre\u003eCREATE INDEX idxgintags ON api USING GIN ((jdoc -\u0026gt; \u0026#39;tags\u0026#39;));\n\u003c/pre\u003e\n\u003cp\u003eNow, the \u003ccode\u003eWHERE\u003c/code\u003e clause \u003ccode\u003ejdoc -\u0026gt; \u0026#39;tags\u0026#39; ? \u0026#39;qui\u0026#39;\u003c/code\u003e will be recognized as an application of the indexable operator \u003ccode\u003e?\u003c/code\u003e to the indexed expression \u003ccode\u003ejdoc -\u0026gt; \u0026#39;tags\u0026#39;\u003c/code\u003e. (More information on expression indexes can be found in \u003ca href=\"/docs/18/indexes-expressional.html\" rel=\"nofollow\"\u003eSection 11.7\u003c/a\u003e.)\u003c/p\u003e\n\u003cp\u003eAnother approach to querying is to exploit containment, for example:\u003c/p\u003e\n\u003cpre\u003e-- Find documents in which the key \u0026#34;tags\u0026#34; contains array element \u0026#34;qui\u0026#34;\nSELECT jdoc-\u0026gt;\u0026#39;guid\u0026#39;, jdoc-\u0026gt;\u0026#39;name\u0026#39; FROM api WHERE jdoc @\u0026gt; \u0026#39;{\u0026#34;tags\u0026#34;: [\u0026#34;qui\u0026#34;]}\u0026#39;;\n\u003c/pre\u003e\n\u003cp\u003eA simple GIN index on the \u003ccode\u003ejdoc\u003c/code\u003e column can support this query. But note that such an index will store copies of every key and value in the \u003ccode\u003ejdoc\u003c/code\u003e column, whereas the expression index of the previous example stores only data found under the \u003ccode\u003etags\u003c/code\u003e 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.\u003c/p\u003e\n\u003cp\u003eGIN indexes also support the \u003ccode\u003e@?\u003c/code\u003e and \u003ccode\u003e@@\u003c/code\u003e operators, which perform \u003ccode\u003ejsonpath\u003c/code\u003e matching. Examples are\u003c/p\u003e\n\u003cpre\u003eSELECT jdoc-\u0026gt;\u0026#39;guid\u0026#39;, jdoc-\u0026gt;\u0026#39;name\u0026#39; FROM api WHERE jdoc @? \u0026#39;$.tags[*] ? (@ == \u0026#34;qui\u0026#34;)\u0026#39;;\n\u003c/pre\u003e\n\u003cpre\u003eSELECT jdoc-\u0026gt;\u0026#39;guid\u0026#39;, jdoc-\u0026gt;\u0026#39;name\u0026#39; FROM api WHERE jdoc @@ \u0026#39;$.tags[*] == \u0026#34;qui\u0026#34;\u0026#39;;\n\u003c/pre\u003e\n\u003cp\u003eFor these operators, a GIN index extracts clauses of the form \u003ccode\u003e\u003cem\u003e\u003ccode\u003eaccessors_chain\u003c/code\u003e\u003c/em\u003e == \u003cem\u003e\u003ccode\u003econstant\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e out of the \u003ccode\u003ejsonpath\u003c/code\u003e pattern, and does the index search based on the keys and values mentioned in these clauses. The accessors chain may include \u003ccode\u003e.\u003cem\u003e\u003ccode\u003ekey\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e, \u003ccode\u003e[*]\u003c/code\u003e, and \u003ccode\u003e[\u003cem\u003e\u003ccode\u003eindex\u003c/code\u003e\u003c/em\u003e]\u003c/code\u003e accessors. The \u003ccode\u003ejsonb_ops\u003c/code\u003e operator class also supports \u003ccode\u003e.*\u003c/code\u003e and \u003ccode\u003e.**\u003c/code\u003e accessors, but the \u003ccode\u003ejsonb_path_ops\u003c/code\u003e operator class does not.\u003c/p\u003e\n\u003cp\u003eAlthough the \u003ccode\u003ejsonb_path_ops\u003c/code\u003e operator class supports only queries with the \u003ccode\u003e@\u0026gt;\u003c/code\u003e, \u003ccode\u003e@?\u003c/code\u003e and \u003ccode\u003e@@\u003c/code\u003e operators, it has notable performance advantages over the default operator class \u003ccode\u003ejsonb_ops\u003c/code\u003e. A \u003ccode\u003ejsonb_path_ops\u003c/code\u003e index is usually much smaller than a \u003ccode\u003ejsonb_ops\u003c/code\u003e 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.\u003c/p\u003e\n\u003cp\u003eThe technical difference between a \u003ccode\u003ejsonb_ops\u003c/code\u003e and a \u003ccode\u003ejsonb_path_ops\u003c/code\u003e 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. \u003ca href=\"/docs/18/datatype-json.html#ftn.id-1.5.7.22.18.9.3\" rel=\"nofollow\"\u003e\u003csup id=\"id-1.5.7.22.18.9.3\"\u003e[7]\u003c/sup\u003e\u003c/a\u003e Basically, each \u003ccode\u003ejsonb_path_ops\u003c/code\u003e index item is a hash of the value and the key(s) leading to it; for example to index \u003ccode\u003e{\u0026#34;foo\u0026#34;: {\u0026#34;bar\u0026#34;: \u0026#34;baz\u0026#34;}}\u003c/code\u003e, a single index item would be created incorporating all three of \u003ccode\u003efoo\u003c/code\u003e, \u003ccode\u003ebar\u003c/code\u003e, and \u003ccode\u003ebaz\u003c/code\u003e 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 \u003ccode\u003efoo\u003c/code\u003e appears as a key. On the other hand, a \u003ccode\u003ejsonb_ops\u003c/code\u003e index would create three index items representing \u003ccode\u003efoo\u003c/code\u003e, \u003ccode\u003ebar\u003c/code\u003e, and \u003ccode\u003ebaz\u003c/code\u003e 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 \u003ccode\u003ejsonb_path_ops\u003c/code\u003e search, especially if there are a very large number of rows containing any single one of the three index items.\u003c/p\u003e\n\u003cp\u003eA disadvantage of the \u003ccode\u003ejsonb_path_ops\u003c/code\u003e approach is that it produces no index entries for JSON structures not containing any values, such as \u003ccode\u003e{\u0026#34;a\u0026#34;: {}}\u003c/code\u003e. If a search for documents containing such a structure is requested, it will require a full-index scan, which is quite slow. \u003ccode\u003ejsonb_path_ops\u003c/code\u003e is therefore ill-suited for applications that often perform such searches.\u003c/p\u003e\n\u003cp\u003e\u003ccode\u003ejsonb\u003c/code\u003e also supports \u003ccode\u003ebtree\u003c/code\u003e and \u003ccode\u003ehash\u003c/code\u003e indexes. These are usually useful only if it\u0026#39;s important to check equality of complete JSON documents. The \u003ccode\u003ebtree\u003c/code\u003e ordering for \u003ccode\u003ejsonb\u003c/code\u003e datums is seldom of great interest, but for completeness it is:\u003c/p\u003e\n\u003cpre\u003e\u003cem\u003e\u003ccode\u003eObject\u003c/code\u003e\u003c/em\u003e \u0026gt; \u003cem\u003e\u003ccode\u003eArray\u003c/code\u003e\u003c/em\u003e \u0026gt; \u003cem\u003e\u003ccode\u003eBoolean\u003c/code\u003e\u003c/em\u003e \u0026gt; \u003cem\u003e\u003ccode\u003eNumber\u003c/code\u003e\u003c/em\u003e \u0026gt; \u003cem\u003e\u003ccode\u003eString\u003c/code\u003e\u003c/em\u003e \u0026gt; \u003cem\u003e\u003ccode\u003enull\u003c/code\u003e\u003c/em\u003e\n\n\u003cem\u003e\u003ccode\u003eObject with n pairs\u003c/code\u003e\u003c/em\u003e \u0026gt; \u003cem\u003e\u003ccode\u003eobject with n - 1 pairs\u003c/code\u003e\u003c/em\u003e\n\n\u003cem\u003e\u003ccode\u003eArray with n elements\u003c/code\u003e\u003c/em\u003e \u0026gt; \u003cem\u003e\u003ccode\u003earray with n - 1 elements\u003c/code\u003e\u003c/em\u003e\n\u003c/pre\u003e\n\u003cp\u003ewith the exception that (for historical reasons) an empty top level array sorts less than \u003cem\u003e\u003ccode\u003enull\u003c/code\u003e\u003c/em\u003e. Objects with equal numbers of pairs are compared in the order:\u003c/p\u003e\n\u003cpre\u003e\u003cem\u003e\u003ccode\u003ekey-1\u003c/code\u003e\u003c/em\u003e, \u003cem\u003e\u003ccode\u003evalue-1\u003c/code\u003e\u003c/em\u003e, \u003cem\u003e\u003ccode\u003ekey-2\u003c/code\u003e\u003c/em\u003e ...\n\u003c/pre\u003e\n\u003cp\u003eNote 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:\u003c/p\u003e\n\u003cpre\u003e{ \u0026#34;aa\u0026#34;: 1, \u0026#34;c\u0026#34;: 1} \u0026gt; {\u0026#34;b\u0026#34;: 1, \u0026#34;d\u0026#34;: 1}\n\u003c/pre\u003e\n\u003cp\u003eSimilarly, arrays with equal numbers of elements are compared in the order:\u003c/p\u003e\n\u003cpre\u003e\u003cem\u003e\u003ccode\u003eelement-1\u003c/code\u003e\u003c/em\u003e, \u003cem\u003e\u003ccode\u003eelement-2\u003c/code\u003e\u003c/em\u003e ...\n\u003c/pre\u003e\n\u003cp\u003ePrimitive JSON values are compared using the same comparison rules as for the underlying \u003cspan\u003ePostgreSQL\u003c/span\u003e data type. Strings are compared using the default database collation.\u003c/p\u003e\n\u003c/div\u003e\n\u003cdiv id=\"JSONB-SUBSCRIPTING\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3\u003e8.14.5. \u003ccode\u003ejsonb\u003c/code\u003e Subscripting \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eThe \u003ccode\u003ejsonb\u003c/code\u003e 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 \u003ccode\u003epath\u003c/code\u003e argument in the \u003ccode\u003ejsonb_set\u003c/code\u003e function. If a \u003ccode\u003ejsonb\u003c/code\u003e 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.\u003c/p\u003e\n\u003cp\u003e\u003ccode\u003eUPDATE\u003c/code\u003e statements may use subscripting in the \u003ccode\u003eSET\u003c/code\u003e clause to modify \u003ccode\u003ejsonb\u003c/code\u003e values. Subscript paths must be traversable for all affected values insofar as they exist. For instance, the path \u003ccode\u003eval[\u0026#39;a\u0026#39;][\u0026#39;b\u0026#39;][\u0026#39;c\u0026#39;]\u003c/code\u003e can be traversed all the way to \u003ccode\u003ec\u003c/code\u003e if every \u003ccode\u003eval\u003c/code\u003e, \u003ccode\u003eval[\u0026#39;a\u0026#39;]\u003c/code\u003e, and \u003ccode\u003eval[\u0026#39;a\u0026#39;][\u0026#39;b\u0026#39;]\u003c/code\u003e is an object. If any \u003ccode\u003eval[\u0026#39;a\u0026#39;]\u003c/code\u003e or \u003ccode\u003eval[\u0026#39;a\u0026#39;][\u0026#39;b\u0026#39;]\u003c/code\u003e is not defined, it will be created as an empty object and filled as necessary. However, if any \u003ccode\u003eval\u003c/code\u003e itself or one of the intermediary values is defined as a non-object such as a string, number, or \u003ccode\u003ejsonb\u003c/code\u003e \u003ccode\u003enull\u003c/code\u003e, traversal cannot proceed so an error is raised and the transaction aborted.\u003c/p\u003e\n\u003cp\u003eAn example of subscripting syntax:\u003c/p\u003e\n\u003cpre\u003e\n-- Extract object value by key\nSELECT (\u0026#39;{\u0026#34;a\u0026#34;: 1}\u0026#39;::jsonb)[\u0026#39;a\u0026#39;];\n\n-- Extract nested object value by key path\nSELECT (\u0026#39;{\u0026#34;a\u0026#34;: {\u0026#34;b\u0026#34;: {\u0026#34;c\u0026#34;: 1}}}\u0026#39;::jsonb)[\u0026#39;a\u0026#39;][\u0026#39;b\u0026#39;][\u0026#39;c\u0026#39;];\n\n-- Extract array element by index\nSELECT (\u0026#39;[1, \u0026#34;2\u0026#34;, null]\u0026#39;::jsonb)[1];\n\n-- Update object value by key. Note the quotes around \u0026#39;1\u0026#39;: the assigned\n-- value must be of the jsonb type as well\nUPDATE table_name SET jsonb_field[\u0026#39;key\u0026#39;] = \u0026#39;1\u0026#39;;\n\n-- This will raise an error if any record\u0026#39;s jsonb_field[\u0026#39;a\u0026#39;][\u0026#39;b\u0026#39;] is something\n-- other than an object. For example, the value {\u0026#34;a\u0026#34;: 1} has a numeric value\n-- of the key \u0026#39;a\u0026#39;.\nUPDATE table_name SET jsonb_field[\u0026#39;a\u0026#39;][\u0026#39;b\u0026#39;][\u0026#39;c\u0026#39;] = \u0026#39;1\u0026#39;;\n\n-- Filter records using a WHERE clause with subscripting. Since the result of\n-- subscripting is jsonb, the value we compare it against must also be jsonb.\n-- The double quotes make \u0026#34;value\u0026#34; also a valid jsonb string.\nSELECT * FROM table_name WHERE jsonb_field[\u0026#39;key\u0026#39;] = \u0026#39;\u0026#34;value\u0026#34;\u0026#39;;\n\u003c/pre\u003e\n\u003cp\u003e\u003ccode\u003ejsonb\u003c/code\u003e assignment via subscripting handles a few edge cases differently from \u003ccode\u003ejsonb_set\u003c/code\u003e. When a source \u003ccode\u003ejsonb\u003c/code\u003e value is \u003ccode\u003eNULL\u003c/code\u003e, assignment via subscripting will proceed as if it was an empty JSON value of the type (object or array) implied by the subscript key:\u003c/p\u003e\n\u003cpre\u003e-- Where jsonb_field was NULL, it is now {\u0026#34;a\u0026#34;: 1}\nUPDATE table_name SET jsonb_field[\u0026#39;a\u0026#39;] = \u0026#39;1\u0026#39;;\n\n-- Where jsonb_field was NULL, it is now [1]\nUPDATE table_name SET jsonb_field[0] = \u0026#39;1\u0026#39;;\n\u003c/pre\u003e\n\u003cp\u003eIf an index is specified for an array containing too few elements, \u003ccode\u003eNULL\u003c/code\u003e elements will be appended until the index is reachable and the value can be set.\u003c/p\u003e\n\u003cpre\u003e-- Where jsonb_field was [], it is now [null, null, 2];\n-- where jsonb_field was [0], it is now [0, null, 2]\nUPDATE table_name SET jsonb_field[2] = \u0026#39;2\u0026#39;;\n\u003c/pre\u003e\n\u003cp\u003eA \u003ccode\u003ejsonb\u003c/code\u003e 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 \u003ccode\u003enull\u003c/code\u003e-padded, as specified by the subscript path until the assigned value can be placed.\u003c/p\u003e\n\u003cpre\u003e-- Where jsonb_field was {}, it is now {\u0026#34;a\u0026#34;: [{\u0026#34;b\u0026#34;: 1}]}\nUPDATE table_name SET jsonb_field[\u0026#39;a\u0026#39;][0][\u0026#39;b\u0026#39;] = \u0026#39;1\u0026#39;;\n\n-- Where jsonb_field was [], it is now [null, {\u0026#34;a\u0026#34;: 1}]\nUPDATE table_name SET jsonb_field[1][\u0026#39;a\u0026#39;] = \u0026#39;1\u0026#39;;\n\u003c/pre\u003e\n\u003c/div\u003e\n\u003cdiv id=\"DATATYPE-JSON-TRANSFORMS\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3\u003e8.14.6. Transforms \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eAdditional extensions are available that implement transforms for the \u003ccode\u003ejsonb\u003c/code\u003e type for different procedural languages.\u003c/p\u003e\n\u003cp\u003eThe extensions for PL/Perl are called \u003ccode\u003ejsonb_plperl\u003c/code\u003e and \u003ccode\u003ejsonb_plperlu\u003c/code\u003e. If you use them, \u003ccode\u003ejsonb\u003c/code\u003e values are mapped to Perl arrays, hashes, and scalars, as appropriate.\u003c/p\u003e\n\u003cp\u003eThe extension for PL/Python is called \u003ccode\u003ejsonb_plpython3u\u003c/code\u003e. If you use it, \u003ccode\u003ejsonb\u003c/code\u003e values are mapped to Python dictionaries, lists, and scalars, as appropriate.\u003c/p\u003e\n\u003cp\u003eOf these extensions, \u003ccode\u003ejsonb_plperl\u003c/code\u003e is considered \u003cspan\u003e“\u003cspan\u003etrusted\u003c/span\u003e”\u003c/span\u003e, that is, it can be installed by non-superusers who have \u003ccode\u003eCREATE\u003c/code\u003e privilege on the current database. The rest require superuser privilege to install.\u003c/p\u003e\n\u003c/div\u003e\n\u003cdiv id=\"DATATYPE-JSONPATH\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3\u003e8.14.7. jsonpath Type \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eThe \u003ccode\u003ejsonpath\u003c/code\u003e type implements support for the SQL/JSON path language in \u003cspan\u003ePostgreSQL\u003c/span\u003e 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.\u003c/p\u003e\n\u003cp\u003eThe 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:\u003c/p\u003e\n\u003cdiv\u003e\n\u003cul\u003e\n\u003cli\u003e\n\u003cp\u003eDot (\u003ccode\u003e.\u003c/code\u003e) is used for member access.\u003c/p\u003e\n\u003c/li\u003e\n\u003cli\u003e\n\u003cp\u003eSquare brackets (\u003ccode\u003e[]\u003c/code\u003e) are used for array access.\u003c/p\u003e\n\u003c/li\u003e\n\u003cli\u003e\n\u003cp\u003eSQL/JSON arrays are 0-relative, unlike regular SQL arrays that start from 1.\u003c/p\u003e\n\u003c/li\u003e\n\u003c/ul\u003e\n\u003c/div\u003e\n\u003cp\u003eNumeric literals in SQL/JSON path expressions follow JavaScript rules, which are different from both SQL and JSON in some minor details. For example, SQL/JSON path allows \u003ccode\u003e.1\u003c/code\u003e and \u003ccode\u003e1.\u003c/code\u003e, which are invalid in JSON. Non-decimal integer literals and underscore separators are supported, for example, \u003ccode\u003e1_000_000\u003c/code\u003e, \u003ccode\u003e0x1EEE_FFFF\u003c/code\u003e, \u003ccode\u003e0o273\u003c/code\u003e, \u003ccode\u003e0b100101\u003c/code\u003e. In SQL/JSON path (and in JavaScript, but not in SQL proper), there must not be an underscore separator directly after the radix prefix.\u003c/p\u003e\n\u003cp\u003eAn 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 \u003ca href=\"/docs/18/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS\" rel=\"nofollow\"\u003eSection 4.1.2.1\u003c/a\u003e). 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 \u003ccode\u003e\\\u0026#34;\u003c/code\u003e, and to write a backslash itself, you must write \u003ccode\u003e\\\\\u003c/code\u003e. Other special backslash sequences include those recognized in JavaScript strings: \u003ccode\u003e\\b\u003c/code\u003e, \u003ccode\u003e\\f\u003c/code\u003e, \u003ccode\u003e\\n\u003c/code\u003e, \u003ccode\u003e\\r\u003c/code\u003e, \u003ccode\u003e\\t\u003c/code\u003e, \u003ccode\u003e\\v\u003c/code\u003e for various ASCII control characters, \u003ccode\u003e\\x\u003cem\u003e\u003ccode\u003eNN\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e for a character code written with only two hex digits, \u003ccode\u003e\\u\u003cem\u003e\u003ccode\u003eNNNN\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e for a Unicode character identified by its 4-hex-digit code point, and \u003ccode\u003e\\u{\u003cem\u003e\u003ccode\u003eN...\u003c/code\u003e\u003c/em\u003e}\u003c/code\u003e for a Unicode character code point written with 1 to 6 hex digits.\u003c/p\u003e\n\u003cp\u003eA path expression consists of a sequence of path elements, which can be any of the following:\u003c/p\u003e\n\u003cdiv\u003e\n\u003cul\u003e\n\u003cli\u003e\n\u003cp\u003ePath literals of JSON primitive types: Unicode text, numeric, true, false, or null.\u003c/p\u003e\n\u003c/li\u003e\n\u003cli\u003e\n\u003cp\u003ePath variables listed in \u003ca href=\"/docs/18/datatype-json.html#TYPE-JSONPATH-VARIABLES\" rel=\"nofollow\"\u003eTable 8.24\u003c/a\u003e.\u003c/p\u003e\n\u003c/li\u003e\n\u003cli\u003e\n\u003cp\u003eAccessor operators listed in \u003ca href=\"/docs/18/datatype-json.html#TYPE-JSONPATH-ACCESSORS\" rel=\"nofollow\"\u003eTable 8.25\u003c/a\u003e.\u003c/p\u003e\n\u003c/li\u003e\n\u003cli\u003e\n\u003cp\u003e\u003ccode\u003ejsonpath\u003c/code\u003e operators and methods listed in \u003ca href=\"/docs/18/functions-json.html#FUNCTIONS-SQLJSON-PATH-OPERATORS\" rel=\"nofollow\"\u003eSection 9.16.2.3\u003c/a\u003e.\u003c/p\u003e\n\u003c/li\u003e\n\u003cli\u003e\n\u003cp\u003eParentheses, which can be used to provide filter expressions or define the order of path evaluation.\u003c/p\u003e\n\u003c/li\u003e\n\u003c/ul\u003e\n\u003c/div\u003e\n\u003cp\u003eFor details on using \u003ccode\u003ejsonpath\u003c/code\u003e expressions with SQL/JSON query functions, see \u003ca href=\"/docs/18/functions-json.html#FUNCTIONS-SQLJSON-PATH\" rel=\"nofollow\"\u003eSection 9.16.2\u003c/a\u003e.\u003c/p\u003e\n\u003cdiv id=\"TYPE-JSONPATH-VARIABLES\"\u003e\n\u003cp\u003e\u003cstrong\u003eTable 8.24. \u003ccode\u003ejsonpath\u003c/code\u003e Variables\u003c/strong\u003e\u003c/p\u003e\n\u003cdiv\u003e\n\u003ctable\u003e\n\n\n\n\n\u003cthead\u003e\n\u003ctr\u003e\n\u003cth\u003eVariable\u003c/th\u003e\n\u003cth\u003eDescription\u003c/th\u003e\n\u003c/tr\u003e\n\u003c/thead\u003e\n\u003ctbody\u003e\n\u003ctr\u003e\n\u003ctd\u003e\u003ccode\u003e$\u003c/code\u003e\u003c/td\u003e\n\u003ctd\u003eA variable representing the JSON value being queried (the \u003cem\u003econtext item\u003c/em\u003e).\u003c/td\u003e\n\u003c/tr\u003e\n\u003ctr\u003e\n\u003ctd\u003e\u003ccode\u003e$varname\u003c/code\u003e\u003c/td\u003e\n\u003ctd\u003eA named variable. Its value can be set by the parameter \u003cem\u003e\u003ccode\u003evars\u003c/code\u003e\u003c/em\u003e of several JSON processing functions; see \u003ca href=\"/docs/18/functions-json.html#FUNCTIONS-JSON-PROCESSING-TABLE\" rel=\"nofollow\"\u003eTable 9.51\u003c/a\u003e for details.\u003c/td\u003e\n\u003c/tr\u003e\n\u003ctr\u003e\n\u003ctd\u003e\u003ccode\u003e@\u003c/code\u003e\u003c/td\u003e\n\u003ctd\u003eA variable representing the result of path evaluation in filter expressions.\u003c/td\u003e\n\u003c/tr\u003e\n\u003c/tbody\u003e\n\u003c/table\u003e\n\u003c/div\u003e\n\u003c/div\u003e\u003cbr\u003e\n\u003cdiv id=\"TYPE-JSONPATH-ACCESSORS\"\u003e\n\u003cp\u003e\u003cstrong\u003eTable 8.25. \u003ccode\u003ejsonpath\u003c/code\u003e Accessors\u003c/strong\u003e\u003c/p\u003e\n\u003cdiv\u003e\n\u003ctable\u003e\n\n\n\n\n\u003cthead\u003e\n\u003ctr\u003e\n\u003cth\u003eAccessor Operator\u003c/th\u003e\n\u003cth\u003eDescription\u003c/th\u003e\n\u003c/tr\u003e\n\u003c/thead\u003e\n\u003ctbody\u003e\n\u003ctr\u003e\n\u003ctd\u003e\n\u003cp\u003e\u003ccode\u003e.\u003cem\u003e\u003ccode\u003ekey\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e\u003c/p\u003e\n\u003cp\u003e\u003ccode\u003e.\u0026#34;$\u003cem\u003e\u003ccode\u003evarname\u003c/code\u003e\u003c/em\u003e\u0026#34;\u003c/code\u003e\u003c/p\u003e\n\u003c/td\u003e\n\u003ctd\u003e\n\u003cp\u003eMember accessor that returns an object member with the specified key. If the key name matches some named variable starting with \u003ccode\u003e$\u003c/code\u003e or does not meet the JavaScript rules for an identifier, it must be enclosed in double quotes to make it a string literal.\u003c/p\u003e\n\u003c/td\u003e\n\u003c/tr\u003e\n\u003ctr\u003e\n\u003ctd\u003e\n\u003cp\u003e\u003ccode\u003e.*\u003c/code\u003e\u003c/p\u003e\n\u003c/td\u003e\n\u003ctd\u003e\n\u003cp\u003eWildcard member accessor that returns the values of all members located at the top level of the current object.\u003c/p\u003e\n\u003c/td\u003e\n\u003c/tr\u003e\n\u003ctr\u003e\n\u003ctd\u003e\n\u003cp\u003e\u003ccode\u003e.**\u003c/code\u003e\u003c/p\u003e\n\u003c/td\u003e\n\u003ctd\u003e\n\u003cp\u003eRecursive 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 \u003cspan\u003ePostgreSQL\u003c/span\u003e extension of the SQL/JSON standard.\u003c/p\u003e\n\u003c/td\u003e\n\u003c/tr\u003e\n\u003ctr\u003e\n\u003ctd\u003e\n\u003cp\u003e\u003ccode\u003e.**{\u003cem\u003e\u003ccode\u003elevel\u003c/code\u003e\u003c/em\u003e}\u003c/code\u003e\u003c/p\u003e\n\u003cp\u003e\u003ccode\u003e.**{\u003cem\u003e\u003ccode\u003estart_level\u003c/code\u003e\u003c/em\u003e to \u003cem\u003e\u003ccode\u003eend_level\u003c/code\u003e\u003c/em\u003e}\u003c/code\u003e\u003c/p\u003e\n\u003c/td\u003e\n\u003ctd\u003e\n\u003cp\u003eLike \u003ccode\u003e.**\u003c/code\u003e, 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 \u003ccode\u003elast\u003c/code\u003e keyword. This is a \u003cspan\u003ePostgreSQL\u003c/span\u003e extension of the SQL/JSON standard.\u003c/p\u003e\n\u003c/td\u003e\n\u003c/tr\u003e\n\u003ctr\u003e\n\u003ctd\u003e\n\u003cp\u003e\u003ccode\u003e[\u003cem\u003e\u003ccode\u003esubscript\u003c/code\u003e\u003c/em\u003e, ...]\u003c/code\u003e\u003c/p\u003e\n\u003c/td\u003e\n\u003ctd\u003e\n\u003cp\u003eArray element accessor. \u003ccode\u003e\u003cem\u003e\u003ccode\u003esubscript\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e can be given in two forms: \u003ccode\u003e\u003cem\u003e\u003ccode\u003eindex\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e or \u003ccode\u003e\u003cem\u003e\u003ccode\u003estart_index\u003c/code\u003e\u003c/em\u003e to \u003cem\u003e\u003ccode\u003eend_index\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e. 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 \u003cem\u003e\u003ccode\u003estart_index\u003c/code\u003e\u003c/em\u003e and \u003cem\u003e\u003ccode\u003eend_index\u003c/code\u003e\u003c/em\u003e.\u003c/p\u003e\n\u003cp\u003eThe specified \u003cem\u003e\u003ccode\u003eindex\u003c/code\u003e\u003c/em\u003e 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 \u003ccode\u003elast\u003c/code\u003e keyword to denote the last array element, which is useful for handling arrays of unknown length.\u003c/p\u003e\n\u003c/td\u003e\n\u003c/tr\u003e\n\u003ctr\u003e\n\u003ctd\u003e\n\u003cp\u003e\u003ccode\u003e[*]\u003c/code\u003e\u003c/p\u003e\n\u003c/td\u003e\n\u003ctd\u003e\n\u003cp\u003eWildcard array element accessor that returns all array elements.\u003c/p\u003e\n\u003c/td\u003e\n\u003c/tr\u003e\n\u003c/tbody\u003e\n\u003c/table\u003e\n\u003c/div\u003e\n\u003c/div\u003e\u003cbr\u003e\n\u003c/div\u003e\n\u003cdiv\u003e\n\u003cbr\u003e\n\n\n\u003cdiv id=\"ftn.id-1.5.7.22.18.9.3\"\u003e\n\u003cp\u003e\u003ca href=\"/docs/18/datatype-json.html#id-1.5.7.22.18.9.3\" rel=\"nofollow\"\u003e\u003csup\u003e[7]\u003c/sup\u003e\u003c/a\u003e For this purpose, the term \u003cspan\u003e“\u003cspan\u003evalue\u003c/span\u003e”\u003c/span\u003e includes array elements, though JSON terminology sometimes considers array elements distinct from values within objects.\u003c/p\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e","SourceRevision":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","ContentHash":"a29622a24799052fd6963e8f964710bb0e2aa67b314cf8fbe77f5b265dae5da3","Payload":{"description":["binary JSON data, decomposed"],"manual_html":"\u003cdiv class=\"sect1\" id=\"DATATYPE-JSON\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch2 class=\"title\"\u003e8.14. JSON Types \u003c/h2\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\n\u003cp\u003eJSON data types are for storing JSON (JavaScript Object Notation) data, as specified in \u003ca class=\"ulink\" href=\"https://datatracker.ietf.org/doc/html/rfc7159\"\u003eRFC 7159\u003c/a\u003e. Such data can also be stored as \u003ccode class=\"type\"\u003etext\u003c/code\u003e, 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 \u003ca class=\"xref\" href=\"/docs/18/functions-json.html\" title=\"9.16. JSON Functions and Operators\"\u003eSection 9.16\u003c/a\u003e.\u003c/p\u003e\n\u003cp\u003e\u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e offers two types for storing JSON data: \u003ccode class=\"type\"\u003ejson\u003c/code\u003e and \u003ccode class=\"type\"\u003ejsonb\u003c/code\u003e. To implement efficient query mechanisms for these data types, \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e also provides the \u003ccode class=\"type\"\u003ejsonpath\u003c/code\u003e data type described in \u003ca class=\"xref\" href=\"/docs/18/datatype-json.html#DATATYPE-JSONPATH\" title=\"8.14.7. jsonpath Type\"\u003eSection 8.14.7\u003c/a\u003e.\u003c/p\u003e\n\u003cp\u003eThe \u003ccode class=\"type\"\u003ejson\u003c/code\u003e and \u003ccode class=\"type\"\u003ejsonb\u003c/code\u003e data types accept \u003cspan class=\"emphasis\"\u003e\u003cem\u003ealmost\u003c/em\u003e\u003c/span\u003e identical sets of values as input. The major practical difference is one of efficiency. The \u003ccode class=\"type\"\u003ejson\u003c/code\u003e data type stores an exact copy of the input text, which processing functions must reparse on each execution; while \u003ccode class=\"type\"\u003ejsonb\u003c/code\u003e 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. \u003ccode class=\"type\"\u003ejsonb\u003c/code\u003e also supports indexing, which can be a significant advantage.\u003c/p\u003e\n\u003cp\u003eBecause the \u003ccode class=\"type\"\u003ejson\u003c/code\u003e 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, \u003ccode class=\"type\"\u003ejsonb\u003c/code\u003e 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.\u003c/p\u003e\n\u003cp\u003eIn general, most applications should prefer to store JSON data as \u003ccode class=\"type\"\u003ejsonb\u003c/code\u003e, unless there are quite specialized needs, such as legacy assumptions about ordering of object keys.\u003c/p\u003e\n\u003cp\u003eRFC 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.\u003c/p\u003e\n\u003cp\u003eRFC 7159 permits JSON strings to contain Unicode escape sequences denoted by \u003ccode class=\"literal\"\u003e\\u\u003cem class=\"replaceable\"\u003e\u003ccode\u003eXXXX\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e. In the input function for the \u003ccode class=\"type\"\u003ejson\u003c/code\u003e type, Unicode escapes are allowed regardless of the database encoding, and are checked only for syntactic correctness (that is, that four hex digits follow \u003ccode class=\"literal\"\u003e\\u\u003c/code\u003e). However, the input function for \u003ccode class=\"type\"\u003ejsonb\u003c/code\u003e is stricter: it disallows Unicode escapes for characters that cannot be represented in the database encoding. The \u003ccode class=\"type\"\u003ejsonb\u003c/code\u003e type also rejects \u003ccode class=\"literal\"\u003e\\u0000\u003c/code\u003e (because that cannot be represented in \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e's \u003ccode class=\"type\"\u003etext\u003c/code\u003e 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.\u003c/p\u003e\n\u003cdiv class=\"note\"\u003e\n\u003ch3 class=\"title\"\u003eNote\u003c/h3\u003e\n\u003cp\u003eMany of the JSON processing functions described in \u003ca class=\"xref\" href=\"/docs/18/functions-json.html\" title=\"9.16. JSON Functions and Operators\"\u003eSection 9.16\u003c/a\u003e 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 \u003ccode class=\"type\"\u003ejson\u003c/code\u003e not \u003ccode class=\"type\"\u003ejsonb\u003c/code\u003e. The fact that the \u003ccode class=\"type\"\u003ejson\u003c/code\u003e 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.\u003c/p\u003e\n\u003c/div\u003e\n\u003cp\u003eWhen converting textual JSON input into \u003ccode class=\"type\"\u003ejsonb\u003c/code\u003e, the primitive types described by RFC 7159 are effectively mapped onto native \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e types, as shown in \u003ca class=\"xref\" href=\"/docs/18/datatype-json.html#JSON-TYPE-MAPPING-TABLE\" title=\"Table 8.23. JSON Primitive Types and Corresponding PostgreSQL Types\"\u003eTable 8.23\u003c/a\u003e. Therefore, there are some minor additional constraints on what constitutes valid \u003ccode class=\"type\"\u003ejsonb\u003c/code\u003e data that do not apply to the \u003ccode class=\"type\"\u003ejson\u003c/code\u003e type, nor to JSON in the abstract, corresponding to limits on what can be represented by the underlying data type. Notably, \u003ccode class=\"type\"\u003ejsonb\u003c/code\u003e will reject numbers that are outside the range of the \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e \u003ccode class=\"type\"\u003enumeric\u003c/code\u003e data type, while \u003ccode class=\"type\"\u003ejson\u003c/code\u003e will not. Such implementation-defined restrictions are permitted by RFC 7159. However, in practice such problems are far more likely to occur in other implementations, as it is common to represent JSON's \u003ccode class=\"type\"\u003enumber\u003c/code\u003e primitive type as IEEE 754 double precision floating point (which RFC 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 \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e should be considered.\u003c/p\u003e\n\u003cp\u003eConversely, 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 \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e types.\u003c/p\u003e\n\u003cdiv class=\"table\" id=\"JSON-TYPE-MAPPING-TABLE\"\u003e\n\u003cp class=\"title\"\u003e\u003cstrong\u003eTable 8.23. JSON Primitive Types and Corresponding \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e Types\u003c/strong\u003e\u003c/p\u003e\n\u003cdiv class=\"table-contents\"\u003e\n\u003ctable class=\"table\"\u003e\n\n\n\n\n\n\u003cthead\u003e\n\u003ctr\u003e\n\u003cth\u003eJSON primitive type\u003c/th\u003e\n\u003cth\u003e\u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e type\u003c/th\u003e\n\u003cth\u003eNotes\u003c/th\u003e\n\u003c/tr\u003e\n\u003c/thead\u003e\n\u003ctbody\u003e\n\u003ctr\u003e\n\u003ctd\u003e\u003ccode class=\"type\"\u003estring\u003c/code\u003e\u003c/td\u003e\n\u003ctd\u003e\u003ccode class=\"type\"\u003etext\u003c/code\u003e\u003c/td\u003e\n\u003ctd\u003e\u003ccode class=\"literal\"\u003e\\u0000\u003c/code\u003e is disallowed, as are Unicode escapes representing characters not available in the database encoding\u003c/td\u003e\n\u003c/tr\u003e\n\u003ctr\u003e\n\u003ctd\u003e\u003ccode class=\"type\"\u003enumber\u003c/code\u003e\u003c/td\u003e\n\u003ctd\u003e\u003ccode class=\"type\"\u003enumeric\u003c/code\u003e\u003c/td\u003e\n\u003ctd\u003e\u003ccode class=\"literal\"\u003eNaN\u003c/code\u003e and \u003ccode class=\"literal\"\u003einfinity\u003c/code\u003e values are disallowed\u003c/td\u003e\n\u003c/tr\u003e\n\u003ctr\u003e\n\u003ctd\u003e\u003ccode class=\"type\"\u003eboolean\u003c/code\u003e\u003c/td\u003e\n\u003ctd\u003e\u003ccode class=\"type\"\u003eboolean\u003c/code\u003e\u003c/td\u003e\n\u003ctd\u003eOnly lowercase \u003ccode class=\"literal\"\u003etrue\u003c/code\u003e and \u003ccode class=\"literal\"\u003efalse\u003c/code\u003e spellings are accepted\u003c/td\u003e\n\u003c/tr\u003e\n\u003ctr\u003e\n\u003ctd\u003e\u003ccode class=\"type\"\u003enull\u003c/code\u003e\u003c/td\u003e\n\u003ctd\u003e(none)\u003c/td\u003e\n\u003ctd\u003eSQL \u003ccode class=\"literal\"\u003eNULL\u003c/code\u003e is a different concept\u003c/td\u003e\n\u003c/tr\u003e\n\u003c/tbody\u003e\n\u003c/table\u003e\n\u003c/div\u003e\n\u003c/div\u003e\u003cbr class=\"table-break\"\u003e\n\u003cdiv class=\"sect2\" id=\"JSON-KEYS-ELEMENTS\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3 class=\"title\"\u003e8.14.1. JSON Input and Output Syntax \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eThe input/output syntax for the JSON data types is as specified in RFC 7159.\u003c/p\u003e\n\u003cp\u003eThe following are all valid \u003ccode class=\"type\"\u003ejson\u003c/code\u003e (or \u003ccode class=\"type\"\u003ejsonb\u003c/code\u003e) expressions:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003e-- Simple scalar/primitive value\n-- Primitive values can be numbers, quoted strings, true, false, or null\nSELECT '5'::json;\n\n-- Array of zero or more elements (elements need not be of same type)\nSELECT '[1, 2, \"foo\", null]'::json;\n\n-- Object containing pairs of keys and values\n-- Note that object keys must always be quoted strings\nSELECT '{\"bar\": \"baz\", \"balance\": 7.77, \"active\": false}'::json;\n\n-- Arrays and objects can be nested arbitrarily\nSELECT '{\"foo\": [true, \"bar\"], \"tags\": {\"a\": 1, \"b\": null}}'::json;\n\u003c/pre\u003e\n\u003cp\u003eAs previously stated, when a JSON value is input and then printed without any additional processing, \u003ccode class=\"type\"\u003ejson\u003c/code\u003e outputs the same text that was input, while \u003ccode class=\"type\"\u003ejsonb\u003c/code\u003e does not preserve semantically-insignificant details such as whitespace. For example, note the differences here:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT '{\"bar\": \"baz\", \"balance\": 7.77, \"active\":false}'::json;\n                      json\n-------------------------------------------------\n {\"bar\": \"baz\", \"balance\": 7.77, \"active\":false}\n(1 row)\n\nSELECT '{\"bar\": \"baz\", \"balance\": 7.77, \"active\":false}'::jsonb;\n                      jsonb\n--------------------------------------------------\n {\"bar\": \"baz\", \"active\": false, \"balance\": 7.77}\n(1 row)\n\u003c/pre\u003e\n\u003cp\u003eOne semantically-insignificant detail worth noting is that in \u003ccode class=\"type\"\u003ejsonb\u003c/code\u003e, numbers will be printed according to the behavior of the underlying \u003ccode class=\"type\"\u003enumeric\u003c/code\u003e type. In practice this means that numbers entered with \u003ccode class=\"literal\"\u003eE\u003c/code\u003e notation will be printed without it, for example:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT '{\"reading\": 1.230e-5}'::json, '{\"reading\": 1.230e-5}'::jsonb;\n         json          |          jsonb\n-----------------------+-------------------------\n {\"reading\": 1.230e-5} | {\"reading\": 0.00001230}\n(1 row)\n\u003c/pre\u003e\n\u003cp\u003eHowever, \u003ccode class=\"type\"\u003ejsonb\u003c/code\u003e will preserve trailing fractional zeroes, as seen in this example, even though those are semantically insignificant for purposes such as equality checks.\u003c/p\u003e\n\u003cp\u003eFor the list of built-in functions and operators available for constructing and processing JSON values, see \u003ca class=\"xref\" href=\"/docs/18/functions-json.html\" title=\"9.16. JSON Functions and Operators\"\u003eSection 9.16\u003c/a\u003e.\u003c/p\u003e\n\u003c/div\u003e\n\u003cdiv class=\"sect2\" id=\"JSON-DOC-DESIGN\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3 class=\"title\"\u003e8.14.2. Designing JSON Documents \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eRepresenting 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 \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003edocuments\u003c/span\u003e”\u003c/span\u003e (datums) in a table.\u003c/p\u003e\n\u003cp\u003eJSON 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.\u003c/p\u003e\n\u003c/div\u003e\n\u003cdiv class=\"sect2\" id=\"JSON-CONTAINMENT\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3 class=\"title\"\u003e8.14.3. \u003ccode class=\"type\"\u003ejsonb\u003c/code\u003e Containment and Existence \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eTesting \u003cem class=\"firstterm\"\u003econtainment\u003c/em\u003e is an important capability of \u003ccode class=\"type\"\u003ejsonb\u003c/code\u003e. There is no parallel set of facilities for the \u003ccode class=\"type\"\u003ejson\u003c/code\u003e type. Containment tests whether one \u003ccode class=\"type\"\u003ejsonb\u003c/code\u003e document has contained within it another one. These examples return true except as noted:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003e-- Simple scalar/primitive values contain only the identical value:\nSELECT '\"foo\"'::jsonb @\u0026gt; '\"foo\"'::jsonb;\n\n-- The array on the right side is contained within the one on the left:\nSELECT '[1, 2, 3]'::jsonb @\u0026gt; '[1, 3]'::jsonb;\n\n-- Order of array elements is not significant, so this is also true:\nSELECT '[1, 2, 3]'::jsonb @\u0026gt; '[3, 1]'::jsonb;\n\n-- Duplicate array elements don't matter either:\nSELECT '[1, 2, 3]'::jsonb @\u0026gt; '[1, 2, 2]'::jsonb;\n\n-- The object with a single pair on the right side is contained\n-- within the object on the left side:\nSELECT '{\"product\": \"PostgreSQL\", \"version\": 9.4, \"jsonb\": true}'::jsonb @\u0026gt; '{\"version\": 9.4}'::jsonb;\n\n-- The array on the right side is \u003cspan class=\"emphasis\"\u003e\u003cstrong\u003enot\u003c/strong\u003e\u003c/span\u003e considered contained within the\n-- array on the left, even though a similar array is nested within it:\nSELECT '[1, 2, [1, 3]]'::jsonb @\u0026gt; '[1, 3]'::jsonb;  -- yields false\n\n-- But with a layer of nesting, it is contained:\nSELECT '[1, 2, [1, 3]]'::jsonb @\u0026gt; '[[1, 3]]'::jsonb;\n\n-- Similarly, containment is not reported here:\nSELECT '{\"foo\": {\"bar\": \"baz\"}}'::jsonb @\u0026gt; '{\"bar\": \"baz\"}'::jsonb;  -- yields false\n\n-- A top-level key and an empty object is contained:\nSELECT '{\"foo\": {\"bar\": \"baz\"}}'::jsonb @\u0026gt; '{\"foo\": {}}'::jsonb;\n\u003c/pre\u003e\n\u003cp\u003eThe 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.\u003c/p\u003e\n\u003cp\u003eAs a special exception to the general principle that the structures must match, an array may contain a primitive value:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003e-- This array contains the primitive string value:\nSELECT '[\"foo\", \"bar\"]'::jsonb @\u0026gt; '\"bar\"'::jsonb;\n\n-- This exception is not reciprocal -- non-containment is reported here:\nSELECT '\"bar\"'::jsonb @\u0026gt; '[\"bar\"]'::jsonb;  -- yields false\n\u003c/pre\u003e\n\u003cp\u003e\u003ccode class=\"type\"\u003ejsonb\u003c/code\u003e also has an \u003cem class=\"firstterm\"\u003eexistence\u003c/em\u003e operator, which is a variation on the theme of containment: it tests whether a string (given as a \u003ccode class=\"type\"\u003etext\u003c/code\u003e value) appears as an object key or array element at the top level of the \u003ccode class=\"type\"\u003ejsonb\u003c/code\u003e value. These examples return true except as noted:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003e-- String exists as array element:\nSELECT '[\"foo\", \"bar\", \"baz\"]'::jsonb ? 'bar';\n\n-- String exists as object key:\nSELECT '{\"foo\": \"bar\"}'::jsonb ? 'foo';\n\n-- Object values are not considered:\nSELECT '{\"foo\": \"bar\"}'::jsonb ? 'bar';  -- yields false\n\n-- As with containment, existence must match at the top level:\nSELECT '{\"foo\": {\"bar\": \"baz\"}}'::jsonb ? 'bar'; -- yields false\n\n-- A string is considered to exist if it matches a primitive JSON string:\nSELECT '\"foo\"'::jsonb ? 'foo';\n\u003c/pre\u003e\n\u003cp\u003eJSON 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.\u003c/p\u003e\n\u003cdiv class=\"tip\"\u003e\n\u003ch3 class=\"title\"\u003eTip\u003c/h3\u003e\n\u003cp\u003eBecause JSON containment is nested, an appropriate query can skip explicit selection of sub-objects. As an example, suppose that we have a \u003ccode class=\"structfield\"\u003edoc\u003c/code\u003e column containing objects at the top level, with most objects containing \u003ccode class=\"literal\"\u003etags\u003c/code\u003e fields that contain arrays of sub-objects. This query finds entries in which sub-objects containing both \u003ccode class=\"literal\"\u003e\"term\":\"paris\"\u003c/code\u003e and \u003ccode class=\"literal\"\u003e\"term\":\"food\"\u003c/code\u003e appear, while ignoring any such keys outside the \u003ccode class=\"literal\"\u003etags\u003c/code\u003e array:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT doc-\u0026gt;'site_name' FROM websites\n  WHERE doc @\u0026gt; '{\"tags\":[{\"term\":\"paris\"}, {\"term\":\"food\"}]}';\n\u003c/pre\u003e\n\u003cp\u003eOne could accomplish the same thing with, say,\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT doc-\u0026gt;'site_name' FROM websites\n  WHERE doc-\u0026gt;'tags' @\u0026gt; '[{\"term\":\"paris\"}, {\"term\":\"food\"}]';\n\u003c/pre\u003e\n\u003cp\u003ebut that approach is less flexible, and often less efficient as well.\u003c/p\u003e\n\u003cp\u003eOn 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.\u003c/p\u003e\n\u003c/div\u003e\n\u003cp\u003eThe various containment and existence operators, along with all other JSON operators and functions are documented in \u003ca class=\"xref\" href=\"/docs/18/functions-json.html\" title=\"9.16. JSON Functions and Operators\"\u003eSection 9.16\u003c/a\u003e.\u003c/p\u003e\n\u003c/div\u003e\n\u003cdiv class=\"sect2\" id=\"JSON-INDEXING\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3 class=\"title\"\u003e8.14.4. \u003ccode class=\"type\"\u003ejsonb\u003c/code\u003e Indexing \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eGIN indexes can be used to efficiently search for keys or key/value pairs occurring within a large number of \u003ccode class=\"type\"\u003ejsonb\u003c/code\u003e documents (datums). Two GIN \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003eoperator classes\u003c/span\u003e”\u003c/span\u003e are provided, offering different performance and flexibility trade-offs.\u003c/p\u003e\n\u003cp\u003eThe default GIN operator class for \u003ccode class=\"type\"\u003ejsonb\u003c/code\u003e supports queries with the key-exists operators \u003ccode class=\"literal\"\u003e?\u003c/code\u003e, \u003ccode class=\"literal\"\u003e?|\u003c/code\u003e and \u003ccode class=\"literal\"\u003e?\u0026amp;\u003c/code\u003e, the containment operator \u003ccode class=\"literal\"\u003e@\u0026gt;\u003c/code\u003e, and the \u003ccode class=\"type\"\u003ejsonpath\u003c/code\u003e match operators \u003ccode class=\"literal\"\u003e@?\u003c/code\u003e and \u003ccode class=\"literal\"\u003e@@\u003c/code\u003e. (For details of the semantics that these operators implement, see \u003ca class=\"xref\" href=\"/docs/18/functions-json.html#FUNCTIONS-JSONB-OP-TABLE\" title=\"Table 9.48. Additional jsonb Operators\"\u003eTable 9.48\u003c/a\u003e.) An example of creating an index with this operator class is:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eCREATE INDEX idxgin ON api USING GIN (jdoc);\n\u003c/pre\u003e\n\u003cp\u003eThe non-default GIN operator class \u003ccode class=\"literal\"\u003ejsonb_path_ops\u003c/code\u003e does not support the key-exists operators, but it does support \u003ccode class=\"literal\"\u003e@\u0026gt;\u003c/code\u003e, \u003ccode class=\"literal\"\u003e@?\u003c/code\u003e and \u003ccode class=\"literal\"\u003e@@\u003c/code\u003e. An example of creating an index with this operator class is:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eCREATE INDEX idxginp ON api USING GIN (jdoc jsonb_path_ops);\n\u003c/pre\u003e\n\u003cp\u003eConsider 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:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003e{\n    \"guid\": \"9c36adc1-7fb5-4d5b-83b4-90356a46061a\",\n    \"name\": \"Angela Barton\",\n    \"is_active\": true,\n    \"company\": \"Magnafone\",\n    \"address\": \"178 Howard Place, Gulf, Washington, 702\",\n    \"registered\": \"2009-11-07T08:53:22 +08:00\",\n    \"latitude\": 19.793713,\n    \"longitude\": 86.513373,\n    \"tags\": [\n        \"enim\",\n        \"aliquip\",\n        \"qui\"\n    ]\n}\n\u003c/pre\u003e\n\u003cp\u003eWe store these documents in a table named \u003ccode class=\"structname\"\u003eapi\u003c/code\u003e, in a \u003ccode class=\"type\"\u003ejsonb\u003c/code\u003e column named \u003ccode class=\"structfield\"\u003ejdoc\u003c/code\u003e. If a GIN index is created on this column, queries like the following can make use of the index:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003e-- Find documents in which the key \"company\" has value \"Magnafone\"\nSELECT jdoc-\u0026gt;'guid', jdoc-\u0026gt;'name' FROM api WHERE jdoc @\u0026gt; '{\"company\": \"Magnafone\"}';\n\u003c/pre\u003e\n\u003cp\u003eHowever, the index could not be used for queries like the following, because though the operator \u003ccode class=\"literal\"\u003e?\u003c/code\u003e is indexable, it is not applied directly to the indexed column \u003ccode class=\"structfield\"\u003ejdoc\u003c/code\u003e:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003e-- Find documents in which the key \"tags\" contains key or array element \"qui\"\nSELECT jdoc-\u0026gt;'guid', jdoc-\u0026gt;'name' FROM api WHERE jdoc -\u0026gt; 'tags' ? 'qui';\n\u003c/pre\u003e\n\u003cp\u003eStill, with appropriate use of expression indexes, the above query can use an index. If querying for particular items within the \u003ccode class=\"literal\"\u003e\"tags\"\u003c/code\u003e key is common, defining an index like this may be worthwhile:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eCREATE INDEX idxgintags ON api USING GIN ((jdoc -\u0026gt; 'tags'));\n\u003c/pre\u003e\n\u003cp\u003eNow, the \u003ccode class=\"literal\"\u003eWHERE\u003c/code\u003e clause \u003ccode class=\"literal\"\u003ejdoc -\u0026gt; 'tags' ? 'qui'\u003c/code\u003e will be recognized as an application of the indexable operator \u003ccode class=\"literal\"\u003e?\u003c/code\u003e to the indexed expression \u003ccode class=\"literal\"\u003ejdoc -\u0026gt; 'tags'\u003c/code\u003e. (More information on expression indexes can be found in \u003ca class=\"xref\" href=\"/docs/18/indexes-expressional.html\" title=\"11.7. Indexes on Expressions\"\u003eSection 11.7\u003c/a\u003e.)\u003c/p\u003e\n\u003cp\u003eAnother approach to querying is to exploit containment, for example:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003e-- Find documents in which the key \"tags\" contains array element \"qui\"\nSELECT jdoc-\u0026gt;'guid', jdoc-\u0026gt;'name' FROM api WHERE jdoc @\u0026gt; '{\"tags\": [\"qui\"]}';\n\u003c/pre\u003e\n\u003cp\u003eA simple GIN index on the \u003ccode class=\"structfield\"\u003ejdoc\u003c/code\u003e column can support this query. But note that such an index will store copies of every key and value in the \u003ccode class=\"structfield\"\u003ejdoc\u003c/code\u003e column, whereas the expression index of the previous example stores only data found under the \u003ccode class=\"literal\"\u003etags\u003c/code\u003e 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.\u003c/p\u003e\n\u003cp\u003eGIN indexes also support the \u003ccode class=\"literal\"\u003e@?\u003c/code\u003e and \u003ccode class=\"literal\"\u003e@@\u003c/code\u003e operators, which perform \u003ccode class=\"type\"\u003ejsonpath\u003c/code\u003e matching. Examples are\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT jdoc-\u0026gt;'guid', jdoc-\u0026gt;'name' FROM api WHERE jdoc @? '$.tags[*] ? (@ == \"qui\")';\n\u003c/pre\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT jdoc-\u0026gt;'guid', jdoc-\u0026gt;'name' FROM api WHERE jdoc @@ '$.tags[*] == \"qui\"';\n\u003c/pre\u003e\n\u003cp\u003eFor these operators, a GIN index extracts clauses of the form \u003ccode class=\"literal\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eaccessors_chain\u003c/code\u003e\u003c/em\u003e == \u003cem class=\"replaceable\"\u003e\u003ccode\u003econstant\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e out of the \u003ccode class=\"type\"\u003ejsonpath\u003c/code\u003e pattern, and does the index search based on the keys and values mentioned in these clauses. The accessors chain may include \u003ccode class=\"literal\"\u003e.\u003cem class=\"replaceable\"\u003e\u003ccode\u003ekey\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e, \u003ccode class=\"literal\"\u003e[*]\u003c/code\u003e, and \u003ccode class=\"literal\"\u003e[\u003cem class=\"replaceable\"\u003e\u003ccode\u003eindex\u003c/code\u003e\u003c/em\u003e]\u003c/code\u003e accessors. The \u003ccode class=\"literal\"\u003ejsonb_ops\u003c/code\u003e operator class also supports \u003ccode class=\"literal\"\u003e.*\u003c/code\u003e and \u003ccode class=\"literal\"\u003e.**\u003c/code\u003e accessors, but the \u003ccode class=\"literal\"\u003ejsonb_path_ops\u003c/code\u003e operator class does not.\u003c/p\u003e\n\u003cp\u003eAlthough the \u003ccode class=\"literal\"\u003ejsonb_path_ops\u003c/code\u003e operator class supports only queries with the \u003ccode class=\"literal\"\u003e@\u0026gt;\u003c/code\u003e, \u003ccode class=\"literal\"\u003e@?\u003c/code\u003e and \u003ccode class=\"literal\"\u003e@@\u003c/code\u003e operators, it has notable performance advantages over the default operator class \u003ccode class=\"literal\"\u003ejsonb_ops\u003c/code\u003e. A \u003ccode class=\"literal\"\u003ejsonb_path_ops\u003c/code\u003e index is usually much smaller than a \u003ccode class=\"literal\"\u003ejsonb_ops\u003c/code\u003e 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.\u003c/p\u003e\n\u003cp\u003eThe technical difference between a \u003ccode class=\"literal\"\u003ejsonb_ops\u003c/code\u003e and a \u003ccode class=\"literal\"\u003ejsonb_path_ops\u003c/code\u003e 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. \u003ca class=\"footnote\" href=\"/docs/18/datatype-json.html#ftn.id-1.5.7.22.18.9.3\"\u003e\u003csup class=\"footnote\" id=\"id-1.5.7.22.18.9.3\"\u003e[7]\u003c/sup\u003e\u003c/a\u003e Basically, each \u003ccode class=\"literal\"\u003ejsonb_path_ops\u003c/code\u003e index item is a hash of the value and the key(s) leading to it; for example to index \u003ccode class=\"literal\"\u003e{\"foo\": {\"bar\": \"baz\"}}\u003c/code\u003e, a single index item would be created incorporating all three of \u003ccode class=\"literal\"\u003efoo\u003c/code\u003e, \u003ccode class=\"literal\"\u003ebar\u003c/code\u003e, and \u003ccode class=\"literal\"\u003ebaz\u003c/code\u003e 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 \u003ccode class=\"literal\"\u003efoo\u003c/code\u003e appears as a key. On the other hand, a \u003ccode class=\"literal\"\u003ejsonb_ops\u003c/code\u003e index would create three index items representing \u003ccode class=\"literal\"\u003efoo\u003c/code\u003e, \u003ccode class=\"literal\"\u003ebar\u003c/code\u003e, and \u003ccode class=\"literal\"\u003ebaz\u003c/code\u003e 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 \u003ccode class=\"literal\"\u003ejsonb_path_ops\u003c/code\u003e search, especially if there are a very large number of rows containing any single one of the three index items.\u003c/p\u003e\n\u003cp\u003eA disadvantage of the \u003ccode class=\"literal\"\u003ejsonb_path_ops\u003c/code\u003e approach is that it produces no index entries for JSON structures not containing any values, such as \u003ccode class=\"literal\"\u003e{\"a\": {}}\u003c/code\u003e. If a search for documents containing such a structure is requested, it will require a full-index scan, which is quite slow. \u003ccode class=\"literal\"\u003ejsonb_path_ops\u003c/code\u003e is therefore ill-suited for applications that often perform such searches.\u003c/p\u003e\n\u003cp\u003e\u003ccode class=\"type\"\u003ejsonb\u003c/code\u003e also supports \u003ccode class=\"literal\"\u003ebtree\u003c/code\u003e and \u003ccode class=\"literal\"\u003ehash\u003c/code\u003e indexes. These are usually useful only if it's important to check equality of complete JSON documents. The \u003ccode class=\"literal\"\u003ebtree\u003c/code\u003e ordering for \u003ccode class=\"type\"\u003ejsonb\u003c/code\u003e datums is seldom of great interest, but for completeness it is:\u003c/p\u003e\n\u003cpre class=\"synopsis\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eObject\u003c/code\u003e\u003c/em\u003e \u0026gt; \u003cem class=\"replaceable\"\u003e\u003ccode\u003eArray\u003c/code\u003e\u003c/em\u003e \u0026gt; \u003cem class=\"replaceable\"\u003e\u003ccode\u003eBoolean\u003c/code\u003e\u003c/em\u003e \u0026gt; \u003cem class=\"replaceable\"\u003e\u003ccode\u003eNumber\u003c/code\u003e\u003c/em\u003e \u0026gt; \u003cem class=\"replaceable\"\u003e\u003ccode\u003eString\u003c/code\u003e\u003c/em\u003e \u0026gt; \u003cem class=\"replaceable\"\u003e\u003ccode\u003enull\u003c/code\u003e\u003c/em\u003e\n\n\u003cem class=\"replaceable\"\u003e\u003ccode\u003eObject with n pairs\u003c/code\u003e\u003c/em\u003e \u0026gt; \u003cem class=\"replaceable\"\u003e\u003ccode\u003eobject with n - 1 pairs\u003c/code\u003e\u003c/em\u003e\n\n\u003cem class=\"replaceable\"\u003e\u003ccode\u003eArray with n elements\u003c/code\u003e\u003c/em\u003e \u0026gt; \u003cem class=\"replaceable\"\u003e\u003ccode\u003earray with n - 1 elements\u003c/code\u003e\u003c/em\u003e\n\u003c/pre\u003e\n\u003cp\u003ewith the exception that (for historical reasons) an empty top level array sorts less than \u003cem class=\"replaceable\"\u003e\u003ccode\u003enull\u003c/code\u003e\u003c/em\u003e. Objects with equal numbers of pairs are compared in the order:\u003c/p\u003e\n\u003cpre class=\"synopsis\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ekey-1\u003c/code\u003e\u003c/em\u003e, \u003cem class=\"replaceable\"\u003e\u003ccode\u003evalue-1\u003c/code\u003e\u003c/em\u003e, \u003cem class=\"replaceable\"\u003e\u003ccode\u003ekey-2\u003c/code\u003e\u003c/em\u003e ...\n\u003c/pre\u003e\n\u003cp\u003eNote 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:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003e{ \"aa\": 1, \"c\": 1} \u0026gt; {\"b\": 1, \"d\": 1}\n\u003c/pre\u003e\n\u003cp\u003eSimilarly, arrays with equal numbers of elements are compared in the order:\u003c/p\u003e\n\u003cpre class=\"synopsis\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eelement-1\u003c/code\u003e\u003c/em\u003e, \u003cem class=\"replaceable\"\u003e\u003ccode\u003eelement-2\u003c/code\u003e\u003c/em\u003e ...\n\u003c/pre\u003e\n\u003cp\u003ePrimitive JSON values are compared using the same comparison rules as for the underlying \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e data type. Strings are compared using the default database collation.\u003c/p\u003e\n\u003c/div\u003e\n\u003cdiv class=\"sect2\" id=\"JSONB-SUBSCRIPTING\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3 class=\"title\"\u003e8.14.5. \u003ccode class=\"type\"\u003ejsonb\u003c/code\u003e Subscripting \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eThe \u003ccode class=\"type\"\u003ejsonb\u003c/code\u003e 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 \u003ccode class=\"literal\"\u003epath\u003c/code\u003e argument in the \u003ccode class=\"literal\"\u003ejsonb_set\u003c/code\u003e function. If a \u003ccode class=\"type\"\u003ejsonb\u003c/code\u003e 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.\u003c/p\u003e\n\u003cp\u003e\u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e statements may use subscripting in the \u003ccode class=\"literal\"\u003eSET\u003c/code\u003e clause to modify \u003ccode class=\"type\"\u003ejsonb\u003c/code\u003e values. Subscript paths must be traversable for all affected values insofar as they exist. For instance, the path \u003ccode class=\"literal\"\u003eval['a']['b']['c']\u003c/code\u003e can be traversed all the way to \u003ccode class=\"literal\"\u003ec\u003c/code\u003e if every \u003ccode class=\"literal\"\u003eval\u003c/code\u003e, \u003ccode class=\"literal\"\u003eval['a']\u003c/code\u003e, and \u003ccode class=\"literal\"\u003eval['a']['b']\u003c/code\u003e is an object. If any \u003ccode class=\"literal\"\u003eval['a']\u003c/code\u003e or \u003ccode class=\"literal\"\u003eval['a']['b']\u003c/code\u003e is not defined, it will be created as an empty object and filled as necessary. However, if any \u003ccode class=\"literal\"\u003eval\u003c/code\u003e itself or one of the intermediary values is defined as a non-object such as a string, number, or \u003ccode class=\"literal\"\u003ejsonb\u003c/code\u003e \u003ccode class=\"literal\"\u003enull\u003c/code\u003e, traversal cannot proceed so an error is raised and the transaction aborted.\u003c/p\u003e\n\u003cp\u003eAn example of subscripting syntax:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003e\n-- Extract object value by key\nSELECT ('{\"a\": 1}'::jsonb)['a'];\n\n-- Extract nested object value by key path\nSELECT ('{\"a\": {\"b\": {\"c\": 1}}}'::jsonb)['a']['b']['c'];\n\n-- Extract array element by index\nSELECT ('[1, \"2\", null]'::jsonb)[1];\n\n-- Update object value by key. Note the quotes around '1': the assigned\n-- value must be of the jsonb type as well\nUPDATE table_name SET jsonb_field['key'] = '1';\n\n-- This will raise an error if any record's jsonb_field['a']['b'] is something\n-- other than an object. For example, the value {\"a\": 1} has a numeric value\n-- of the key 'a'.\nUPDATE table_name SET jsonb_field['a']['b']['c'] = '1';\n\n-- Filter records using a WHERE clause with subscripting. Since the result of\n-- subscripting is jsonb, the value we compare it against must also be jsonb.\n-- The double quotes make \"value\" also a valid jsonb string.\nSELECT * FROM table_name WHERE jsonb_field['key'] = '\"value\"';\n\u003c/pre\u003e\n\u003cp\u003e\u003ccode class=\"type\"\u003ejsonb\u003c/code\u003e assignment via subscripting handles a few edge cases differently from \u003ccode class=\"literal\"\u003ejsonb_set\u003c/code\u003e. When a source \u003ccode class=\"type\"\u003ejsonb\u003c/code\u003e value is \u003ccode class=\"literal\"\u003eNULL\u003c/code\u003e, assignment via subscripting will proceed as if it was an empty JSON value of the type (object or array) implied by the subscript key:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003e-- Where jsonb_field was NULL, it is now {\"a\": 1}\nUPDATE table_name SET jsonb_field['a'] = '1';\n\n-- Where jsonb_field was NULL, it is now [1]\nUPDATE table_name SET jsonb_field[0] = '1';\n\u003c/pre\u003e\n\u003cp\u003eIf an index is specified for an array containing too few elements, \u003ccode class=\"literal\"\u003eNULL\u003c/code\u003e elements will be appended until the index is reachable and the value can be set.\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003e-- Where jsonb_field was [], it is now [null, null, 2];\n-- where jsonb_field was [0], it is now [0, null, 2]\nUPDATE table_name SET jsonb_field[2] = '2';\n\u003c/pre\u003e\n\u003cp\u003eA \u003ccode class=\"type\"\u003ejsonb\u003c/code\u003e 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 \u003ccode class=\"literal\"\u003enull\u003c/code\u003e-padded, as specified by the subscript path until the assigned value can be placed.\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003e-- Where jsonb_field was {}, it is now {\"a\": [{\"b\": 1}]}\nUPDATE table_name SET jsonb_field['a'][0]['b'] = '1';\n\n-- Where jsonb_field was [], it is now [null, {\"a\": 1}]\nUPDATE table_name SET jsonb_field[1]['a'] = '1';\n\u003c/pre\u003e\n\u003c/div\u003e\n\u003cdiv class=\"sect2\" id=\"DATATYPE-JSON-TRANSFORMS\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3 class=\"title\"\u003e8.14.6. Transforms \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eAdditional extensions are available that implement transforms for the \u003ccode class=\"type\"\u003ejsonb\u003c/code\u003e type for different procedural languages.\u003c/p\u003e\n\u003cp\u003eThe extensions for PL/Perl are called \u003ccode class=\"literal\"\u003ejsonb_plperl\u003c/code\u003e and \u003ccode class=\"literal\"\u003ejsonb_plperlu\u003c/code\u003e. If you use them, \u003ccode class=\"type\"\u003ejsonb\u003c/code\u003e values are mapped to Perl arrays, hashes, and scalars, as appropriate.\u003c/p\u003e\n\u003cp\u003eThe extension for PL/Python is called \u003ccode class=\"literal\"\u003ejsonb_plpython3u\u003c/code\u003e. If you use it, \u003ccode class=\"type\"\u003ejsonb\u003c/code\u003e values are mapped to Python dictionaries, lists, and scalars, as appropriate.\u003c/p\u003e\n\u003cp\u003eOf these extensions, \u003ccode class=\"literal\"\u003ejsonb_plperl\u003c/code\u003e is considered \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003etrusted\u003c/span\u003e”\u003c/span\u003e, that is, it can be installed by non-superusers who have \u003ccode class=\"literal\"\u003eCREATE\u003c/code\u003e privilege on the current database. The rest require superuser privilege to install.\u003c/p\u003e\n\u003c/div\u003e\n\u003cdiv class=\"sect2\" id=\"DATATYPE-JSONPATH\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3 class=\"title\"\u003e8.14.7. jsonpath Type \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eThe \u003ccode class=\"type\"\u003ejsonpath\u003c/code\u003e type implements support for the SQL/JSON path language in \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e 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.\u003c/p\u003e\n\u003cp\u003eThe 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:\u003c/p\u003e\n\u003cdiv class=\"itemizedlist\"\u003e\n\u003cul class=\"itemizedlist\"\u003e\n\u003cli class=\"listitem\"\u003e\n\u003cp\u003eDot (\u003ccode class=\"literal\"\u003e.\u003c/code\u003e) is used for member access.\u003c/p\u003e\n\u003c/li\u003e\n\u003cli class=\"listitem\"\u003e\n\u003cp\u003eSquare brackets (\u003ccode class=\"literal\"\u003e[]\u003c/code\u003e) are used for array access.\u003c/p\u003e\n\u003c/li\u003e\n\u003cli class=\"listitem\"\u003e\n\u003cp\u003eSQL/JSON arrays are 0-relative, unlike regular SQL arrays that start from 1.\u003c/p\u003e\n\u003c/li\u003e\n\u003c/ul\u003e\n\u003c/div\u003e\n\u003cp\u003eNumeric literals in SQL/JSON path expressions follow JavaScript rules, which are different from both SQL and JSON in some minor details. For example, SQL/JSON path allows \u003ccode class=\"literal\"\u003e.1\u003c/code\u003e and \u003ccode class=\"literal\"\u003e1.\u003c/code\u003e, which are invalid in JSON. Non-decimal integer literals and underscore separators are supported, for example, \u003ccode class=\"literal\"\u003e1_000_000\u003c/code\u003e, \u003ccode class=\"literal\"\u003e0x1EEE_FFFF\u003c/code\u003e, \u003ccode class=\"literal\"\u003e0o273\u003c/code\u003e, \u003ccode class=\"literal\"\u003e0b100101\u003c/code\u003e. In SQL/JSON path (and in JavaScript, but not in SQL proper), there must not be an underscore separator directly after the radix prefix.\u003c/p\u003e\n\u003cp\u003eAn 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 \u003ca class=\"xref\" href=\"/docs/18/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS\" title=\"4.1.2.1. String Constants\"\u003eSection 4.1.2.1\u003c/a\u003e). 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 \u003ccode class=\"literal\"\u003e\\\"\u003c/code\u003e, and to write a backslash itself, you must write \u003ccode class=\"literal\"\u003e\\\\\u003c/code\u003e. Other special backslash sequences include those recognized in JavaScript strings: \u003ccode class=\"literal\"\u003e\\b\u003c/code\u003e, \u003ccode class=\"literal\"\u003e\\f\u003c/code\u003e, \u003ccode class=\"literal\"\u003e\\n\u003c/code\u003e, \u003ccode class=\"literal\"\u003e\\r\u003c/code\u003e, \u003ccode class=\"literal\"\u003e\\t\u003c/code\u003e, \u003ccode class=\"literal\"\u003e\\v\u003c/code\u003e for various ASCII control characters, \u003ccode class=\"literal\"\u003e\\x\u003cem class=\"replaceable\"\u003e\u003ccode\u003eNN\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e for a character code written with only two hex digits, \u003ccode class=\"literal\"\u003e\\u\u003cem class=\"replaceable\"\u003e\u003ccode\u003eNNNN\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e for a Unicode character identified by its 4-hex-digit code point, and \u003ccode class=\"literal\"\u003e\\u{\u003cem class=\"replaceable\"\u003e\u003ccode\u003eN...\u003c/code\u003e\u003c/em\u003e}\u003c/code\u003e for a Unicode character code point written with 1 to 6 hex digits.\u003c/p\u003e\n\u003cp\u003eA path expression consists of a sequence of path elements, which can be any of the following:\u003c/p\u003e\n\u003cdiv class=\"itemizedlist\"\u003e\n\u003cul class=\"itemizedlist\"\u003e\n\u003cli class=\"listitem\"\u003e\n\u003cp\u003ePath literals of JSON primitive types: Unicode text, numeric, true, false, or null.\u003c/p\u003e\n\u003c/li\u003e\n\u003cli class=\"listitem\"\u003e\n\u003cp\u003ePath variables listed in \u003ca class=\"xref\" href=\"/docs/18/datatype-json.html#TYPE-JSONPATH-VARIABLES\" title=\"Table 8.24. jsonpath Variables\"\u003eTable 8.24\u003c/a\u003e.\u003c/p\u003e\n\u003c/li\u003e\n\u003cli class=\"listitem\"\u003e\n\u003cp\u003eAccessor operators listed in \u003ca class=\"xref\" href=\"/docs/18/datatype-json.html#TYPE-JSONPATH-ACCESSORS\" title=\"Table 8.25. jsonpath Accessors\"\u003eTable 8.25\u003c/a\u003e.\u003c/p\u003e\n\u003c/li\u003e\n\u003cli class=\"listitem\"\u003e\n\u003cp\u003e\u003ccode class=\"type\"\u003ejsonpath\u003c/code\u003e operators and methods listed in \u003ca class=\"xref\" href=\"/docs/18/functions-json.html#FUNCTIONS-SQLJSON-PATH-OPERATORS\" title=\"9.16.2.3. SQL/JSON Path Operators and Methods\"\u003eSection 9.16.2.3\u003c/a\u003e.\u003c/p\u003e\n\u003c/li\u003e\n\u003cli class=\"listitem\"\u003e\n\u003cp\u003eParentheses, which can be used to provide filter expressions or define the order of path evaluation.\u003c/p\u003e\n\u003c/li\u003e\n\u003c/ul\u003e\n\u003c/div\u003e\n\u003cp\u003eFor details on using \u003ccode class=\"type\"\u003ejsonpath\u003c/code\u003e expressions with SQL/JSON query functions, see \u003ca class=\"xref\" href=\"/docs/18/functions-json.html#FUNCTIONS-SQLJSON-PATH\" title=\"9.16.2. The SQL/JSON Path Language\"\u003eSection 9.16.2\u003c/a\u003e.\u003c/p\u003e\n\u003cdiv class=\"table\" id=\"TYPE-JSONPATH-VARIABLES\"\u003e\n\u003cp class=\"title\"\u003e\u003cstrong\u003eTable 8.24. \u003ccode class=\"type\"\u003ejsonpath\u003c/code\u003e Variables\u003c/strong\u003e\u003c/p\u003e\n\u003cdiv class=\"table-contents\"\u003e\n\u003ctable class=\"table\"\u003e\n\n\n\n\n\u003cthead\u003e\n\u003ctr\u003e\n\u003cth\u003eVariable\u003c/th\u003e\n\u003cth\u003eDescription\u003c/th\u003e\n\u003c/tr\u003e\n\u003c/thead\u003e\n\u003ctbody\u003e\n\u003ctr\u003e\n\u003ctd\u003e\u003ccode class=\"literal\"\u003e$\u003c/code\u003e\u003c/td\u003e\n\u003ctd\u003eA variable representing the JSON value being queried (the \u003cem class=\"firstterm\"\u003econtext item\u003c/em\u003e).\u003c/td\u003e\n\u003c/tr\u003e\n\u003ctr\u003e\n\u003ctd\u003e\u003ccode class=\"literal\"\u003e$varname\u003c/code\u003e\u003c/td\u003e\n\u003ctd\u003eA named variable. Its value can be set by the parameter \u003cem class=\"parameter\"\u003e\u003ccode\u003evars\u003c/code\u003e\u003c/em\u003e of several JSON processing functions; see \u003ca class=\"xref\" href=\"/docs/18/functions-json.html#FUNCTIONS-JSON-PROCESSING-TABLE\" title=\"Table 9.51. JSON Processing Functions\"\u003eTable 9.51\u003c/a\u003e for details.\u003c/td\u003e\n\u003c/tr\u003e\n\u003ctr\u003e\n\u003ctd\u003e\u003ccode class=\"literal\"\u003e@\u003c/code\u003e\u003c/td\u003e\n\u003ctd\u003eA variable representing the result of path evaluation in filter expressions.\u003c/td\u003e\n\u003c/tr\u003e\n\u003c/tbody\u003e\n\u003c/table\u003e\n\u003c/div\u003e\n\u003c/div\u003e\u003cbr class=\"table-break\"\u003e\n\u003cdiv class=\"table\" id=\"TYPE-JSONPATH-ACCESSORS\"\u003e\n\u003cp class=\"title\"\u003e\u003cstrong\u003eTable 8.25. \u003ccode class=\"type\"\u003ejsonpath\u003c/code\u003e Accessors\u003c/strong\u003e\u003c/p\u003e\n\u003cdiv class=\"table-contents\"\u003e\n\u003ctable class=\"table\"\u003e\n\n\n\n\n\u003cthead\u003e\n\u003ctr\u003e\n\u003cth\u003eAccessor Operator\u003c/th\u003e\n\u003cth\u003eDescription\u003c/th\u003e\n\u003c/tr\u003e\n\u003c/thead\u003e\n\u003ctbody\u003e\n\u003ctr\u003e\n\u003ctd\u003e\n\u003cp\u003e\u003ccode class=\"literal\"\u003e.\u003cem class=\"replaceable\"\u003e\u003ccode\u003ekey\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e\u003c/p\u003e\n\u003cp\u003e\u003ccode class=\"literal\"\u003e.\"$\u003cem class=\"replaceable\"\u003e\u003ccode\u003evarname\u003c/code\u003e\u003c/em\u003e\"\u003c/code\u003e\u003c/p\u003e\n\u003c/td\u003e\n\u003ctd\u003e\n\u003cp\u003eMember accessor that returns an object member with the specified key. If the key name matches some named variable starting with \u003ccode class=\"literal\"\u003e$\u003c/code\u003e or does not meet the JavaScript rules for an identifier, it must be enclosed in double quotes to make it a string literal.\u003c/p\u003e\n\u003c/td\u003e\n\u003c/tr\u003e\n\u003ctr\u003e\n\u003ctd\u003e\n\u003cp\u003e\u003ccode class=\"literal\"\u003e.*\u003c/code\u003e\u003c/p\u003e\n\u003c/td\u003e\n\u003ctd\u003e\n\u003cp\u003eWildcard member accessor that returns the values of all members located at the top level of the current object.\u003c/p\u003e\n\u003c/td\u003e\n\u003c/tr\u003e\n\u003ctr\u003e\n\u003ctd\u003e\n\u003cp\u003e\u003ccode class=\"literal\"\u003e.**\u003c/code\u003e\u003c/p\u003e\n\u003c/td\u003e\n\u003ctd\u003e\n\u003cp\u003eRecursive 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 \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e extension of the SQL/JSON standard.\u003c/p\u003e\n\u003c/td\u003e\n\u003c/tr\u003e\n\u003ctr\u003e\n\u003ctd\u003e\n\u003cp\u003e\u003ccode class=\"literal\"\u003e.**{\u003cem class=\"replaceable\"\u003e\u003ccode\u003elevel\u003c/code\u003e\u003c/em\u003e}\u003c/code\u003e\u003c/p\u003e\n\u003cp\u003e\u003ccode class=\"literal\"\u003e.**{\u003cem class=\"replaceable\"\u003e\u003ccode\u003estart_level\u003c/code\u003e\u003c/em\u003e to \u003cem class=\"replaceable\"\u003e\u003ccode\u003eend_level\u003c/code\u003e\u003c/em\u003e}\u003c/code\u003e\u003c/p\u003e\n\u003c/td\u003e\n\u003ctd\u003e\n\u003cp\u003eLike \u003ccode class=\"literal\"\u003e.**\u003c/code\u003e, 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 \u003ccode class=\"literal\"\u003elast\u003c/code\u003e keyword. This is a \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e extension of the SQL/JSON standard.\u003c/p\u003e\n\u003c/td\u003e\n\u003c/tr\u003e\n\u003ctr\u003e\n\u003ctd\u003e\n\u003cp\u003e\u003ccode class=\"literal\"\u003e[\u003cem class=\"replaceable\"\u003e\u003ccode\u003esubscript\u003c/code\u003e\u003c/em\u003e, ...]\u003c/code\u003e\u003c/p\u003e\n\u003c/td\u003e\n\u003ctd\u003e\n\u003cp\u003eArray element accessor. \u003ccode class=\"literal\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003esubscript\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e can be given in two forms: \u003ccode class=\"literal\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eindex\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e or \u003ccode class=\"literal\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003estart_index\u003c/code\u003e\u003c/em\u003e to \u003cem class=\"replaceable\"\u003e\u003ccode\u003eend_index\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e. 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 \u003cem class=\"replaceable\"\u003e\u003ccode\u003estart_index\u003c/code\u003e\u003c/em\u003e and \u003cem class=\"replaceable\"\u003e\u003ccode\u003eend_index\u003c/code\u003e\u003c/em\u003e.\u003c/p\u003e\n\u003cp\u003eThe specified \u003cem class=\"replaceable\"\u003e\u003ccode\u003eindex\u003c/code\u003e\u003c/em\u003e 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 \u003ccode class=\"literal\"\u003elast\u003c/code\u003e keyword to denote the last array element, which is useful for handling arrays of unknown length.\u003c/p\u003e\n\u003c/td\u003e\n\u003c/tr\u003e\n\u003ctr\u003e\n\u003ctd\u003e\n\u003cp\u003e\u003ccode class=\"literal\"\u003e[*]\u003c/code\u003e\u003c/p\u003e\n\u003c/td\u003e\n\u003ctd\u003e\n\u003cp\u003eWildcard array element accessor that returns all array elements.\u003c/p\u003e\n\u003c/td\u003e\n\u003c/tr\u003e\n\u003c/tbody\u003e\n\u003c/table\u003e\n\u003c/div\u003e\n\u003c/div\u003e\u003cbr class=\"table-break\"\u003e\n\u003c/div\u003e\n\u003cdiv class=\"footnotes\"\u003e\n\u003cbr\u003e\n\n\n\u003cdiv class=\"footnote\" id=\"ftn.id-1.5.7.22.18.9.3\"\u003e\n\u003cp\u003e\u003ca class=\"para\" href=\"/docs/18/datatype-json.html#id-1.5.7.22.18.9.3\"\u003e\u003csup class=\"para\"\u003e[7]\u003c/sup\u003e\u003c/a\u003e For this purpose, the term \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003evalue\u003c/span\u003e”\u003c/span\u003e includes array elements, though JSON terminology sometimes considers array elements distinct from values within objects.\u003c/p\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e","related":[{"label":"pg_type catalog","url":"/wiki/catalog/pg_type/?v=18"},{"label":"pg_cast catalog","url":"/wiki/catalog/pg_cast/?v=18"},{"label":"pg_operator catalog","url":"/wiki/catalog/pg_operator/?v=18"},{"label":"pg_opclass catalog","url":"/wiki/catalog/pg_opclass/?v=18"},{"label":"Arrays","url":"/wiki/type/arrays/?v=18"},{"label":"btree index access method","url":"/wiki/indexam/btree/?v=18"},{"label":"gin index access method","url":"/wiki/indexam/gin/?v=18"},{"label":"hash index access method","url":"/wiki/indexam/hash/?v=18"}],"sections":[]}},"RequestedLocale":"zh-Hans","Fallback":true,"Versions":["10","11","12","13","14","15","16","17","18","19","20"],"Locales":["en"],"Signatures":null,"Spellings":null,"SQLState":null,"Evidence":null}