In this section, we give the temporal versions of the functions for the json and jsonb types. We refer to the PostgreSQL documentation for detailed explanations of these functions. We aimed at keeping as much as possible the same syntax for the non-temporal and the temporal versions of these functions as illustrated in this document.
As stated in the section called “Temporalizing Operations”, a common way to generalize the traditional operations to the temporal types is to apply the operation at each instant, which yields a temporal value as result. Consider for example the -> operator that extracts a JSON object field with the given key.
SELECT jsonb '{"unit": "km", "speed": 10}' -> text 'speed';
-- 10
Applying the same operator to a temporal JSON or JSONB value yields the following result
SELECT ttext '{[{"unit": "km", "speed": 10}@2001-01-01,
{"unit": "km", "speed": 20}@2001-01-02]}' -> text 'speed';
-- {["10"@2001-01-01, "20"@2001-01-02]}
SELECT tjsonb '{[{"unit": "km", "speed": 10}@2001-01-01,
{"unit": "km", "speed": 20}@2001-01-02]}' -> text 'speed';
-- {["10"@2001-01-01, "20"@2001-01-02]}
which is obtained by applying the PostgreSQL operator -> at every instant of the temporal JSON or JSONB value.
When manipulating JSON collections of varying structure, it may be the case that an item is defined in some of the documents but not all. PostgreSQL provides the lax mode for this purpose, where the argument null_value_treatment determines the behavior in the case a function returns a NULL value. The argument can take one of the following values: 'raise_exception', 'use_json_null', 'delete_key', 'return_target', where 'use_json_null' is the default value. In PostgreSQL the lax mode is supported only for update (not query) operations with the function jsonb_set_lax. In MobilityDB, we kept the same semantics for the corresponding function tjsonb_set_lax, but we enabled similar behavior for all temporal JSON operations that may return a null value, except that we replaced the value 'return_target' with 'return_null', since the former is not meaningful for temporal operations. For operators such as ->, the default value 'use_json_null' is used and cannot be changed, whereas for the corresponding functions tjson_object_field and tjsonb_object_field, the last argument specifies the behavior in the case the function returns NULL. We illustrate this behavior for function tjson_object_field below.
Extract a temporal JSON object field specified by a key
{ttext,tjsonb} -> text → {ttext,tjsonb}
tjsonb ->> text → ttext
tjson_object_field(ttext,text,null_handle text='use_json_null') → ttext
tjsonb_object_field(tjsonb,text,null_handle text='use_json_null') → tjsonb
tjsonb_object_field_text(tjsonb,text,null_handle text='use_json_null') → ttext
SELECT ttext '{[{"unit": "km", "speed": 10}@2001-01-01,
{"unit": "km", "speed": 20}@2001-01-02]}' -> text 'speed';
-- {["10"@2001-01-01, "20"@2001-01-02]}
SELECT tjsonb '[{"position":"Point(1 1)", "speed":10}@2001-01-01,
{"position":"Point(2 2)", "speed":20}@2001-01-03]' ->> text 'position';
-- ["Point(1 1)"@2001-01-01, "Point(2 2)"@2001-01-03]
SELECT ttext '{[{"unit": "km", "speed": 10}@2001-01-01,
{"unit": "km"}@2001-01-02, {"unit": "km", "speed": 10}@2001-01-03]}' -> text 'speed';
-- {[10@2001-01-01, null@2001-01-02, 10@2001-01-03]}
We show below the behavior of the function tjson_object_field for the possible values of the null_handle argument. All JSON functions that may return a NULL value behave similarly in MobilityDB.
SELECT tjson_object_field(ttext '{[{"unit": "km", "speed": 10}@2001-01-01,
{"unit": "km"}@2001-01-02, {"unit": "km", "speed": 10}@2001-01-03]}', 'speed',
'raise_exception');
-- ERROR: The lifted operation returned NULL
SELECT tjson_object_field(ttext '{[{"unit": "km", "speed": 10}@2001-01-01,
{"unit": "km"}@2001-01-02, {"unit": "km", "speed": 10}@2001-01-03]}', 'speed',
'use_json_null');
-- {[10@2001-01-01, null@2001-01-02, 10@2001-01-03]}
SELECT tjson_object_field(ttext '{[{"unit": "km", "speed": 10}@2001-01-01,
{"unit": "km"}@2001-01-02, {"unit": "km", "speed": 10}@2001-01-03]}', 'speed',
'delete_key');
-- {[10@2001-01-01, 10@2001-01-02), [10@2001-01-03]}
SELECT tjson_object_field(ttext '{[{"unit": "km", "speed": 10}@2001-01-01,
{"unit": "km"}@2001-01-02, {"unit": "km", "speed": 10}@2001-01-03]}', 'speed',
'return_null');
-- NULL
Extract a temporal JSON object field specified by a path
{ttext,tjsonb} #> text[] → {ttext,tjsonb}
tjson_extract_path(ttext,text[],null_handle text='use_json_null') → ttext
tjsonb_extract_path(tjsonb,text[],null_handle text='use_json_null') → tjsonb
tjsonb_extract_path_text(tjsonb,text[],null_handle text='use_json_null') → ttext
SELECT ttext '[{"speed": {"unit": "km", "value": 10}}@2001-01-01,
{"speed": {"unit": "km", "value": 20}}@2001-01-02]' #> ARRAY[text 'speed', 'value'];
-- {["10"@2001-01-01, "20"@2001-01-02]}
SELECT tjsonb '[{"position":"Point(1 1)", "PoIs":["Grand Place", "La Bourse"]}@2001-01-01,
{"position":"Point(2 2)", "PoIs":["Palais Royal", "Manneken Pis"]}@2001-01-03]' #>>
ARRAY[text 'PoIs', '1'];
-- ["La Bourse"@2001-01-01, "Manneken Pis"@2001-01-03]
Extract an element from a temporal JSON array
tjson_array_element(tjsonb,integer,null_handle text='use_json_null') → tjsonb
tjsonb_array_element(tjsonb,integer,null_handle text='use_json_null') → tjsonb
tjsonb_array_element_text(tjsonb,integer,null_handle text='use_json_null') → tjsonb
SELECT tjson_array_element(ttext '[["Grand Place", "La Bourse"]@2001-01-01, ["Palais Royal", "Manneken Pis"]@2001-01-02]', 0); -- ["Grand Place"@2001-01-01, "Palais Royal"@2001-01-02] SELECT tjsonb_array_element(tjsonb '[["Grand Place", "La Bourse"]@2001-01-01, ["Palais Royal", "Manneken Pis"]@2001-01-02]', 1); -- ["La Bourse"@2001-01-01, "Manneken Pis"@2001-01-02]
Extract a temporal alphanumeric value from a temporal JSONB value given by a key
tbool(tjsonb,text,null_handle text='raise_exception') → tbool
tint(tjsonb,text,null_handle text='raise_exception') → tint
tfloat(tjsonb,text,interp='linear',null_handle text='raise_exception') → tfloat
ttext(tjsonb,text, null_handle text='raise_exception') → ttext
Note that the value use_json_null cannot be used for the above functions and thus the default value 'raise_exception' is used.
SELECT tbool(tjsonb '[{"speed": 10, "lights": "on"}@2001-01-01,
{"speed": 20, "lights": "on"}@2001-01-02]', 'lights');
-- [true@2001-01-01, true@2001-01-02]
SELECT tint(tjsonb '[{"speed": 10, "units": "km/h"}@2001-01-01,
{"speed": 20, "units": "km/h"}@2001-01-02]', 'speed');
-- [10@2001-01-01, 20@2001-01-02]
SELECT tfloat(tjsonb '{[{"speed": 10, "units": "km/h"}@2001-01-01,
{"speed": 20, "units": "km/h"}@2001-01-02],[{"speed": 20}@2001-01-03,
{"speed": 20}@2001-01-04]}', 'speed', 'step');
-- Interp=Step;{[10@2001-01-01, 20@2001-01-02], [20@2001-01-03, 20@2001-01-04]}
SELECT ttext(tjsonb '[{"road": "Bvd Gén. Jacques", "category": "primary"}@2001-01-01,
{"road": "Bvd Gén. Jacques", "category": "primary"}@2001-01-02,
{"road": "Bvd de la Cambre", "category": "residential"}@2001-01-03]', 'category');
-- [primary@2001-01-01, residential@2001-01-03]
Temporal JSONB concatenation
tjsonb || {jsonb,tjsonb} → tjsonb
SELECT tjsonb '{[{"speed":10}@2001-01-01, {"speed":20}@2001-01-02]}' ||
'{"unit":"km"}'::jsonb;
-- {[{"unit": "km", "speed": 10}@2001-01-01, {"unit": "km", "speed": 20}@2001-01-02]}
SELECT tjsonb '{[{"speed":10}@2001-01-01, {"speed":20}@2001-01-03]}' ||
tjsonb '{[{"Position":"Point(1 1)"}@2001-01-02, {"Position":"Point(2 2)"}@2001-01-04]}';
/* {[{"speed": 10, "Position": "Point(1 1)"}@2001-01-02,
{"speed": 20, "Position": "Point(2 2)"}@2001-01-03]} */
Temporal JSONB deletion
tjsonb - {int,text,text[]} → tjsonb
tjsonb #- text[] → tjsonb
SELECT tjsonb '{[{"unit": "km", "speed": 10}@2001-01-01,
{"unit": "km", "speed": 20}@2001-01-02]}' - 'unit';
-- {[{"speed": 10}@2001-01-01, {"speed": 20}@2001-01-02]}
SELECT tjsonb '[["Grand Place", "La Bourse"]@2001-01-01,
["Palais Royal", "Manneken Pis"]@2001-01-02]' - 1;
-- [["Grand Place"]@2001-01-01, ["Palais Royal"]@2001-01-02]
SELECT tjsonb '[{"location":"Point(1 1)", "PoIs": ["Grand Place", "La Bourse"]@2001-01-01,
"location":"Point(2 2)", "PoIs": ["Palais Royal", "Manneken Pis"]@2001-01-02]' #-
ARRAY[text "\"PoIs\"", "1"];
-- ["La Bourse"@2001-01-01, "Manneken Pis"@2001-01-02]
As shown below, the result of a delete operation may be an empty record or an empty array. To remove these values, the function minusValue can used.
SELECT tjsonb '{[{"unit": "km", "speed": 10, "light": true}@2001-01-01,
{"unit": "km", "speed": 20}@2001-01-02]}' - ARRAY[text 'unit', 'speed'];
-- {[{"light": true}@2001-01-01, {}@2001-01-02]}
SELECT tjsonb '[["Grand Place", "La Bourse"]@2001-01-01,
["Palais Royal"]@2001-01-02]' - 0;
-- [["La Bourse"]@2001-01-01, []@2001-01-02]
SELECT minusValues(tjsonb '{[{"unit": "km", "speed": 10, "light": true}@2001-01-01,
{"unit": "km", "speed": 20}@2001-01-02]}' - ARRAY[text 'unit', 'speed'],
jsonbset '{"[]","{}"}');
-- {[{"light": true}@2001-01-01, {"light": true}@2001-01-02)}
SELECT minusValues(tjsonb '[["Grand Place", "La Bourse"]@2001-01-01,
["Palais Royal"]@2001-01-02]' - 0, jsonbset '{"[]","{}"}');
-- {[["La Bourse"]@2001-01-01, ["La Bourse"]@2001-01-02)}
Temporal JSONB exists
tjsonb ? jsonb → tbool
tjsonb ?| jsonb[] → tbool
tjsonb ?& jsonb[] → tbool
As in PostgreSQL, the operators ?| and ?& test, respectively, whether any or all of the strings in the text array exist as top-level keys or array elements.
SELECT tjsonb '"{\"geom\": \"Point(1 1)\"}"@2001-01-01' ? text 'geom';
-- t@2001-01-01
SELECT tjsonb '{{"geom": "Point(1 1)"}@2001-01-01, {"geom": "Point(2 2)"}@2001-01-02,
{"geom": "Point(1 1)"}@2001-01-03}' ?| ARRAY[text 'geom'];
-- {t@2001-01-01, t@2001-01-02, t@2001-01-03}
SELECT tjsonb '[{"speed": 10, "units": "km/h"}@2001-01-01,
{"speed": 20, "units": "km/h"}@2001-01-02]' ?& ARRAY['speed', 'units'];
-- {[t@2001-01-01, t@2001-01-02]}
Temporal JSONB contains/contained
{tjsonb,jsonb} @> {tjsonb,jsonb} → tbool
{tjsonb,jsonb} <@ {tjsonb,jsonb} → tbool
SELECT tjsonb '"{\"geom\": \"Point(1 1)\"}"@2001-01-01' @> jsonb '{"geom": "Point(1 1)"}';
-- t@2001-01-01
SELECT tjsonb '{{"geom": "Point(1 1)"}@2001-01-01, {"geom": "Point(2 2)"}@2001-01-02,
{"geom": "Point(1 1)"}@2001-01-03}' @> jsonb '{"geom": "Point(1 1)"}';
-- {t@2001-01-01, f@2001-01-02, t@2001-01-03}
SELECT jsonb '{"geom": "Point(1 1)"}' <@ tjsonb '{[{"geom": "Point(1 1)"}@2001-01-01,
{"geom": "Point(1 1)"}@2001-01-02, {"geom": "Point(1 1)"}@2001-01-03],
[{"geom": "Point(2 2)"}@2001-01-04, {"geom": "Point(2 2)"}@2001-01-05]}';
-- {[t@2001-01-01, t@2001-01-03], [f@2001-01-04, f@2001-01-05]}
Temporal JSONB set
tjsonb_set(tjsonb,path text[],jsonb,create boolean=true) → tjsonb
tjsonb_set_lax(tjsonb,path text[],jsonb,create boolean=true,handle_null text) → tjsonb
Return the tjsonb value with the item specified by path replaced by jsonb, or added if create is true and the item 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.
Function tjsonb_set_lax behaves identically to tjsonb_set if the given value is not NULL, Otherwise, it behaves according to the value of handle_null, which must be one of 'raise_exception', 'use_json_null', 'delete_key', or 'return_source'. As stated above, we kept PostgreSQL behavior for this function enabling the value 'return_source' whereas for all other query operations, this value has been replaced with 'return_null'.
SELECT tjsonb_set(tjsonb '[{"speed":10}@2001-01-01, {"speed":20}@2001-01-02]',
ARRAY['units'], '"km/h"'::jsonb);
-- [{"speed": 10, "units": "km/h"}@2001-01-01, {"speed": 20, "units": "km/h"}@2001-01-02]
SELECT tjsonb_set(tjsonb '[{"speed":10}@2001-01-01, {"speed":20}@2001-01-02]',
ARRAY['units'], '"km/h"'::jsonb, false);
-- [{"speed": 10}@2001-01-01, {"speed": 20}@2001-01-02]
SELECT tjsonb_set(tjsonb '[{"speed": 10, "units": "km/h"}@2001-01-01,
{"speed": 20, "units": "km/h"}@2001-01-02]', ARRAY['units'], '"mi/h"'::jsonb);
-- [{"speed": 10, "units": "mi/h"}@2001-01-01, {"speed": 20, "units": "mi/h"}@2001-01-02]
SELECT tjsonb_set_lax(tjsonb '[{"speed": 10, "units": "km/h"}@2001-01-01,
{"speed": 20}@2001-01-02]', ARRAY['units'], 'null'::jsonb, true, 'delete_key');
-- [{"speed": 10, "units": "km/h"}@2001-01-01, {"speed": 20}@2001-01-02]
Temporal JSONB insert
tjsonb_insert(tjsonb,path text[],jsonb,after boolean=false) → tjsonb
Return the tjsonb value with jsonb inserted. If the item specified by the path is an array element, the new value will be inserted before that item if after is false, or after it otherwise. If the item specified by the path is an object field, the 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.
SELECT tjsonb_insert(tjsonb '[{"speed":10}@2001-01-01, {"speed":20}@2001-01-02]',
ARRAY['units'], '"km/h"'::jsonb);
-- [{"speed": 10, "units": "km/h"}@2001-01-01, {"speed": 20, "units": "km/h"}@2001-01-02]
SELECT tjsonb_insert(tjsonb '[{"speed":10}@2001-01-01, {"speed":20}@2001-01-02]',
ARRAY['units'], '"km/h"'::jsonb, false);
-- [{"speed": 10}@2001-01-01, {"speed": 20}@2001-01-02]
SELECT tjsonb_insert(tjsonb '[{"speed": 10, "units": "km/h"}@2001-01-01,
{"speed": 20, "units": "km/h"}@2001-01-02]', ARRAY['units'], '"mi/h"'::jsonb);
-- [{"speed": 10, "units": "mi/h"}@2001-01-01, {"speed": 20, "units": "mi/h"}@2001-01-02]
Return a temporal JSON value without nulls
tjson_strip_nulls(ttext,strip_in_arrays bool=false) → ttext
tjsonb_strip_nulls(tjsonb,strip_in_arrays bool=false) → tjsonb
The last argument states whether null array elements are also stripped. Bare null values are never stripped.
SELECT tjson_strip_nulls(ttext '[{"speed": 10, "lights": null}@2001-01-01,
{"speed": 20, "PoIs": ["Grand Place", "La Bourse", null]}@2001-01-02]');
/* [{"speed": 10}@2001-01-01,
{"PoIs": ["Grand Place", "La Bourse", null], "speed": 20}@2001-01-02] */
SELECT tjsonb_strip_nulls(tjsonb '[{"speed": 10, "lights": null}@2001-01-01,
{"speed": 20, "PoIs": ["Grand Place", "La Bourse", null]}@2001-01-02]', true);
/* [{"speed": 10}@2001-01-01,
{"PoIs": ["Grand Place", "La Bourse"], "speed": 20}@2001-01-02] */
SELECT tjsonb_strip_nulls(tjsonb
'{{"road": "Bvd Gén. Jacques", "category": "primary"}@2001-01-01,
{"road": "Bvd de la Cambre", "category": "primary"}@2001-01-02,
{"road": "rue de l''Abbaye", "category": null}@2001-01-03}');
/* [{"road": "Bvd Gén. Jacques", "category": "primary"}@2001-01-01,
{"road": "Bvd de la Cambre", "category": "primary"}@2001-01-02,
{"road": "rue de l'Abbaye"}@2001-01-03] */
As shown below, the function does not strip bare null values. Function minusValue can be used for this purpose.
SELECT tjson_strip_nulls(ttext '[{"speed": 10}@2001-01-01, null@2001-01-02,
{"speed": 20, "PoIs": ["Grand Place", "La Bourse"]}@2001-01-03]');
/* [{"speed":10}@2001-01-01, null@2001-01-02,
{"speed":20,"PoIs":["Grand Place","La Bourse"]}@2001-01-03] */
SELECT minusValues(ttext '[{"speed": 10}@2001-01-01, null@2001-01-02,
{"speed": 20, "PoIs": ["Grand Place", "La Bourse"]}@2001-01-03]', 'null');
/* {[{"speed": 10}@2001-01-01, {"speed": 10}@2001-01-02),
[{"speed": 20, "PoIs": ["Grand Place", "La Bourse"]}@2001-01-03]}
Does the JSON path return any item for the specified temporal JSONB value?
tjsonb @? jsonpath → tbool
tjsonb_path_exists(tjsonb,vars jsonb='{}',silent boolean=false) → tbool
tjsonb_path_exists_tz(tjsonb,vars jsonb='{}',silent boolean=false) → tbool
The operator @? suppresses the following errors: missing object field or array element, unexpected JSON item type, datetime and numeric errors. The above functions can also suppress these types of errors by setting the last argument to true. This behavior might be helpful when searching JSON document collections of varying structure.
SELECT tjsonb '[{"speed":10}@2001-01-01, {"speed": 20, "units": "km/h"}@2001-01-02]' @?
'$.units ? (@ == "km/h")';
-- [f@2001-01-01, t@2001-01-02]
SELECT tjsonb_path_exists(tjsonb
'{{"road": "Bvd Gén. Jacques", "category": "primary"}@2001-01-01,
{"road": "Bvd Gén. Jacques", "category": "primary"}@2001-01-02,
{"road": "Bvd de la Cambre", "category": "residential"}@2001-01-03}',
'$.category ? (@ == "residential")');
-- {f@2001-01-01, f@2001-01-02, t@2001-01-03}
-- TODO, it works without ".datetime()"
SELECT tjsonb_path_exists_tz(tjsonb
'[{"speed":10, "cameraId": "25", "lastInspection": "2000-08-01 12:00:00"}@2001-01-01,
{"speed":20, "cameraId": "35", "lastInspection": "2000-03-01 12:00:00"}@2001-01-02]',
'$.lastInspection ? (@.datetime() > "2000-06-01".datetime() )');
-- [t@2001-01-01, f@2001-01-02]
Return the result of a JSON path predicate check for a temporal JSONB value
tjsonb @@ jsonpath → tbool
tjsonb_path_match(tjsonb,vars jsonb='{}',silent boolean=false) → tbool
tjsonb_path_match_tz(tjsonb,vars jsonb='{}',silent boolean=false) → tbool
The operator @@ suppresses the following errors: missing object field or array element, unexpected JSON item type, datetime and numeric errors. The above functions can also suppress these types of errors by setting the last argument to true. This behavior might be helpful when searching JSON document collections of varying structure.
SELECT tjsonb '[{"speed":10}@2001-01-01, {"speed": 20, "units": "km/h"}@2001-01-02]' @@
'$.units == "km/h"';
-- [f@2001-01-01, t@2001-01-02]
SELECT tjsonb_path_match(tjsonb
'{{"road": "Bvd Gén. Jacques", "category": "primary"}@2001-01-01,
{"road": "Bvd Gén. Jacques", "category": "primary"}@2001-01-02,
{"road": "Bvd de la Cambre", "category": "residential"}@2001-01-03}',
'$.category == "residential"');
-- {f@2001-01-01, f@2001-01-02, t@2001-01-03}
-- TODO, it works without ".datetime()"
SELECT tjsonb_path_match_tz(tjsonb
'[{"speed":10, "cameraId": "25", "lastInspection": "2000-08-01 12:00:00"}@2001-01-01,
{"speed":20, "cameraId": "35", "lastInspection": "2000-03-01 12:00:00"}@2001-01-02]',
'$.lastInspection.datetime() > "2000-06-01".datetime()');
-- [t@2001-01-01, f@2001-01-02]
Return all items returned by a JSON path from a temporal JSONB value, as a JSON array
tjsonb_path_query_array(tjsonb,vars jsonb='{}',silent boolean=false) → tjsonb
tjsonb_path_query_array_tz(tjsonb,vars jsonb='{}',silent boolean=false) → tjsonb
The above function can suppress the following errors by setting the last argument to true: missing object field or array element, unexpected JSON item type, datetime and numeric errors. This behavior might be helpful when searching JSON document collections of varying structure.
SELECT tjsonb_path_query_array(tjsonb
'[{"speed":10}@2001-01-01, {"speed": 20, "units": "km/h"}@2001-01-02]',
'$ ? (@.speed >= $min && @.speed <= $max)', '{"min":10, "max":30}');
-- [[{"speed": 10}]@2001-01-01, [{"speed": 20, "units": "km/h"}]@2001-01-02]
-- TODO, it works without "_tz"
SELECT tjsonb_path_query_array_tz(tjsonb
'[{"cameraId":25, "inspections":["2000-01-03", "2000-01-06", "2000-01-09"]}@2001-01-01,
{"cameraId":35, "inspections":["2000-01-04", "2000-01-07", "2000-01-10"]}@2001-01-02]',
'$.inspections ? (@.timestamp_tz() >= $min.timestamp_tz() &&
@.timestamp_tz() <= $max.timestamp_tz())', '{"min":"2000-01-05", "max":"2000-01-09"}');
-- [["2000-01-06", "2000-01-09"]@2001-01-01, ["2000-01-07"]@2001-01-02]
Return the fist item returned by a JSON path from a temporal JSONB value
tjsonb_path_query_first(tjsonb,vars jsonb='{}',silent boolean=false) → tjsonb
tjsonb_path_query_first_tz(tjsonb,vars jsonb='{}',silent boolean=false) → tjsonb
The above functions can suppress the following errors by setting the last argument to true: missing object field or array element, unexpected JSON item type, datetime and numeric errors. This behavior might be helpful when searching JSON document collections of varying structure.
SELECT tjsonb_path_query_first(tjsonb
'[{"speed":10}@2001-01-01, {"speed": 20, "units": "km/h"}@2001-01-02]',
'$.speed ? (@ >= $min && @ <= $max'), '{"min":10, "max":30}');
-- [[{"speed": 10}]@2001-01-01, [{"speed": 20, "units": "km/h"}]@2001-01-02]
-- TODO, it works without "_tz"
SELECT tjsonb_path_query_array_tz(tjsonb
'[{"cameraId":25, "inspections":["2000-01-03", "2000-01-06", "2000-01-09"]}@2001-01-01,
{"cameraId":35, "inspections":["2000-01-04", "2000-01-07", "2000-01-10"]}@2001-01-02]',
'$.inspections ? (@.datetime() >= $min.datetime() && @.datetime() <= $max.datetime())',
'{"min":"2000-01-05", "max":"2000-01-09"}');
-- [["2000-01-06", "2000-01-09"]@2001-01-01, ["2000-01-07"]@2001-01-02]