"Jonathan S. Katz" writes:
> On 4/29/20 7:40 PM, Jonathan S. Katz wrote:
>> I'll compromise on the temporary importants, but first I want to see
>> what's causing the need for it. Do you have a suggestion on a page to test?
I haven't yet pushed anything dependent on the new markup, but
attached is a draft revision for the JSON section; if you look at
the SRFs such as json_array_elements you'll see the issue.
> From real quick I got it to here. With the latest copy of the doc builds
> it appears to still work as expected, but I need a section with the new
> "pre" block to test.
Yeah, I see you found the same and
settings I did.
> I think the "background-color: inherit !important" is a bit odd, and
> would like to trace that one down a bit more, but I did not see anything
> obvious on my glance through it.
I think it's coming from this bit at about main.css:660:
pre,
code,
#docContent kbd,
#docContent tt.LITERAL,
#docContent tt.REPLACEABLE {
font-size: 0.9rem !important;
color: inherit !important;
background-color: #f8f9fa !important;
border-radius: .25rem;
margin: .6rem 0;
font-weight: 300;
}
I had to override most of that.
regards, tom lane
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 68c08c5..1d3c281 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -14368,1461 +14423,2322 @@ table2-mapping
shows the operators that
are available for use with JSON data types (see ).
-
-
-
- <type>json</type> and <type>jsonb</type> Operators
-
-
-
- Operator
- Right Operand Type
- Return type
- Description
- Example
- Example Result
-
-
-
-
- ->
- int
- json or jsonb
- Get JSON array element (indexed from zero, negative
- integers count from the end)
- '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->2
- {"c":"baz"}
-
-
- ->
- text
- json or jsonb
- Get JSON object field by key
- '{"a": {"b":"foo"}}'::json->'a'
- {"b":"foo"}
-
-
- ->>
- int
- text
- Get JSON array element as text
- '[1,2,3]'::json->>2
- 3
-
-
- ->>
- text
- text
- Get JSON object field as text
- '{"a":1,"b":2}'::json->>'b'
- 2
-
-
- #>
- text[]
- json or jsonb
- Get JSON object at the specified path
- '{"a": {"b":{"c": "foo"}}}'::json#>'{a,b}'
- {"c": "foo"}
-
-
- #>>
- text[]
- text
- Get JSON object at the specified path as text
- '{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}'
- 3
-
-
-
-
-
-
-
- There are parallel variants of these operators for both the
- json and jsonb types.
- The field/element/path extraction operators
- return the same type as their left-hand input (either json
- or jsonb), except for those specified as
- returning text, which coerce the value to text.
- The field/element/path extraction operators return NULL, rather than
- failing, if the JSON input does not have the right structure to match
- the request; for example if no such element exists. The
- field/element/path extraction operators that accept integer JSON
- array subscripts all support negative subscripting from the end of
- arrays.
-
-
-
- The standard comparison operators shown in are available for
- jsonb, but not for json. They follow the
- ordering rules for B-tree operations outlined at .
-
-
- Some further operators also exist only for jsonb, as shown
- in .
- Many of these operators can be indexed by
- jsonb operator classes. For a full description of
- jsonb containment and existence semantics, see .
- describes how these operators can be used to effectively index
- jsonb data.
-
-
- Additional <type>jsonb</type> Operators
-
-
-
- Operator
- Right Operand Type
- Description
- Example
-
-
-
-
- @>
- jsonb
- Does the left JSON value contain the right JSON
- path/value entries at the top level?
- '{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb
-
-
- <@
- jsonb
- Are the left JSON path/value entries contained at the top level within
- the right JSON value?
- '{"b":2}'::jsonb <@ '{"a":1, "b":2}'::jsonb
-
-
- ?
- text
- Does the string exist as a top-level
- key within the JSON value?
- '{"a":1, "b":2}'::jsonb ? 'b'
-
-
- ?|
- text[]
- Do any of these array strings
- exist as top-level keys?
- '{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'c']
-
-
- ?&
- text[]
- Do all of these array strings exist
- as top-level keys?
- '["a", "b"]'::jsonb ?& array['a', 'b']
-
-
- ||
- jsonb
- Concatenate two jsonb values into a new jsonb value
- '["a", "b"]'::jsonb || '["c", "d"]'::jsonb
-
-
- -
- text
- Delete key/value pair or string
- element from left operand. Key/value pairs are matched based
- on their key value.
- '{"a": "b"}'::jsonb - 'a'
-
-
- -
- text[]
- Delete multiple key/value pairs or string
- elements from left operand. Key/value pairs are matched based
- on their key value.
- '{"a": "b", "c": "d"}'::jsonb - '{a,c}'::text[]
-
-
- -
- integer
- Delete the array element with specified index (Negative
- integers count from the end). Throws an error if top level
- container is not an array.
- '["a", "b"]'::jsonb - 1
-
-
- #-
- text[]
- Delete the field or element with specified path (for
- JSON arrays, negative integers count from the end)
- '["a", {"b":1}]'::jsonb #- '{1,b}'
-
-
- @?
- jsonpath
- Does JSON path return any item for the specified JSON value?
- '{"a":[1,2,3,4,5]}'::jsonb @? '$.a[*] ? (@ > 2)'
-
-
- @@
- jsonpath
- Returns the result of JSON path predicate check for the specified JSON value.
- Only the first item of the result is taken into account. If the
- result is not Boolean, then null is returned.
- '{"a":[1,2,3,4,5]}'::jsonb @@ '$.a[*] > 2'
-
-
-
-
-
-
-
- The || operator concatenates the elements at the top level of
- each of its operands. It does not operate recursively. For example, if
- both operands are objects with a common key field name, the value of the
- field in the result will just be the value from the right hand operand.
-
-
-
-
-
- The @? and @@ operators suppress
- the following errors: lacking object field or array element, unexpected
- JSON item type, datetime and numeric errors.
- This behavior might be helpful while searching over JSON document
- collections of varying structure.
-
-
-
-
- shows the functions that are
- available for creating json and jsonb values.
- (There are no equivalent functions for jsonb, of the row_to_json
- and array_to_json functions. However, the to_jsonb
- function supplies much the same functionality as these functions would.)
+ jsonb, though not for json. The comparison
+ operators follow the ordering rules for B-tree operations outlined in
+ .
-
- to_json
-
-
- array_to_json
-
-
- row_to_json
-
-
- json_build_array
-
-
- json_build_object
-
-
- json_object
-
-
- to_jsonb
-
-
- jsonb_build_array
-
-
- jsonb_build_object
-
-
- jsonb_object
-
-
-
- JSON Creation Functions
-
+
+ <type>json</type> and <type>jsonb</type> Operators
+
- Function
- Description
- Example
- Example Result
+
+ Operator
+
+
+ Description
+
+
+ Example(s)
+
+
- to_json(anyelement)
- to_jsonb(anyelement)
+
+ json -> integer
+ json
+
+
+ jsonb -> integer
+ jsonb
+
+
+ Extracts n'th element of JSON array
+ (array elements are indexed from zero, but negative integers count
+ from the end).
+
+
+ '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json -> 2
+ {"c":"baz"}
+
+
+ '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json -> -3
+ {"a":"foo"}
-
- Returns the value as json or jsonb.
- Arrays and composites are converted
- (recursively) to arrays and objects; otherwise, if there is a cast
- from the type to json, the cast function will be used to
- perform the conversion; otherwise, a scalar value is produced.
- For any scalar type other than a number, a Boolean, or a null value,
- the text representation will be used, in such a fashion that it is a
- valid json or jsonb value.
-
- to_json('Fred said "Hi."'::text)
- "Fred said \"Hi.\""
-
-
-
- array_to_json(anyarray [, pretty_bool])
-
-
- Returns the array as a JSON array. A PostgreSQL multidimensional array
- becomes a JSON array of arrays. Line feeds will be added between
- dimension-1 elements if pretty_bool is true.
-
- array_to_json('{{1,5},{99,100}}'::int[])
- [[1,5],[99,100]]
+
-
- row_to_json(record [, pretty_bool])
-
-
- Returns the row as a JSON object. Line feeds will be added between
- level-1 elements if pretty_bool is true.
-
- row_to_json(row(1,'foo'))
- {"f1":1,"f2":"foo"}
+
+ json -> text
+ json
+
+
+ jsonb -> text
+ jsonb
+
+
+ Extracts JSON object field with the given key.
+
+
+ '{"a": {"b":"foo"}}'::json -> 'a'
+ {"b":"foo"}
+
+
- json_build_array(VARIADIC "any")
- jsonb_build_array(VARIADIC "any")
+
+ json ->> integer
+ text
+
+
+ jsonb ->> integer
+ text
+
+
+ Extracts n'th element of JSON array,
+ as text.
+
+
+ '[1,2,3]'::json ->> 2
+ 3
-
- Builds a possibly-heterogeneously-typed JSON array out of a variadic
- argument list.
-
- json_build_array(1,2,'3',4,5)
- [1, 2, "3", 4, 5]
+
- json_build_object(VARIADIC "any")
- jsonb_build_object(VARIADIC "any")
+
+ json ->> text
+ text
+
+
+ jsonb ->> text
+ text
+
+
+ Extracts JSON object field with the given key, as text.
+
+
+ '{"a":1,"b":2}'::json ->> 'b'
+ 2
-
- Builds a JSON object out of a variadic argument list. By
- convention, the argument list consists of alternating
- keys and values.
-
- json_build_object('foo',1,'bar',2)
- {"foo": 1, "bar": 2}
+
- json_object(text[])
- jsonb_object(text[])
+
+ json #> text[]
+ json
+
+
+ jsonb #> text[]
+ jsonb
+
+
+ Extracts JSON sub-object at the specified path, where path elements
+ can be either field keys or array indexes.
+
+
+ '{"a": {"b": ["foo","bar"]}}'::json #> '{a,b,1}'
+ "bar"
-
- Builds a JSON object out of a text array. The array must have either
- exactly one dimension with an even number of members, in which case
- they are taken as alternating key/value pairs, or two dimensions
- such that each inner array has exactly two elements, which
- are taken as a key/value pair.
-
- json_object('{a, 1, b, "def", c, 3.5}')
- json_object('{{a, 1},{b, "def"},{c, 3.5}}')
- {"a": "1", "b": "def", "c": "3.5"}
+
- json_object(keys text[], values text[])
- jsonb_object(keys text[], values text[])
+
+ json #>> text[]
+ text
+
+
+ jsonb #>> text[]
+ text
+
+
+ Extracts JSON sub-object at the specified path as text.
+
+
+ '{"a": {"b": ["foo","bar"]}}'::json #>> '{a,b,1}'
+ bar
-
- This form of json_object takes keys and values pairwise from two separate
- arrays. In all other respects it is identical to the one-argument form.
-
- json_object('{a, b}', '{1,2}')
- {"a": "1", "b": "2"}
-
- array_to_json and row_to_json have the same
- behavior as to_json except for offering a pretty-printing
- option. The behavior described for to_json likewise applies
- to each individual value converted by the other JSON creation functions.
-
-
-
-
-
- The extension has a cast
- from hstore to json, so that
- hstore values converted via the JSON creation functions
- will be represented as JSON objects, not as primitive string values.
-
+
+ The field/element/path extraction operators return NULL, rather than
+ failing, if the JSON input does not have the right structure to match
+ the request; for example if no such key or array element exists.
+
- shows the functions that
- are available for processing json and jsonb values.
+ Some further operators exist only for jsonb, as shown
+ in .
+
+ describes how these operators can be used to effectively search indexed
+ jsonb data.
-
- json_array_length
-
-
- jsonb_array_length
-
-
- json_each
-
-
- jsonb_each
-
-
- json_each_text
-
-
- jsonb_each_text
-
-
- json_extract_path
-
-
- jsonb_extract_path
-
-
- json_extract_path_text
-
-
- jsonb_extract_path_text
-
-
- json_object_keys
-
-
- jsonb_object_keys
-
-
- json_populate_record
-
-
- jsonb_populate_record
-
-
- json_populate_recordset
-
-
- jsonb_populate_recordset
-
-
- json_array_elements
-
-
- jsonb_array_elements
-
-
- json_array_elements_text
-
-
- jsonb_array_elements_text
-
-
- json_typeof
-
-
- jsonb_typeof
-
-
- json_to_record
-
-
- jsonb_to_record
-
-
- json_to_recordset
-
-
- jsonb_to_recordset
-
-
- json_strip_nulls
-
-
- jsonb_strip_nulls
-
-
- jsonb_set
-
-
- jsonb_set_lax
-
-
- jsonb_insert
-
-
- jsonb_pretty
-
-
- jsonb_path_exists
-
-
- jsonb_path_exists_tz
-
-
- jsonb_path_match
-
-
- jsonb_path_match_tz
-
-
- jsonb_path_query
-
-
- jsonb_path_query_tz
-
-
- jsonb_path_query_array
-
-
- jsonb_path_query_array_tz
-
-
- jsonb_path_query_first
-
-
- jsonb_path_query_first_tz
-
-
-
- JSON Processing Functions
-
+
+ Additional <type>jsonb</type> Operators
+
- Function
- Return Type
- Description
- Example
- Example Result
+
+ Operator
+
+
+ Description
+
+
+ Example(s)
+
+
- json_array_length(json)
- jsonb_array_length(jsonb)
+
+ jsonb @> jsonb
+ boolean
+
+
+ Does the first JSON value contain the second?
+ (See for details about containment.)
+
+
+ '{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb
+ t
- int
-
- Returns the number of elements in the outermost JSON array.
-
- json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]')
- 5
+
- json_each(json)
- jsonb_each(jsonb)
-
- setof key text, value json
- setof key text, value jsonb
+
+ jsonb <@ jsonb
+ boolean
+
+
+ Is the first JSON value contained in the second?
+
+
+ '{"b":2}'::jsonb <@ '{"a":1, "b":2}'::jsonb
+ t
-
- Expands the outermost JSON object into a set of key/value pairs.
-
- select * from json_each('{"a":"foo", "b":"bar"}')
-
-
- key | value
------+-------
- a | "foo"
- b | "bar"
-
-
+
- json_each_text(json)
- jsonb_each_text(jsonb)
+
+ jsonb ? text
+ boolean
+
+
+ Does the text string exist as a top-level key or array element within
+ the JSON value?
+
+
+ '{"a":1, "b":2}'::jsonb ? 'b'
+ t
+
+
+ '["a", "b", "c"]'::jsonb ? 'b'
+ t
- setof key text, value text
-
- Expands the outermost JSON object into a set of key/value pairs. The
- returned values will be of type text.
-
- select * from json_each_text('{"a":"foo", "b":"bar"}')
-
-
- key | value
------+-------
- a | foo
- b | bar
-
-
+
- json_extract_path(from_json json, VARIADIC path_elems text[])
- jsonb_extract_path(from_json jsonb, VARIADIC path_elems text[])
-
- jsonjsonb
+
+ jsonb ?| text[]
+ boolean
+
+
+ Do any of the strings in the text array exist as top-level keys or
+ array elements?
+
+
+ '{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'd']
+ t
-
- Returns JSON value pointed to by path_elems
- (equivalent to #> operator).
-
- json_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4')
- {"f5":99,"f6":"foo"}
+
- json_extract_path_text(from_json json, VARIADIC path_elems text[])
- jsonb_extract_path_text(from_json jsonb, VARIADIC path_elems text[])
+
+ jsonb ?& text[]
+ boolean
+
+
+ Do all of the strings in the text array exist as top-level keys or
+ array elements?
+
+
+ '["a", "b", "c"]'::jsonb ?& array['a', 'b']
+ t
- text
-
- Returns JSON value pointed to by path_elems
- as text
- (equivalent to #>> operator).
-
- json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4', 'f6')
- foo
+
- json_object_keys(json)
- jsonb_object_keys(jsonb)
+
+ jsonb || jsonb
+ jsonb
+
+
+ Concatenates two jsonb values.
+ Concatenating two objects generates an object with the union of their
+ keys, taking the second object's value when there are duplicate keys.
+ Does not operate recursively: only the top-level array or object
+ structure is merged.
+
+
+ '["a", "b"]'::jsonb || '["a", "d"]'::jsonb
+ ["a", "b", "a", "d"]
+
+
+ '{"a": "b"}'::jsonb || '{"c": "d"}'::jsonb
+ {"a": "b", "c": "d"}
- setof text
-
- Returns set of keys in the outermost JSON object.
-
- json_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}}')
-
-
- json_object_keys
-------------------
- f1
- f2
-
-
+
- json_populate_record(base anyelement, from_json json)
- jsonb_populate_record(base anyelement, from_json jsonb)
+
+ jsonb - text
+ jsonb
+
+
+ Deletes a key (and its value) from a JSON object, or matching string
+ value(s) from a JSON array.
+
+
+ '{"a": "b", "c": "d"}'::jsonb - 'a'
+ {"c": "d"}
+
+
+ '["a", "b", "c", "b"]'::jsonb - 'b'
+ ["a", "c"]
- anyelement
-
- Expands the object in from_json to a row
- whose columns match the record type defined by base
- (see note below).
-
- select * from json_populate_record(null::myrowtype, '{"a": 1, "b": ["2", "a b"], "c": {"d": 4, "e": "a b c"}}')
-
-
- a | b | c
----+-----------+-------------
- 1 | {2,"a b"} | (4,"a b c")
-
-
+
- json_populate_recordset(base anyelement, from_json json)
- jsonb_populate_recordset(base anyelement, from_json jsonb)
+
+ jsonb - text[]
+ jsonb
+
+
+ Deletes all matching keys or array elements from the left operand.
+
+
+ '{"a": "b", "c": "d"}'::jsonb - '{a,c}'::text[]
+ {}
- setof anyelement
-
- Expands the outermost array of objects
- in from_json to a set of rows whose
- columns match the record type defined by base (see
- note below).
-
- select * from json_populate_recordset(null::myrowtype, '[{"a":1,"b":2},{"a":3,"b":4}]')
-
-
- a | b
----+---
- 1 | 2
- 3 | 4
-
-
+
- json_array_elements(json)
- jsonb_array_elements(jsonb)
-
- setof json
- setof jsonb
+
+ jsonb - integer
+ jsonb
+
+
+ Deletes the array element with specified index (negative
+ integers count from the end). Throws an error if JSON value
+ is not an array.
+
+
+ '["a", "b"]'::jsonb - 1
+ ["a"]
-
- Expands a JSON array to a set of JSON values.
-
- select * from json_array_elements('[1,true, [2,false]]')
-
-
- value
------------
- 1
- true
- [2,false]
-
-
+
- json_array_elements_text(json)
- jsonb_array_elements_text(jsonb)
-
- setof text
-
- Expands a JSON array to a set of text values.
-
- select * from json_array_elements_text('["foo", "bar"]')
-
-
- value
------------
- foo
- bar
-
-
-
-
- json_typeof(json)
- jsonb_typeof(jsonb)
+
+ jsonb #- text[]
+ jsonb
+
+
+ Deletes the field or array element at the specified path, where path
+ elements can be either field keys or array indexes.
+
+
+ '["a", {"b":1}]'::jsonb #- '{1,b}'
+ ["a", {}]
- text
-
- Returns the type of the outermost JSON value as a text string.
- Possible types are
- object, array, string, number,
- boolean, and null.
-
- json_typeof('-123.4')
- number
+
- json_to_record(json)
- jsonb_to_record(jsonb)
+
+ jsonb @? jsonpath
+ boolean
+
+
+ Does JSON path return any item for the specified JSON value?
+
+
+ '{"a":[1,2,3,4,5]}'::jsonb @? '$.a[*] ? (@ > 2)'
+ t
- record
-
- Builds an arbitrary record from a JSON object (see note below). As
- with all functions returning record, the caller must
- explicitly define the structure of the record with an AS
- clause.
-
- select * from json_to_record('{"a":1,"b":[1,2,3],"c":[1,2,3],"e":"bar","r": {"a": 123, "b": "a b c"}}') as x(a int, b text, c int[], d text, r myrowtype)
-
-
- a | b | c | d | r
----+---------+---------+---+---------------
- 1 | [1,2,3] | {1,2,3} | | (123,"a b c")
-
-
+
- json_to_recordset(json)
- jsonb_to_recordset(jsonb)
+
+ jsonb @@ jsonpath
+ boolean
+
+
+ Returns the result of a JSON path predicate check for the
+ specified JSON value. Only the first item of the result is taken into
+ account. If the result is not Boolean, then NULL
+ is returned.
+
+
+ '{"a":[1,2,3,4,5]}'::jsonb @@ '$.a[*] > 2'
+ t
- setof record
-
- Builds an arbitrary set of records from a JSON array of objects (see
- note below). As with all functions returning record, the
- caller must explicitly define the structure of the record with
- an AS clause.
-
- select * from json_to_recordset('[{"a":1,"b":"foo"},{"a":"2","c":"bar"}]') as x(a int, b text);
-
-
- a | b
----+-----
- 1 | foo
- 2 |
-
-
-
- json_strip_nulls(from_json json)
- jsonb_strip_nulls(from_json jsonb)
-
- jsonjsonb
-
- Returns from_json
- with all object fields that have null values omitted. Other null values
- are untouched.
-
- json_strip_nulls('[{"f1":1,"f2":null},2,null,3]')
- [{"f1":1},2,null,3]
-
-
- jsonb_set(target jsonb, path text[], new_value jsonb , create_missing boolean)
-
- jsonb
-
- Returns target
- with the section designated by path
- replaced by new_value, or with
- new_value added if
- create_missing is true (default is
- true) and the item
- designated by path does not exist.
- As with the path oriented operators, negative integers that
- appear in path count from the end
- of JSON arrays.
-
- jsonb_set('[{"f1":1,"f2":null},2,null,3]', '{0,f1}','[2,3,4]', false)
- jsonb_set('[{"f1":1,"f2":null},2]', '{0,f3}','[2,3,4]')
-
- [{"f1":[2,3,4],"f2":null},2,null,3]
- [{"f1": 1, "f2": null, "f3": [2, 3, 4]}, 2]
-
-
-
- jsonb_set_lax(target jsonb, path text[], new_value jsonb , create_missing boolean , null_value_treatment text)
-
- jsonb
-
- If new_value is not null,
- behaves identically to jsonb_set. Otherwise behaves
- according to the value of null_value_treatment
- which must be one of 'raise_exception',
- 'use_json_null', 'delete_key', or
- 'return_target'. The default is
- 'use_json_null'.
-
- jsonb_set_lax('[{"f1":1,"f2":null},2,null,3]', '{0,f1}',null)
- jsonb_set_lax('[{"f1":99,"f2":null},2]', '{0,f3}',null, true, 'return_target')
-
- [{"f1":null,"f2":null},2,null,3]
- [{"f1": 99, "f2": null}, 2]
-
-
-
-
-
- jsonb_insert(target jsonb, path text[], new_value jsonb , insert_after boolean)
-
-
- jsonb
-
- Returns target with
- new_value inserted. If
- target section designated by
- path is in a JSONB array,
- new_value will be inserted before target or
- after if insert_after is true (default is
- false). If target section
- designated by path is in JSONB object,
- new_value will be inserted only if
- target does not exist. As with the path
- oriented operators, negative integers that appear in
- path count from the end of JSON arrays.
-
-
-
- jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"new_value"')
-
-
- jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"new_value"', true)
-
-
- {"a": [0, "new_value", 1, 2]}
- {"a": [0, 1, "new_value", 2]}
-
-
-
- jsonb_pretty(from_json jsonb)
-
- text
-
- Returns from_json
- as indented JSON text.
-
- jsonb_pretty('[{"f1":1,"f2":null},2,null,3]')
-
-
-[
- {
- "f1": 1,
- "f2": null
- },
- 2,
- null,
- 3
-]
-
-
-
-
-
-
- jsonb_path_exists(target jsonb, path jsonpath , vars jsonb , silent bool)
-
-
- jsonb_path_exists_tz(target jsonb, path jsonpath , vars jsonb , silent bool)
-
-
- boolean
-
- Checks whether JSON path returns any item for the specified JSON
- value.
-
-
-
- jsonb_path_exists('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ >= $min && @ <= $max)', '{"min":2,"max":4}')
-
-
-
- true
-
-
-
-
-
- jsonb_path_match(target jsonb, path jsonpath , vars jsonb , silent bool)
-
-
- jsonb_path_match_tz(target jsonb, path jsonpath , vars jsonb , silent bool)
-
-
- boolean
-
- Returns the result of JSON path predicate check for the specified JSON value.
- Only the first item of the result is taken into account. If the
- result is not Boolean, then null is returned.
-
-
-
- jsonb_path_match('{"a":[1,2,3,4,5]}', 'exists($.a[*] ? (@ >= $min && @ <= $max))', '{"min":2,"max":4}')
-
-
-
- true
-
-
-
-
-
- jsonb_path_query(target jsonb, path jsonpath , vars jsonb , silent bool)
-
-
- jsonb_path_query_tz(target jsonb, path jsonpath , vars jsonb , silent bool)
-
-
- setof jsonb
-
- Gets all JSON items returned by JSON path for the specified JSON
- value.
-
-
-
- select * from jsonb_path_query('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ >= $min && @ <= $max)', '{"min":2,"max":4}');
-
-
-
-
-
- jsonb_path_query
-------------------
- 2
- 3
- 4
-
-
-
-
-
-
-
- jsonb_path_query_array(target jsonb, path jsonpath , vars jsonb , silent bool)
-
-
- jsonb_path_query_array_tz(target jsonb, path jsonpath , vars jsonb , silent bool)
-
-
- jsonb
-
- Gets all JSON items returned by JSON path for the specified JSON
- value and wraps result into an array.
-
-
-
- jsonb_path_query_array('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ >= $min && @ <= $max)', '{"min":2,"max":4}')
-
-
-
- [2, 3, 4]
-
-
-
-
-
- jsonb_path_query_first(target jsonb, path jsonpath , vars jsonb , silent bool)
-
-
- jsonb_path_query_first_tz(target jsonb, path jsonpath , vars jsonb , silent bool)
-
-
- jsonb
-
- Gets the first JSON item returned by JSON path for the specified JSON
- value. Returns NULL on no results.
-
-
-
- jsonb_path_query_first('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ >= $min && @ <= $max)', '{"min":2,"max":4}')
-
-
-
- 2
-
-
-
- Many of these functions and operators will convert Unicode escapes in
- JSON strings to the appropriate single character. This is a non-issue
- if the input is type jsonb, because the conversion was already
- done; but for json input, this may result in throwing an error,
- as noted in .
-
-
-
-
-
- The functions
- json[b]_populate_record,
- json[b]_populate_recordset,
- json[b]_to_record and
- json[b]_to_recordset
- operate on a JSON object, or array of objects, and extract the values
- associated with keys whose names match column names of the output row
- type.
- Object fields that do not correspond to any output column name are
- ignored, and output columns that do not match any object field will be
- filled with nulls.
- To convert a JSON value to the SQL type of an output column, the
- following rules are applied in sequence:
-
-
-
- A JSON null value is converted to a SQL null in all cases.
-
-
-
-
- If the output column is of type json
- or jsonb, the JSON value is just reproduced exactly.
-
-
-
-
- If the output column is a composite (row) type, and the JSON value is
- a JSON object, the fields of the object are converted to columns of
- the output row type by recursive application of these rules.
-
-
-
-
- Likewise, if the output column is an array type and the JSON value is
- a JSON array, the elements of the JSON array are converted to elements
- of the output array by recursive application of these rules.
-
-
-
-
- Otherwise, if the JSON value is a string literal, the contents of the
- string are fed to the input conversion function for the column's data
- type.
-
-
-
-
- Otherwise, the ordinary text representation of the JSON value is fed
- to the input conversion function for the column's data type.
-
-
-
-
-
-
- While the examples for these functions use constants, the typical use
- would be to reference a table in the FROM clause
- and use one of its json or jsonb columns
- as an argument to the function. Extracted key values can then be
- referenced in other parts of the query, like WHERE
- clauses and target lists. Extracting multiple values in this
- way can improve performance over extracting them separately with
- per-key operators.
-
-
-
-
-
- All the items of the path parameter of jsonb_set
- as well as jsonb_insert except the last item must be present
- in the target. If create_missing is false, all
- items of the path parameter of jsonb_set must be
- present. If these conditions are not met the target is
- returned unchanged.
-
-
- If the last path item is an object key, it will be created if it
- is absent and given the new value. If the last path item is an array
- index, if it is positive the item to set is found by counting from
- the left, and if negative by counting from the right - -1
- designates the rightmost element, and so on.
- If the item is out of the range -array_length .. array_length -1,
- and create_missing is true, the new value is added at the beginning
- of the array if the item is negative, and at the end of the array if
- it is positive.
-
-
-
-
-
- The json_typeof function's null return value
- should not be confused with a SQL NULL. While
- calling json_typeof('null'::json) will
- return null, calling json_typeof(NULL::json)
- will return a SQL NULL.
-
-
-
-
-
- If the argument to json_strip_nulls contains duplicate
- field names in any object, the result could be semantically somewhat
- different, depending on the order in which they occur. This is not an
- issue for jsonb_strip_nulls since jsonb values never have
- duplicate object field names.
-
-
-
-
-
- The jsonb_path_* functions have optional
- vars and silent arguments.
-
-
- If the vars argument is specified, it provides an
- object containing named variables to be substituted into a
- jsonpath expression.
-
-
- If the silent argument is specified and has the
- true value, these functions suppress the same errors
- as the @? and @@ operators.
-
-
-
-
- Some of the jsonb_path_* functions have a
- _tz suffix. These functions have been implemented to
- support comparison of date/time values that involves implicit
- timezone-aware casts. Since operations with time zones are not immutable,
- these functions are qualified as stable. Their counterparts without the
- suffix do not support such casts, so they are immutable and can be used for
- such use-cases as expression indexes
- (see ). There is no difference
- between these functions for other jsonpath operations.
+ The jsonpath operators @?
+ and @@ suppress the following errors: missing object
+ field or array element, unexpected JSON item type, datetime and numeric
+ errors. The jsonpath-related functions described below can
+ also be told to suppress these types of errors. This behavior might be
+ helpful when searching JSON document collections of varying structure.
- See also for the aggregate
- function json_agg which aggregates record
- values as JSON, and the aggregate function
- json_object_agg which aggregates pairs of values
- into a JSON object, and their jsonb equivalents,
- jsonb_agg and jsonb_object_agg.
+ shows the functions that are
+ available for constructing json and jsonb values.
-
-
-
- The SQL/JSON Path Language
-
- SQL/JSON path language
-
+
+ JSON Creation Functions
+
+
+
+
+ Function
+
+
+ Description
+
+
+ Example(s)
+
+
+
-
- SQL/JSON path expressions specify the items to be retrieved
- from the JSON data, similar to XPath expressions used
- for SQL access to XML. In PostgreSQL,
- path expressions are implemented as the jsonpath
- data type and can use any elements described in
- .
-
+
+
+
+
+ to_json
+
+ to_json ( anyelement )
+ json
+
+
+
+ to_jsonb
+
+ to_jsonb ( anyelement )
+ jsonb
+
+
+ Converts any SQL value to json or jsonb.
+ Arrays and composites are converted recursively to arrays and
+ objects (multidimensional arrays become arrays of arrays in JSON).
+ Otherwise, if there is a cast from the SQL data type
+ to json, the cast function will be used to perform the
+ conversion;
+
+ For example, the extension has a cast
+ from hstore to json, so that
+ hstore values converted via the JSON creation functions
+ will be represented as JSON objects, not as primitive string values.
+
+
+ otherwise, a scalar JSON value is produced. For any scalar other than
+ a number, a Boolean, or a null value, the text representation will be
+ used, with escaping as necessary to make it a valid JSON string value.
+
+
+ to_json('Fred said "Hi."'::text)
+ "Fred said \"Hi.\""
+
+
+ to_jsonb(row(42, 'Fred said "Hi."'::text))
+ {"f1": 42, "f2": "Fred said \"Hi.\""}
+
+
- JSON query functions and operators
- pass the provided path expression to the path engine
- for evaluation. If the expression matches the queried JSON data,
- the corresponding SQL/JSON item is returned.
- Path expressions are written in the SQL/JSON path language
- and can also include arithmetic expressions and functions.
- Query functions treat the provided expression as a
- text string, so it must be enclosed in single quotes.
-
+
+
+
+ array_to_json
+
+ array_to_json ( anyarray , boolean )
+ json
+
+
+ Converts a SQL array to a JSON array. The behavior is the same
+ as to_json except that line feeds will be added
+ between top-level array elements if the optional boolean parameter is
+ true.
+
+
+ array_to_json('{{1,5},{99,100}}'::int[])
+ [[1,5],[99,100]]
+
+
-
- A path expression consists of a sequence of elements allowed
- by the jsonpath data type.
- The path expression is evaluated from left to right, but
- you can use parentheses to change the order of operations.
- If the evaluation is successful, a sequence of SQL/JSON items
- (SQL/JSON sequence) is produced,
- and the evaluation result is returned to the JSON query function
- that completes the specified computation.
-
+
+
+
+ row_to_json
+
+ row_to_json ( record , boolean )
+ json
+
+
+ Converts a SQL composite value to a JSON object. The behavior is the
+ same as to_json except that line feeds will be
+ added between top-level elements if the optional boolean parameter is
+ true.
+
+
+ row_to_json(row(1,'foo'))
+ {"f1":1,"f2":"foo"}
+
+
-
- To refer to the JSON data to be queried (the
- context item), use the $ sign
- in the path expression. It can be followed by one or more
- accessor operators,
- which go down the JSON structure level by level to retrieve the
- content of context item. Each operator that follows deals with the
- result of the previous evaluation step.
-
+
+
+
+ json_build_array
+
+ json_build_array ( VARIADIC "any" )
+ json
+
+
+
+ jsonb_build_array
+
+ jsonb_build_array ( VARIADIC "any" )
+ jsonb
+
+
+ Builds a possibly-heterogeneously-typed JSON array out of a variadic
+ argument list. Each argument is converted as
+ per to_json or to_jsonb.
+
+
+ json_build_array(1,2,'foo',4,5)
+ [1, 2, "foo", 4, 5]
+
+
-
- For example, suppose you have some JSON data from a GPS tracker that you
- would like to parse, such as:
-
-{
- "track": {
- "segments": [
- {
- "location": [ 47.763, 13.4034 ],
- "start time": "2018-10-14 10:05:14",
- "HR": 73
- },
- {
- "location": [ 47.706, 13.2635 ],
- "start time": "2018-10-14 10:39:21",
- "HR": 135
- }
- ]
- }
-}
-
-
+
+
+
+ json_build_object
+
+ json_build_object ( VARIADIC "any" )
+ json
+
+
+
+ jsonb_build_object
+
+ jsonb_build_object ( VARIADIC "any" )
+ jsonb
+
+
+ Builds a JSON object out of a variadic argument list. By convention,
+ the argument list consists of alternating keys and values. Key
+ arguments are coerced to text; value arguments are converted as
+ per to_json or to_jsonb.
+
+
+ json_build_object('foo',1,2,row(3,'bar'))
+ {"foo" : 1, "2" : {"f1":3,"f2":"bar"}}
+
+
-
- To retrieve the available track segments, you need to use the
- .key accessor
- operator for all the preceding JSON objects:
-
-'$.track.segments'
-
-
+
+
+
+ json_object
+
+ json_object ( text[] )
+ json
+
+
+
+ jsonb_object
+
+ jsonb_object ( text[] )
+ jsonb
+
+
+ Builds a JSON object out of a text array. The array must have either
+ exactly one dimension with an even number of members, in which case
+ they are taken as alternating key/value pairs, or two dimensions
+ such that each inner array has exactly two elements, which
+ are taken as a key/value pair. All values are converted to JSON
+ strings.
+
+
+ json_object('{a, 1, b, "def", c, 3.5}')
+ {"a" : "1", "b" : "def", "c" : "3.5"}
+
+ json_object('{{a, 1},{b, "def"},{c, 3.5}}')
+ {"a" : "1", "b" : "def", "c" : "3.5"}
+
+
+
+
+
+ json_object ( keys text[], values text[] )
+ json
+
+
+ jsonb_object ( keys text[], values text[] )
+ jsonb
+
+
+ This form of json_object takes keys and values
+ pairwise from separate text arrays. Otherwise it is identical to
+ the one-argument form.
+
+
+ json_object('{a, b}', '{1,2}')
+ {"a": "1", "b": "2"}
+
+
+
+
+
- If the item to retrieve is an element of an array, you have
- to unnest this array using the [*] operator. For example,
- the following path will return location coordinates for all
- the available track segments:
-
-'$.track.segments[*].location'
-
+ shows the functions that
+ are available for processing json and jsonb values.
-
- To return the coordinates of the first segment only, you can
- specify the corresponding subscript in the []
- accessor operator. Note that the SQL/JSON arrays are 0-relative:
+
+ JSON Processing Functions
+
+
+
+
+ Function
+
+
+ Description
+
+
+ Example(s)
+
+
+
+
+
+
+
+
+ json_array_elements
+
+ json_array_elements ( json )
+ setof json
+
+
+
+ jsonb_array_elements
+
+ jsonb_array_elements ( jsonb )
+ setof jsonb
+
+
+ Expands the top-level JSON array into a set of JSON values.
+
+
+ select * from json_array_elements('[1,true, [2,false]]')
+
-'$.track.segments[0].location'
+ value
+-----------
+ 1
+ true
+ [2,false]
-
+
+
-
- The result of each path evaluation step can be processed
- by one or more jsonpath operators and methods
- listed in .
- Each method name must be preceded by a dot. For example,
- you can get an array size:
+
+
+
+ json_array_elements_text
+
+ json_array_elements_text ( json )
+ setof text
+
+
+
+ jsonb_array_elements_text
+
+ jsonb_array_elements_text ( jsonb )
+ setof text
+
+
+ Expands the top-level JSON array into a set of text values.
+
+
+ select * from json_array_elements_text('["foo", "bar"]')
+
-'$.track.segments.size()'
+ value
+-----------
+ foo
+ bar
- For more examples of using jsonpath operators
- and methods within path expressions, see
- .
-
+
+
-
- When defining the path, you can also use one or more
- filter expressions that work similar to the
- WHERE clause in SQL. A filter expression begins with
- a question mark and provides a condition in parentheses:
+
+
+
+ json_array_length
+
+ json_array_length ( json )
+ integer
+
+
+
+ jsonb_array_length
+
+ jsonb_array_length ( jsonb )
+ integer
+
+
+ Returns the number of elements in the top-level JSON array.
+
+
+ json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]')
+ 5
+
+
+
+
+
+ json_each
+
+ json_each ( json )
+ setof key text,
+ value json
+
+
+
+ jsonb_each
+
+ jsonb_each ( jsonb )
+ setof key text,
+ value jsonb
+
+
+ Expands the top-level JSON object into a set of key/value pairs.
+
+
+ select * from json_each('{"a":"foo", "b":"bar"}')
+
-? (condition)
+ key | value
+-----+-------
+ a | "foo"
+ b | "bar"
-
-
-
- Filter expressions must be specified right after the path evaluation step
- to which they are applied. The result of this step is filtered to include
- only those items that satisfy the provided condition. SQL/JSON defines
- three-valued logic, so the condition can be true, false,
- or unknown. The unknown value
- plays the same role as SQL NULL and can be tested
- for with the is unknown predicate. Further path
- evaluation steps use only those items for which filter expressions
- return true.
-
-
-
- Functions and operators that can be used in filter expressions are listed
- in . The path
- evaluation result to be filtered is denoted by the @
- variable. To refer to a JSON element stored at a lower nesting level,
- add one or more accessor operators after @.
-
+
+
-
- Suppose you would like to retrieve all heart rate values higher
- than 130. You can achieve this using the following expression:
+
+
+
+ json_each_text
+
+ json_each_text ( json )
+ setof key text,
+ value text
+
+
+
+ jsonb_each_text
+
+ jsonb_each_text ( jsonb )
+ setof key text,
+ value text
+
+
+ Expands the top-level JSON object into a set of key/value pairs.
+ The returned values will be of
+ type text.
+
+
+ select * from json_each_text('{"a":"foo", "b":"bar"}')
+
-'$.track.segments[*].HR ? (@ > 130)'
+ key | value
+-----+-------
+ a | foo
+ b | bar
-
+
+
-
- To get the start time of segments with such values instead, you have to
- filter out irrelevant segments before returning the start time, so the
- filter expression is applied to the previous step, and the path used
- in the condition is different:
+
+
+
+ json_extract_path
+
+ json_extract_path ( from_json json, VARIADIC path_elems text[] )
+ json
+
+
+
+ jsonb_extract_path
+
+ jsonb_extract_path ( from_json jsonb, VARIADIC path_elems text[] )
+ jsonb
+
+
+ Extracts JSON sub-object at the specified path.
+ (This is functionally equivalent to the #>
+ operator, but writing the path out as a variadic list can be more
+ convenient in some cases.)
+
+
+ json_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}', 'f4', 'f6')
+ "foo"
+
+
+
+
+
+
+ json_extract_path_text
+
+ json_extract_path_text ( from_json json, VARIADIC path_elems text[] )
+ text
+
+
+
+ jsonb_extract_path_text
+
+ jsonb_extract_path_text ( from_json jsonb, VARIADIC path_elems text[] )
+ text
+
+
+ Extracts JSON sub-object at the specified path as text.
+ (This is functionally equivalent to the #>>
+ operator.)
+
+
+ json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}', 'f4', 'f6')
+ foo
+
+
+
+
+
+
+ json_object_keys
+
+ json_object_keys ( json )
+ setof text
+
+
+
+ jsonb_object_keys
+
+ jsonb_object_keys ( jsonb )
+ setof text
+
+
+ Returns the set of keys in the top-level JSON object.
+
+
+ select * from json_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}}')
+
-'$.track.segments[*] ? (@.HR > 130)."start time"'
+ json_object_keys
+------------------
+ f1
+ f2
-
+
+
-
- You can use several filter expressions on the same nesting level, if
- required. For example, the following expression selects all segments
- that contain locations with relevant coordinates and high heart rate values:
+
+
+
+ json_populate_record
+
+ json_populate_record ( base anyelement, from_json json )
+ anyelement
+
+
+
+ jsonb_populate_record
+
+ jsonb_populate_record ( base anyelement, from_json jsonb )
+ anyelement
+
+
+ Expands the top-level JSON object to a row having the composite type
+ of the base argument. The JSON object
+ is scanned for fields whose names match column names of the output row
+ type, and their values are inserted into those columns of the output.
+ (Fields that do not correspond to any output column name are ignored.)
+ In typical use, the value of base is just
+ NULL, which means that any output columns that do
+ not match any object field will be filled with nulls. However,
+ if base isn't NULL then
+ the values it contains will be used for unmatched columns.
+
+
+ To convert a JSON value to the SQL type of an output column, the
+ following rules are applied in sequence:
+
+
+
+ A JSON null value is converted to a SQL null in all cases.
+
+
+
+
+ If the output column is of type json
+ or jsonb, the JSON value is just reproduced exactly.
+
+
+
+
+ If the output column is a composite (row) type, and the JSON value
+ is a JSON object, the fields of the object are converted to columns
+ of the output row type by recursive application of these rules.
+
+
+
+
+ Likewise, if the output column is an array type and the JSON value
+ is a JSON array, the elements of the JSON array are converted to
+ elements of the output array by recursive application of these
+ rules.
+
+
+
+
+ Otherwise, if the JSON value is a string, the contents of the
+ string are fed to the input conversion function for the column's
+ data type.
+
+
+
+
+ Otherwise, the ordinary text representation of the JSON value is
+ fed to the input conversion function for the column's data type.
+
+
+
+
+
+ While the example below uses a constant JSON value, typical use would
+ be to reference a json or jsonb column
+ laterally from another table in the query's FROM
+ clause. Writing json_populate_record in
+ the FROM clause is good practice, since all of the
+ extracted columns are available for use without duplicate function
+ calls.
+
+
+ select * from json_populate_record(null::myrowtype, '{"a": 1, "b": ["2", "a b"], "c": {"d": 4, "e": "a b c"}}')
+
-'$.track.segments[*] ? (@.location[1] < 13.4) ? (@.HR > 130)."start time"'
+ a | b | c
+---+-----------+-------------
+ 1 | {2,"a b"} | (4,"a b c")
-
+
+
-
- Using filter expressions at different nesting levels is also allowed.
- The following example first filters all segments by location, and then
- returns high heart rate values for these segments, if available:
+
+
+
+ json_populate_recordset
+
+ json_populate_recordset ( base anyelement, from_json json )
+ setof anyelement
+
+
+
+ jsonb_populate_recordset
+
+ jsonb_populate_recordset ( base anyelement, from_json jsonb )
+ setof anyelement
+
+
+ Expands the top-level JSON array of objects to a set of rows having
+ the composite type of the base argument.
+ Each element of the JSON array is processed as described above
+ for json[b]_populate_record.
+
+
+ select * from json_populate_recordset(null::myrowtype, '[{"a":1,"b":2},{"a":3,"b":4}]')
+
-'$.track.segments[*] ? (@.location[1] < 13.4).HR ? (@ > 130)'
+ a | b
+---+---
+ 1 | 2
+ 3 | 4
-
+
+
-
- You can also nest filter expressions within each other:
+
+
+
+ json_to_record
+
+ json_to_record ( json )
+ record
+
+
+
+ jsonb_to_record
+
+ jsonb_to_record ( jsonb )
+ record
+
+
+ Expands the top-level JSON object to a row having the composite type
+ defined by an AS clause. (As with all functions
+ returning record, the calling query must explicitly
+ define the structure of the record with an AS
+ clause.) The output record is filled from fields of the JSON object,
+ in the same way as described above
+ for json[b]_populate_record. Since there is no
+ input record value, unmatched columns are always filled with nulls.
+
+
+ select * from json_to_record('{"a":1,"b":[1,2,3],"c":[1,2,3],"e":"bar","r": {"a": 123, "b": "a b c"}}') as x(a int, b text, c int[], d text, r myrowtype)
+
-'$.track ? (exists(@.segments[*] ? (@.HR > 130))).segments.size()'
+ a | b | c | d | r
+---+---------+---------+---+---------------
+ 1 | [1,2,3] | {1,2,3} | | (123,"a b c")
- This expression returns the size of the track if it contains any
- segments with high heart rate values, or an empty sequence otherwise.
-
-
-
- PostgreSQL's implementation of SQL/JSON path
- language has the following deviations from the SQL/JSON standard:
-
+
+
-
-
-
- A path expression can be a Boolean predicate, although the SQL/JSON
- standard allows predicates only in filters. This is necessary for
- implementation of the @@ operator. For example,
- the following jsonpath expression is valid in
- PostgreSQL:
+
+
+
+ json_to_recordset
+
+ json_to_recordset ( json )
+ setof record
+
+
+
+ jsonb_to_recordset
+
+ jsonb_to_recordset ( jsonb )
+ setof record
+
+
+ Expands the top-level JSON array of objects to a set of rows having
+ the composite type defined by an AS clause. (As
+ with all functions returning record, the calling query
+ must explicitly define the structure of the record with
+ an AS clause.) Each element of the JSON array is
+ processed as described above
+ for json[b]_populate_record.
+
+
+ select * from json_to_recordset('[{"a":1,"b":"foo"},{"a":"2","c":"bar"}]') as x(a int, b text)
+
-'$.track.segments[*].HR < 70'
+ a | b
+---+-----
+ 1 | foo
+ 2 |
-
-
+
+
-
-
- There are minor differences in the interpretation of regular
- expression patterns used in like_regex filters, as
- described in .
-
-
-
+
+
+
+ jsonb_set
+
+ jsonb_set ( target jsonb, path text[], new_value jsonb , create_if_missing boolean )
+ jsonb
+
+
+ Returns target
+ with the item designated by path
+ replaced by new_value, or with
+ new_value added if
+ create_if_missing is true (which is the
+ default) and the item designated by path
+ does not exist.
+ All earlier steps in the path must exist, or
+ the target is returned unchanged.
+ As with the path oriented operators, negative integers that
+ appear in the path count from the end
+ of JSON arrays.
+ If the last path step is an array index that is out of range,
+ and create_if_missing is true, the new
+ value is added at the beginning of the array if the index is negative,
+ or at the end of the array if it is positive.
+
+
+ jsonb_set('[{"f1":1,"f2":null},2,null,3]', '{0,f1}','[2,3,4]', false)
+ [{"f1": [2, 3, 4], "f2": null}, 2, null, 3]
+
+
+ jsonb_set('[{"f1":1,"f2":null},2]', '{0,f3}','[2,3,4]')
+ [{"f1": 1, "f2": null, "f3": [2, 3, 4]}, 2]
+
+
-
- Strict and Lax Modes
-
- When you query JSON data, the path expression may not match the
- actual JSON data structure. An attempt to access a non-existent
- member of an object or element of an array results in a
- structural error. SQL/JSON path expressions have two modes
- of handling structural errors:
-
+
+
+
+ jsonb_set_lax
+
+ jsonb_set_lax ( target jsonb, path text[], new_value jsonb , create_if_missing boolean , null_value_treatment text )
+ jsonb
+
+
+ If new_value is not null,
+ behaves identically to jsonb_set. Otherwise behaves
+ according to the value
+ of null_value_treatment which must be one
+ of 'raise_exception',
+ 'use_json_null', 'delete_key', or
+ 'return_target'. The default is
+ 'use_json_null'.
+
+
+ jsonb_set_lax('[{"f1":1,"f2":null},2,null,3]', '{0,f1}',null)
+ [{"f1":null,"f2":null},2,null,3]
+
+
+ jsonb_set_lax('[{"f1":99,"f2":null},2]', '{0,f3}',null, true, 'return_target')
+ [{"f1": 99, "f2": null}, 2]
+
+
+
+
+
+
+ jsonb_insert
+
+ jsonb_insert ( target jsonb, path text[], new_value jsonb , insert_after boolean )
+ jsonb
+
+
+ Returns target
+ with new_value inserted. If the item
+ designated by the path is an array
+ element, new_value will be inserted before
+ that item if insert_after is false (which
+ is the default), or after it
+ if insert_after is true. If the item
+ designated by the path is an object
+ field, new_value will be inserted only if
+ the object does not already contain that key.
+ All earlier steps in the path must exist, or
+ the target is returned unchanged.
+ As with the path oriented operators, negative integers that
+ appear in the path count from the end
+ of JSON arrays.
+ If the last path step is an array index that is out of range, the new
+ value is added at the beginning of the array if the index is negative,
+ or at the end of the array if it is positive.
+
+
+ jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"new_value"')
+ {"a": [0, "new_value", 1, 2]}
+
+
+ jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"new_value"', true)
+ {"a": [0, 1, "new_value", 2]}
+
+
+
+
+
+
+ json_strip_nulls
+
+ json_strip_nulls ( json )
+ json
+
+
+
+ jsonb_strip_nulls
+
+ jsonb_strip_nulls ( jsonb )
+ jsonb
+
+
+ Deletes all object fields that have null values from the given JSON
+ value, recursively. Null values that are not object fields are
+ untouched.
+
+
+ json_strip_nulls('[{"f1":1,"f2":null},2,null,3]')
+ [{"f1":1},2,null,3]
+
+
+
+
+
+
+ jsonb_path_exists
+
+ jsonb_path_exists ( target jsonb, path jsonpath , vars jsonb , silent boolean )
+ boolean
+
+
+ Checks whether the JSON path returns any item for the specified JSON
+ value.
+ If the vars argument is specified, it must
+ be a JSON object, and its fields provide named values to be
+ substituted into the jsonpath expression.
+ If the silent argument is specified and
+ is true, the function suppresses the same errors
+ as the @? and @@ operators do.
+
+
+ jsonb_path_exists('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ >= $min && @ <= $max)', '{"min":2,"max":4}')
+ t
+
+
+
+
+
+
+ jsonb_path_match
+
+ jsonb_path_match ( target jsonb, path jsonpath , vars jsonb , silent boolean )
+ boolean
+
+
+ Returns the result of a JSON path predicate check for the specified
+ JSON value. Only the first item of the result is taken into account.
+ If the result is not Boolean, then NULL is returned.
+ The optional vars
+ and silent arguments act the same as
+ for jsonb_path_exists.
+
+
+ jsonb_path_match('{"a":[1,2,3,4,5]}', 'exists($.a[*] ? (@ >= $min && @ <= $max))', '{"min":2,"max":4}')
+ t
+
+
+
+
+
+
+ jsonb_path_query
+
+ jsonb_path_query ( target jsonb, path jsonpath , vars jsonb , silent boolean )
+ setof jsonb
+
+
+ Returns all JSON items returned by the JSON path for the specified
+ JSON value.
+ The optional vars
+ and silent arguments act the same as
+ for jsonb_path_exists.
+
+
+ select * from jsonb_path_query('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ >= $min && @ <= $max)', '{"min":2,"max":4}')
+
+
+ jsonb_path_query
+------------------
+ 2
+ 3
+ 4
+
+
+
+
+
+
+
+ jsonb_path_query_array
+
+ jsonb_path_query_array ( target jsonb, path jsonpath , vars jsonb , silent boolean )
+ jsonb
+
+
+ Returns all JSON items returned by the JSON path for the specified
+ JSON value, as a JSON array.
+ The optional vars
+ and silent arguments act the same as
+ for jsonb_path_exists.
+
+
+ jsonb_path_query_array('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ >= $min && @ <= $max)', '{"min":2,"max":4}')
+ [2, 3, 4]
+
+
+
+
+
+
+ jsonb_path_query_first
+
+ jsonb_path_query_first ( target jsonb, path jsonpath , vars jsonb , silent boolean )
+ jsonb
+
+
+ Returns the first JSON item returned by the JSON path for the
+ specified JSON value. Returns NULL if there are no
+ results.
+ The optional vars
+ and silent arguments act the same as
+ for jsonb_path_exists.
+
+
+ jsonb_path_query_first('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ >= $min && @ <= $max)', '{"min":2,"max":4}')
+ 2
+
+
+
+
+
+
+ jsonb_path_exists_tz
+
+ jsonb_path_exists_tz ( target jsonb, path jsonpath , vars jsonb , silent boolean )
+ boolean
+
+
+
+ jsonb_path_match_tz
+
+ jsonb_path_match_tz ( target jsonb, path jsonpath , vars jsonb , silent boolean )
+ boolean
+
+
+
+ jsonb_path_query_tz
+
+ jsonb_path_query_tz ( target jsonb, path jsonpath , vars jsonb , silent boolean )
+ setof jsonb
+
+
+
+ jsonb_path_query_array_tz
+
+ jsonb_path_query_array_tz ( target jsonb, path jsonpath , vars jsonb , silent boolean )
+ jsonb
+
+
+
+ jsonb_path_query_first_tz
+
+ jsonb_path_query_first_tz ( target jsonb, path jsonpath , vars jsonb , silent boolean )
+ jsonb
+
+
+ These functions act like their counterparts described above without
+ the _tz suffix, except that these functions support
+ comparisons of date/time values that require timezone-aware
+ conversions. The example below requires interpretation of the
+ date-only value 2015-08-02 as a timestamp with time
+ zone, so the result depends on the current
+ setting. Due to this dependency, these
+ functions are marked as stable, which means these functions cannot be
+ used in indexes. Their counterparts are immutable, and so can be used
+ in indexes; but they will throw errors if asked to make such
+ comparisons.
+
+
+ jsonb_path_exists_tz('["2015-08-01 12:00:00 -05"]', '$[*] ? (@.datetime() < "2015-08-02".datetime())')
+ t
+
+
+
+
+
+
+ jsonb_pretty
+
+ jsonb_pretty ( jsonb )
+ text
+
+
+ Converts the given JSON value to pretty-printed, indented text.
+
+
+ jsonb_pretty('[{"f1":1,"f2":null},2]')
+
+
+[
+ {
+ "f1": 1,
+ "f2": null
+ },
+ 2
+]
+
+
+
+
+
+
+
+ json_typeof
+
+ json_typeof ( json )
+ text
+
+
+
+ jsonb_typeof
+
+ jsonb_typeof ( jsonb )
+ text
+
+
+ Returns the type of the top-level JSON value as a text string.
+ Possible types are
+ object, array,
+ string, number,
+ boolean, and null.
+ (The null result should not be confused
+ with a SQL NULL; see the examples.)
+
+
+ json_typeof('-123.4')
+ number
+
+
+ json_typeof('null'::json)
+ null
+
+
+ json_typeof(NULL::json) IS NULL
+ t
+
+
+
+
+
+
+
+ See also for the aggregate
+ function json_agg which aggregates record
+ values as JSON, the aggregate function
+ json_object_agg which aggregates pairs of values
+ into a JSON object, and their jsonb equivalents,
+ jsonb_agg and jsonb_object_agg.
+
+
+
+
+ The SQL/JSON Path Language
+
+
+ SQL/JSON path language
+
+
+
+ SQL/JSON path expressions specify the items to be retrieved
+ from the JSON data, similar to XPath expressions used
+ for SQL access to XML. In PostgreSQL,
+ path expressions are implemented as the jsonpath
+ data type and can use any elements described in
+ .
+
+
+
+ JSON query functions and operators
+ pass the provided path expression to the path engine
+ for evaluation. If the expression matches the queried JSON data,
+ the corresponding JSON item, or set of items, is returned.
+ Path expressions are written in the SQL/JSON path language
+ and can include arithmetic expressions and functions.
+
+
+
+ A path expression consists of a sequence of elements allowed
+ by the jsonpath data type.
+ The path expression is normally evaluated from left to right, but
+ you can use parentheses to change the order of operations.
+ If the evaluation is successful, a sequence of JSON items is produced,
+ and the evaluation result is returned to the JSON query function
+ that completes the specified computation.
+
+
+
+ To refer to the JSON value being queried (the
+ context item), use the $ variable
+ in the path expression. It can be followed by one or more
+ accessor operators,
+ which go down the JSON structure level by level to retrieve sub-items
+ of the context item. Each operator that follows deals with the
+ result of the previous evaluation step.
+
+
+
+ For example, suppose you have some JSON data from a GPS tracker that you
+ would like to parse, such as:
+
+{
+ "track": {
+ "segments": [
+ {
+ "location": [ 47.763, 13.4034 ],
+ "start time": "2018-10-14 10:05:14",
+ "HR": 73
+ },
+ {
+ "location": [ 47.706, 13.2635 ],
+ "start time": "2018-10-14 10:39:21",
+ "HR": 135
+ }
+ ]
+ }
+}
+
+
+
+
+ To retrieve the available track segments, you need to use the
+ .key accessor
+ operator to descend through surrounding JSON objects:
+
+$.track.segments
+
+
+
+
+ To retrieve the contents of an array, you typically use the
+ [*] operator. For example,
+ the following path will return the location coordinates for all
+ the available track segments:
+
+$.track.segments[*].location
+
+
+
+
+ To return the coordinates of the first segment only, you can
+ specify the corresponding subscript in the []
+ accessor operator. Recall that JSON array indexes are 0-relative:
+
+$.track.segments[0].location
+
+
+
+
+ The result of each path evaluation step can be processed
+ by one or more jsonpath operators and methods
+ listed in .
+ Each method name must be preceded by a dot. For example,
+ you can get the size of an array:
+
+$.track.segments.size()
+
+ More examples of using jsonpath operators
+ and methods within path expressions appear below in
+ .
+
+
+
+ When defining a path, you can also use one or more
+ filter expressions that work similarly to the
+ WHERE clause in SQL. A filter expression begins with
+ a question mark and provides a condition in parentheses:
+
+
+? (condition)
+
+
+
+
+ Filter expressions must be written just after the path evaluation step
+ to which they should apply. The result of that step is filtered to include
+ only those items that satisfy the provided condition. SQL/JSON defines
+ three-valued logic, so the condition can be true, false,
+ or unknown. The unknown value
+ plays the same role as SQL NULL and can be tested
+ for with the is unknown predicate. Further path
+ evaluation steps use only those items for which the filter expression
+ returned true.
+
+
+
+ The functions and operators that can be used in filter expressions are
+ listed in . Within a
+ filter expression, the @ variable denotes the value
+ being filtered (i.e., one result of the preceding path step). You can
+ write accessor operators after @ to retrieve component
+ items.
+
+
+
+ For example, suppose you would like to retrieve all heart rate values higher
+ than 130. You can achieve this using the following expression:
+
+$.track.segments[*].HR ? (@ > 130)
+
+
+
+
+ To get the start times of segments with such values, you have to
+ filter out irrelevant segments before returning the start times, so the
+ filter expression is applied to the previous step, and the path used
+ in the condition is different:
+
+$.track.segments[*] ? (@.HR > 130)."start time"
+
+
+
+
+ You can use several filter expressions in sequence, if required. For
+ example, the following expression selects start times of all segments that
+ contain locations with relevant coordinates and high heart rate values:
+
+$.track.segments[*] ? (@.location[1] < 13.4) ? (@.HR > 130)."start time"
+
+
+
+
+ Using filter expressions at different nesting levels is also allowed.
+ The following example first filters all segments by location, and then
+ returns high heart rate values for these segments, if available:
+
+$.track.segments[*] ? (@.location[1] < 13.4).HR ? (@ > 130)
+
+
+
+
+ You can also nest filter expressions within each other:
+
+$.track ? (exists(@.segments[*] ? (@.HR > 130))).segments.size()
+
+ This expression returns the size of the track if it contains any
+ segments with high heart rate values, or an empty sequence otherwise.
+
+
+
+ PostgreSQL's implementation of the SQL/JSON path
+ language has the following deviations from the SQL/JSON standard:
+
+
+
+
+
+ A path expression can be a Boolean predicate, although the SQL/JSON
+ standard allows predicates only in filters. This is necessary for
+ implementation of the @@ operator. For example,
+ the following jsonpath expression is valid in
+ PostgreSQL:
+
+$.track.segments[*].HR < 70
+
+
+
+
+
+
+ There are minor differences in the interpretation of regular
+ expression patterns used in like_regex filters, as
+ described in .
+
+
+
+
+
+ Strict and Lax Modes
+
+ When you query JSON data, the path expression may not match the
+ actual JSON data structure. An attempt to access a non-existent
+ member of an object or element of an array results in a
+ structural error. SQL/JSON path expressions have two modes
+ of handling structural errors:
+
+
+
+
+
+ lax (default) — the path engine implicitly adapts
+ the queried data to the specified path.
+ Any remaining structural errors are suppressed and converted
+ to empty SQL/JSON sequences.
+
+
+
+
+ strict — if a structural error occurs, an error is raised.
+
+
+
+
+
+ The lax mode facilitates matching of a JSON document structure and path
+ expression if the JSON data does not conform to the expected schema.
+ If an operand does not match the requirements of a particular operation,
+ it can be automatically wrapped as an SQL/JSON array or unwrapped by
+ converting its elements into an SQL/JSON sequence before performing
+ this operation. Besides, comparison operators automatically unwrap their
+ operands in the lax mode, so you can compare SQL/JSON arrays
+ out-of-the-box. An array of size 1 is considered equal to its sole element.
+ Automatic unwrapping is not performed only when:
+
+
+
+ The path expression contains type() or
+ size() methods that return the type
+ and the number of elements in the array, respectively.
+
+
+
+
+ The queried JSON data contain nested arrays. In this case, only
+ the outermost array is unwrapped, while all the inner arrays
+ remain unchanged. Thus, implicit unwrapping can only go one
+ level down within each path evaluation step.
+
+
+
+
+
+
+ For example, when querying the GPS data listed above, you can
+ abstract from the fact that it stores an array of segments
+ when using the lax mode:
+
+lax $.track.segments.location
+
+
+
+
+ In the strict mode, the specified path must exactly match the structure of
+ the queried JSON document to return an SQL/JSON item, so using this
+ path expression will cause an error. To get the same result as in
+ the lax mode, you have to explicitly unwrap the
+ segments array:
+
+strict $.track.segments[*].location
+
+
+
+
+
+
+ SQL/JSON Path Operators and Methods
+
+
+ shows the operators and
+ methods available in jsonpath. Note that while the unary
+ operators and methods can be applied to multiple values resulting from a
+ preceding path step, the binary operators (addition etc.) can only be
+ applied to single values.
+
+
+
+ <type>jsonpath</type> Operators and Methods
+
+
+
+
+ Operator/Method
+
+
+ Description
+
+
+ Example(s)
+
+
+
+
+
+
+
+ number + number
+ number
+
+
+ Addition
+
+
+ jsonb_path_query('[2]', '$[0] + 3')
+ 5
+
+
+
+
+
+ + number
+ number
+
+
+ Unary plus (no operation); unlike addition, this can iterate over
+ multiple values
+
+
+ jsonb_path_query_array('{"x": [2,3,4]}', '+ $.x')
+ [2, 3, 4]
+
+
+
+
+
+ number - number
+ number
+
+
+ Subtraction
+
+
+ jsonb_path_query('[2]', '7 - $[0]')
+ 5
+
+
+
+
+
+ - number
+ number
+
+
+ Negation; unlike subtraction, this can iterate over
+ multiple values
+
+
+ jsonb_path_query_array('{"x": [2,3,4]}', '- $.x')
+ [-2, -3, -4]
+
+
+
+
+
+ number * number
+ number
+
+
+ Multiplication
+
+
+ jsonb_path_query('[4]', '2 * $[0]')
+ 8
+
+
+
+
+
+ number / number
+ number
+
+
+ Division
+
+
+ jsonb_path_query('[8.5]', '$[0] / 2')
+ 4.2500000000000000
+
+
+
+
+
+ number % number
+ number
+
+
+ Modulo (remainder)
+
+
+ jsonb_path_query('[32]', '$[0] % 10')
+ 2
+
+
+
+
+
+ value . type()
+ string
+
+
+ Type of the JSON item (see json_typeof)
+
+
+ jsonb_path_query_array('[1, "2", {}]', '$[*].type()')
+ ["number", "string", "object"]
+
+
+
+
+
+ value . size()
+ number
+
+
+ Size of the JSON item (number of array elements, or 1 if not an
+ array)
+
+
+ jsonb_path_query('{"m": [11, 15]}', '$.m.size()')
+ 2
+
+
+
+
+
+ value . double()
+ number
+
+
+ Approximate floating-point number converted from a JSON number or
+ string
+
+
+ jsonb_path_query('{"len": "1.9"}', '$.len.double() * 2')
+ 3.8
+
+
+
+
+
+ number . ceiling()
+ number
+
+
+ Nearest integer greater than or equal to the given number
+
+
+ jsonb_path_query('{"h": 1.3}', '$.h.ceiling()')
+ 2
+
+
+
+
+
+ number . floor()
+ number
+
+
+ Nearest integer less than or equal to the given number
+
+
+ jsonb_path_query('{"h": 1.7}', '$.h.floor()')
+ 1
+
+
+
+
+
+ number . abs()
+ number
+
+
+ Absolute value of the given number
+
+
+ jsonb_path_query('{"z": -0.3}', '$.z.abs()')
+ 0.3
+
+
+
+
+
+ string . datetime()
+ datetime_type
+ (see note)
+
+
+ Date/time value converted from a string
+
+
+ jsonb_path_query('["2015-8-1", "2015-08-12"]', '$[*] ? (@.datetime() < "2015-08-2".datetime())')
+ "2015-8-1"
+
+
+
+
+
+ string . datetime(template)
+ datetime_type
+ (see note)
+
+
+ Date/time value converted from a string using the
+ specified to_timestamp template
+
+
+ jsonb_path_query_array('["12:30", "18:40"]', '$[*].datetime("HH24:MI")')
+ ["12:30:00", "18:40:00"]
+
+
+
+
+
+ object . keyvalue()
+ array
+
+
+ The object's key-value pairs, represented as an array of objects
+ containing three fields: "key",
+ "value", and "id";
+ "id" is a unique identifier of the object the
+ key-value pair belongs to
+
+
+ jsonb_path_query_array('{"x": "20", "y": 32}', '$.keyvalue()')
+ [{"id": 0, "key": "x", "value": "20"}, {"id": 0, "key": "y", "value": 32}]
+
+
+
+
+
+
+
+
+ The result type of the datetime() and
+ datetime(template)
+ methods can be date, timetz, time,
+ timestamptz, or timestamp.
+ Both methods determine their result type dynamically.
+
+
+ The datetime() method sequentially tries to
+ match its input string to the ISO formats
+ for date, timetz, time,
+ timestamptz, and timestamp. It stops on
+ the first matching format and emits the corresponding data type.
+
+
+ The datetime(template)
+ method determines the result type according to the fields used in the
+ provided template string.
+
+
+ The datetime() and
+ datetime(template) methods
+ use the same parsing rules as the to_timestamp SQL
+ function does (see ), with three
+ exceptions. First, these methods don't allow unmatched template
+ patterns. Second, only the following separators are allowed in the
+ template string: minus sign, period, solidus (slash), comma, apostrophe,
+ semicolon, colon and space. Third, separators in the template string
+ must exactly match the input string.
+
+
+ If different date/time types need to be compared, an implicit cast is
+ applied. A date value can be cast to timestamp
+ or timestamptz, timestamp can be cast to
+ timestamptz, and time to timetz.
+ However, all but the first of these conversions depend on the current
+ setting, and thus can only be performed
+ within timezone-aware jsonpath functions.
+
+
+
+
+ shows the available
+ filter expression elements.
+
+
+
+ <type>jsonpath</type> Filter Expression Elements
+
+
+
+
+ Predicate/Value
+
+
+ Description
+
+
+ Example(s)
+
+
+
+
+
+
+
+ value == value
+ boolean
+
+
+ Equality comparison (this, and the other comparison operators, work on
+ all JSON scalar values)
+
+
+ jsonb_path_query_array('[1, "a", 1, 3]', '$[*] ? (@ == 1)')
+ [1, 1]
+
+
+ jsonb_path_query_array('[1, "a", 1, 3]', '$[*] ? (@ == "a")')
+ ["a"]
+
+
+
+
+
+ value != value
+ boolean
+
+
+ value <> value
+ boolean
+
+
+ Non-equality comparison
+
+
+ jsonb_path_query_array('[1, 2, 1, 3]', '$[*] ? (@ != 1)')
+ [2, 3]
+
+
+ jsonb_path_query_array('["a", "b", "c"]', '$[*] ? (@ <> "b")')
+ ["a", "c"]
+
+
+
+
+
+ value < value
+ boolean
+
+
+ Less-than comparison
+
+
+ jsonb_path_query_array('[1, 2, 3]', '$[*] ? (@ < 2)')
+ [1]
+
+
+
+
+
+ value <= value
+ boolean
+
+
+ Less-than-or-equal-to comparison
+
+
+ jsonb_path_query_array('["a", "b", "c"]', '$[*] ? (@ <= "b")')
+ ["a", "b"]
+
+
+
+
+
+ value > value
+ boolean
+
+
+ Greater-than comparison
+
+
+ jsonb_path_query_array('[1, 2, 3]', '$[*] ? (@ > 2)')
+ [3]
+
+
+
+
+
+ value >= value
+ boolean
+
+
+ Greater-than-or-equal-to comparison
+
+
+ jsonb_path_query_array('[1, 2, 3]', '$[*] ? (@ >= 2)')
+ [2, 3]
+
+
+
+
+
+ true
+ boolean
+
+
+ JSON constant true
+
+
+ jsonb_path_query('[{"name": "John", "parent": false}, {"name": "Chris", "parent": true}]', '$[*] ? (@.parent == true)')
+ {"name": "Chris", "parent": true}
+
+
+
+
+
+ false
+ boolean
+
+
+ JSON constant false
+
+
+ jsonb_path_query('[{"name": "John", "parent": false}, {"name": "Chris", "parent": true}]', '$[*] ? (@.parent == false)')
+ {"name": "John", "parent": false}
+
+
+
+
+
+ null
+ value
+
+
+ JSON constant null (note that, unlike in SQL,
+ comparison to null works normally)
+
+
+ jsonb_path_query('[{"name": "Mary", "job": null}, {"name": "Michael", "job": "driver"}]', '$[*] ? (@.job == null) .name')
+ "Mary"
+
+
+
+
+
+ boolean && boolean
+ boolean
+
+
+ Boolean AND
+
+
+ jsonb_path_query('[1, 3, 7]', '$[*] ? (@ > 1 && @ < 5)')
+ 3
+
+
+
+
+
+ boolean || boolean
+ boolean
+
+
+ Boolean OR
+
+
+ jsonb_path_query('[1, 3, 7]', '$[*] ? (@ < 1 || @ > 5)')
+ 7
+
+
+
+
+
+ ! boolean
+ boolean
+
+
+ Boolean NOT
+
+
+ jsonb_path_query('[1, 3, 7]', '$[*] ? (!(@ < 5))')
+ 7
+
+
-
-
-
- lax (default) — the path engine implicitly adapts
- the queried data to the specified path.
- Any remaining structural errors are suppressed and converted
- to empty SQL/JSON sequences.
-
-
-
-
- strict — if a structural error occurs, an error is raised.
-
-
-
+
+
+ boolean is unknown
+ boolean
+
+
+ Tests whether a Boolean condition is unknown.
+
+
+ jsonb_path_query('[-1, 2, 7, "foo"]', '$[*] ? ((@ > 0) is unknown)')
+ "foo"
+
+
-
- The lax mode facilitates matching of a JSON document structure and path
- expression if the JSON data does not conform to the expected schema.
- If an operand does not match the requirements of a particular operation,
- it can be automatically wrapped as an SQL/JSON array or unwrapped by
- converting its elements into an SQL/JSON sequence before performing
- this operation. Besides, comparison operators automatically unwrap their
- operands in the lax mode, so you can compare SQL/JSON arrays
- out-of-the-box. An array of size 1 is considered equal to its sole element.
- Automatic unwrapping is not performed only when:
-
-
-
- The path expression contains type() or
- size() methods that return the type
- and the number of elements in the array, respectively.
-
-
-
-
- The queried JSON data contain nested arrays. In this case, only
- the outermost array is unwrapped, while all the inner arrays
- remain unchanged. Thus, implicit unwrapping can only go one
- level down within each path evaluation step.
-
-
-
-
+
+
+ string like_regex string flag string
+ boolean
+
+
+ Tests whether the first operand matches the regular expression
+ given by the second operand, optionally with modifications
+ described by a string of flag characters (see
+ ).
+
+
+ jsonb_path_query_array('["abc", "abd", "aBdC", "abdacb", "babc"]', '$[*] ? (@ like_regex "^ab.*c")')
+ ["abc", "abdacb"]
+
+
+ jsonb_path_query_array('["abc", "abd", "aBdC", "abdacb", "babc"]', '$[*] ? (@ like_regex "^ab.*c" flag "i")')
+ ["abc", "aBdC", "abdacb"]
+
+
-
- For example, when querying the GPS data listed above, you can
- abstract from the fact that it stores an array of segments
- when using the lax mode:
-
-'lax $.track.segments.location'
-
-
+
+
+ string starts with string
+ boolean
+
+
+ Tests whether the second operand is an initial substring of the first
+ operand.
+
+
+ jsonb_path_query('["John Smith", "Mary Stone", "Bob Johnson"]', '$[*] ? (@ starts with "John")')
+ "John Smith"
+
+
-
- In the strict mode, the specified path must exactly match the structure of
- the queried JSON document to return an SQL/JSON item, so using this
- path expression will cause an error. To get the same result as in
- the lax mode, you have to explicitly unwrap the
- segments array:
-
-'strict $.track.segments[*].location'
-
-
+
+
+ exists ( path_expression )
+ boolean
+
+
+ Tests whether a path expression matches at least one SQL/JSON item.
+ Returns unknown if the path expression would result
+ in an error; the second example uses this to avoid a no-such-key error
+ in strict mode.
+
+
+ jsonb_path_query('{"x": [1, 2], "y": [2, 4]}', 'strict $.* ? (exists (@ ? (@[*] > 2)))')
+ [2, 4]
+
+
+ jsonb_path_query_array('{"value": 41}', 'strict $ ? (exists (@.name)) .name')
+ []
+
+
+
+
+
- Regular Expressions
+ SQL/JSON Regular Expressions
LIKE_REGEX
@@ -15835,7 +16751,7 @@ table2-mapping
following SQL/JSON path query would case-insensitively match all
strings in an array that start with an English vowel:
-'$[*] ? (@ like_regex "^[aeiou]" flag "i")'
+$[*] ? (@ like_regex "^[aeiou]" flag "i")
@@ -15872,340 +16788,9 @@ table2-mapping
backslashes you want to use in the regular expression must be doubled.
For example, to match strings that contain only digits:
-'$ ? (@ like_regex "^\\d+$")'
+$ ? (@ like_regex "^\\d+$")
-
-
-
-
- SQL/JSON Path Operators and Methods
-
-
- shows the operators and
- methods available in jsonpath. shows the available filter
- expression elements.
-
-
-
- <type>jsonpath</type> Operators and Methods
-
-
-
- Operator/Method
- Description
- Example JSON
- Example Query
- Result
-
-
-
-
- + (unary)
- Plus operator that iterates over the SQL/JSON sequence
- {"x": [2.85, -14.7, -9.4]}
- + $.x.floor()
- 2, -15, -10
-
-
- - (unary)
- Minus operator that iterates over the SQL/JSON sequence
- {"x": [2.85, -14.7, -9.4]}
- - $.x.floor()
- -2, 15, 10
-
-
- + (binary)
- Addition
- [2]
- 2 + $[0]
- 4
-
-
- - (binary)
- Subtraction
- [2]
- 4 - $[0]
- 2
-
-
- *
- Multiplication
- [4]
- 2 * $[0]
- 8
-
-
- /
- Division
- [8]
- $[0] / 2
- 4
-
-
- %
- Modulus
- [32]
- $[0] % 10
- 2
-
-
- type()
- Type of the SQL/JSON item
- [1, "2", {}]
- $[*].type()
- "number", "string", "object"
-
-
- size()
- Size of the SQL/JSON item
- {"m": [11, 15]}
- $.m.size()
- 2
-
-
- double()
- Approximate floating-point number converted from an SQL/JSON number or a string
- {"len": "1.9"}
- $.len.double() * 2
- 3.8
-
-
- ceiling()
- Nearest integer greater than or equal to the SQL/JSON number
- {"h": 1.3}
- $.h.ceiling()
- 2
-
-
- floor()
- Nearest integer less than or equal to the SQL/JSON number
- {"h": 1.3}
- $.h.floor()
- 1
-
-
- abs()
- Absolute value of the SQL/JSON number
- {"z": -0.3}
- $.z.abs()
- 0.3
-
-
- datetime()
- Date/time value converted from a string
- ["2015-8-1", "2015-08-12"]
- $[*] ? (@.datetime() < "2015-08-2". datetime())
- 2015-8-1
-
-
- datetime(template)
- Date/time value converted from a string using the specified template
- ["12:30", "18:40"]
- $[*].datetime("HH24:MI")
- "12:30:00", "18:40:00"
-
-
- keyvalue()
-
- Sequence of object's key-value pairs represented as array of items
- containing three fields ("key",
- "value", and "id").
- "id" is a unique identifier of the object
- key-value pair belongs to.
-
- {"x": "20", "y": 32}
- $.keyvalue()
- {"key": "x", "value": "20", "id": 0}, {"key": "y", "value": 32, "id": 0}
-
-
-
-
-
-
-
- The result type of datetime() and
- datetime(template)
- methods can be date, timetz, time,
- timestamptz, or timestamp.
- Both methods determine the result type dynamically.
-
-
- The datetime() method sequentially tries ISO formats
- for date, timetz, time,
- timestamptz, and timestamp. It stops on
- the first matching format and the corresponding data type.
-
-
- The datetime(template)
- method determines the result type by the provided template string.
-
-
- The datetime() and
- datetime(template) methods
- use the same parsing rules as the to_timestamp SQL
- function does (see ), with three
- exceptions. First, these methods don't allow unmatched template
- patterns. Second, only the following separators are allowed in the
- template string: minus sign, period, solidus (slash), comma, apostrophe,
- semicolon, colon and space. Third, separators in the template string
- must exactly match the input string.
-
-
-
-
- <type>jsonpath</type> Filter Expression Elements
-
-
-
- Value/Predicate
- Description
- Example JSON
- Example Query
- Result
-
-
-
-
- ==
- Equality operator
- [1, 2, 1, 3]
- $[*] ? (@ == 1)
- 1, 1
-
-
- !=
- Non-equality operator
- [1, 2, 1, 3]
- $[*] ? (@ != 1)
- 2, 3
-
-
- <>
- Non-equality operator (same as !=)
- [1, 2, 1, 3]
- $[*] ? (@ <> 1)
- 2, 3
-
-
- <
- Less-than operator
- [1, 2, 3]
- $[*] ? (@ < 2)
- 1
-
-
- <=
- Less-than-or-equal-to operator
- [1, 2, 3]
- $[*] ? (@ <= 2)
- 1, 2
-
-
- >
- Greater-than operator
- [1, 2, 3]
- $[*] ? (@ > 2)
- 3
-
-
- >=
- Greater-than-or-equal-to operator
- [1, 2, 3]
- $[*] ? (@ >= 2)
- 2, 3
-
-
- true
- Value used to perform comparison with JSON true literal
- [{"name": "John", "parent": false},
- {"name": "Chris", "parent": true}]
- $[*] ? (@.parent == true)
- {"name": "Chris", "parent": true}
-
-
- false
- Value used to perform comparison with JSON false literal
- [{"name": "John", "parent": false},
- {"name": "Chris", "parent": true}]
- $[*] ? (@.parent == false)
- {"name": "John", "parent": false}
-
-
- null
- Value used to perform comparison with JSON null value
- [{"name": "Mary", "job": null},
- {"name": "Michael", "job": "driver"}]
- $[*] ? (@.job == null) .name
- "Mary"
-
-
- &&
- Boolean AND
- [1, 3, 7]
- $[*] ? (@ > 1 && @ < 5)
- 3
-
-
- ||
- Boolean OR
- [1, 3, 7]
- $[*] ? (@ < 1 || @ > 5)
- 7
-
-
- !
- Boolean NOT
- [1, 3, 7]
- $[*] ? (!(@ < 5))
- 7
-
-
- like_regex
-
- Tests whether the first operand matches the regular expression
- given by the second operand, optionally with modifications
- described by a string of flag characters (see
- )
-
- ["abc", "abd", "aBdC", "abdacb", "babc"]
- $[*] ? (@ like_regex "^ab.*c" flag "i")
- "abc", "aBdC", "abdacb"
-
-
- starts with
- Tests whether the second operand is an initial substring of the first operand
- ["John Smith", "Mary Stone", "Bob Johnson"]
- $[*] ? (@ starts with "John")
- "John Smith"
-
-
- exists
- Tests whether a path expression matches at least one SQL/JSON item
- {"x": [1, 2], "y": [2, 4]}
- strict $.* ? (exists (@ ? (@[*] > 2)))
- 2, 4
-
-
- is unknown
- Tests whether a Boolean condition is unknown
- [-1, 2, 7, "infinity"]
- $[*] ? ((@ > 0) is unknown)
- "infinity"
-
-
-
-
-
-
-
- When different date/time values are compared, an implicit cast is
- applied. A date value can be cast to timestamp
- or timestamptz, timestamp can be cast to
- timestamptz, and time — to timetz.
-
-
