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 tjsonbSetLax, 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 tjsonObjectField and tjsonbObjectField, the last argument specifies the behavior in the case the function returns NULL. We illustrate this behavior for function tjsonObjectField below.
Extract a temporal JSON object field specified by a key
{ttext,tjsonb} -> text → {ttext,tjsonb}
tjsonb ->> text → ttext
tjsonObjectField(ttext,text,null_handle text='use_json_null') → ttext
tjsonbObjectField(tjsonb,text,null_handle text='use_json_null') → tjsonb
tjsonbObjectFieldText(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 tjsonObjectField for the possible values of the null_handle argument. All JSON functions that may return a NULL value behave similarly in MobilityDB.
SELECT tjsonObjectField(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 tjsonObjectField(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 tjsonObjectField(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 tjsonObjectField(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}
tjsonExtractPath(ttext,text[],null_handle text='use_json_null') → ttext
tjsonbExtractPath(tjsonb,text[],null_handle text='use_json_null') → tjsonb
tjsonbExtractPathText(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
tjsonArrayElement(tjsonb,integer,null_handle text='use_json_null') → tjsonb
tjsonbArrayElement(tjsonb,integer,null_handle text='use_json_null') → tjsonb
tjsonbArrayElementText(tjsonb,integer,null_handle text='use_json_null') → tjsonb
SELECT tjsonArrayElement(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 tjsonbArrayElement(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
tjsonbSet(tjsonb,path text[],jsonb,create boolean=true) → tjsonb
tjsonbSetLax(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 tjsonbSetLax behaves identically to tjsonbSet 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 tjsonbSet(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 tjsonbSet(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 tjsonbSet(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 tjsonbSetLax(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
tjsonbInsert(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 tjsonbInsert(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 tjsonbInsert(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 tjsonbInsert(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
tjsonStripNulls(ttext,strip_in_arrays bool=false) → ttext
tjsonbStripNulls(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 tjsonStripNulls(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 tjsonbStripNulls(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 tjsonbStripNulls(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 tjsonStripNulls(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
tjsonbPathExists(tjsonb,vars jsonb='{}',silent boolean=false) → tbool
tjsonbPathExistsTz(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 tjsonbPathExists(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 tjsonbPathExistsTz(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
tjsonbPathMatch(tjsonb,vars jsonb='{}',silent boolean=false) → tbool
tjsonbPathMatchTz(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 tjsonbPathMatch(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 tjsonbPathMatchTz(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
tjsonbPathQueryArray(tjsonb,vars jsonb='{}',silent boolean=false) → tjsonb
tjsonbPathQueryArrayTz(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 tjsonbPathQueryArray(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 tjsonbPathQueryArrayTz(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
tjsonbPathQueryFirst(tjsonb,vars jsonb='{}',silent boolean=false) → tjsonb
tjsonbPathQueryFirstTz(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 tjsonbPathQueryFirst(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 tjsonbPathQueryArrayTz(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]