Table of Contents
PostgreSQL provides two types for storing JSON data: json and jsonb. The json type stores an exact copy of the input text, which must be reparsed each time it is processed. On the other hand, the jsonb type stores the JSON data in a decomposed binary format that makes it slightly slower to input due to added conversion overhead, but significantly faster to process, since no reparsing is needed. The jsonb type also supports indexing.
In MobilityDB, the textset and the ttext types are used for representing, respectively, set of JSON values and temporal JSON values. Furthermore, the jsonb type serves as base type for defining the jsonbset and the tjsonb types. Most functions and operators described in the previous chapters for set and temporal types are also applicable for the corresponding JSON types. In addition, there are specific functions defined for these types, which are derived from the corresponding functions of the json and the jsonb types.
In this chapter, we describe the MobilityDB JSON types and its associated operations. We refer to the PostgreSQL documentation for a detailed explanation of the json and jsonb types and their functionality. We aimed at enabling the same syntax of the original PostgreSQL functions for the corresponding MobilityDB types, as illustrated in this document.
Consider for example the -> operator, which extracts a JSON object field with a given key.
SELECT jsonb '{"unit": "km", "speed": 10}' -> text 'speed';
-- 10
Applying the same operator to a JSONB set and to a temporal JSONB value yields the following results
SELECT jsonbset '{"{\"unit\": \"km\", \"speed\": 10}",
"{\"unit\": \"km\", \"speed\": 20}"}' -> text 'speed';
-- {"10", "20"}
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 are obtained by applying the PostgreSQL operator -> at every element of the JSONB set and to every instant of the temporal 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 jsonbset_set_lax, but we enabled similar behavior for all 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 set operations. For operators such as ->, the default value 'use_json_null' is used and cannot be changed, whereas for the corresponding functions jsonbset_object_field, the last argument specifies the behavior in the case the function returns NULL. We illustrate this behavior for function jsonbset_object_field below.
Extract a JSON object field specified by a key
{textset,jsonbset} -> text → {textset,jsonbset}
jsonbset ->> text → textset
jsonbset_object_field(jsonbset,text,null_handle text='use_json_null') → jsonbset
jsonbset_object_field_text(jsonbset,text,null_handle text='use_json_null') → textset
SELECT textset '{[{"unit": "km", "speed": 10},
{"unit": "km", "speed": 20}]}' -> text 'speed';
-- {["10", "20"]}
SELECT jsonbset '[{"position":"Point(1 1)", "speed":10},
{"position":"Point(2 2)", "speed":20}]' ->> text 'position';
-- ["Point(1 1)", "Point(2 2)"]
SELECT textset '{[{"unit": "km", "speed": 10},
{"unit": "km"}, {"unit": "km", "speed": 10}]}' -> text 'speed';
-- {[10, null, 10]}
We show below the behavior of the function jsonbset_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 jsonbset_object_field(textset '{[{"unit": "km", "speed": 10},
{"unit": "km"}, {"unit": "km", "speed": 10}]}', 'speed',
'raise_exception');
-- ERROR: The lifted operation returned NULL
SELECT jsonbset_object_field(textset '{[{"unit": "km", "speed": 10},
{"unit": "km"}, {"unit": "km", "speed": 10}]}', 'speed',
'use_json_null');
-- {[10, null, 10]}
SELECT jsonbset_object_field(textset '{[{"unit": "km", "speed": 10},
{"unit": "km"}, {"unit": "km", "speed": 10}]}', 'speed',
'delete_key');
-- {[10, 10), [10]}
SELECT jsonbset_object_field(textset '{[{"unit": "km", "speed": 10},
{"unit": "km"}, {"unit": "km", "speed": 10}]}', 'speed',
'return_null');
-- NULL
Extract a JSON object field specified by a path
{textset,jsonbset} #> text[] → {textset,jsonbset}
jsonbset_extract_path(jsonbset,text[],null_handle text='use_json_null') → jsonbset
jsonbset_extract_path_text(jsonbset,text[],null_handle text='use_json_null') → textset
SELECT textset '[{"speed": {"unit": "km", "value": 10}},
{"speed": {"unit": "km", "value": 20}}]' #> ARRAY[text 'speed', 'value'];
-- {["10", "20"]}
SELECT jsonbset '[{"position":"Point(1 1)", "PoIs":["Grand Place", "La Bourse"]},
{"position":"Point(2 2)", "PoIs":["Palais Royal", "Manneken Pis"]}]' #>>
ARRAY[text 'PoIs', '1'];
-- ["La Bourse", "Manneken Pis"]
Extract an element from a JSON array
jsonbset_array_element(jsonbset,integer,null_handle text='use_json_null') → jsonbset
jsonbset_array_element_text(jsonbset,integer,null_handle text='use_json_null') → jsonbset
SELECT jsonbset_array_element(textset '[["Grand Place", "La Bourse"], ["Palais Royal", "Manneken Pis"]]', 0); -- ["Grand Place", "Palais Royal"] SELECT jsonbset_array_element(jsonbset '[["Grand Place", "La Bourse"], ["Palais Royal", "Manneken Pis"]]', 1); -- ["La Bourse", "Manneken Pis"]
Extract a alphanumeric value from a JSONB set given by a key
intset(jsonbset,text,null_handle text='raise_exception') → tint
floatset(jsonbset,text,null_handle text='raise_exception') → tfloat
textset(jsonbset,text,null_handle text='raise_exception') → textset
Note that the value use_json_null cannot be used for the above functions and thus the default value 'raise_exception' is used.
SELECT intset(jsonbset '{"{\"speed\": 10, \"units\": \"km/h\"}",
"{\"speed\": 20, \"units\": \"km/h\"}"}', 'speed');
-- {10, 20}
SELECT floatset(jsonbset '{"{\"speed\": 10, \"units\": \"km/h\"}",
"{\"speed\": 20, \"units\": \"km/h\"}", "{\"speed\": 25}"}', 'speed');
-- {10, 20, 25}
SELECT textset(jsonbset '{"{\"road\": \"Bvd Gén. Jacques\", \"category\": \"primary\"}",
"{\"road\": \"Bvd de la Cambre\", \"category\": \"residential\"}"}', 'category');
-- {primary, residential}
Temporal JSONB concatenation
jsonbset || {jsonb,jsonbset} → jsonbset
SELECT jsonbset '{[{"speed":10}, {"speed":20}]}' ||
'{"unit":"km"}'::jsonb;
-- {[{"unit": "km", "speed": 10}, {"unit": "km", "speed": 20}]}
SELECT jsonbset '{[{"speed":10}, {"speed":20}]}' ||
jsonbset '{[{"Position":"Point(1 1)"}, {"Position":"Point(2 2)"}]}';
/* {[{"speed": 10, "Position": "Point(1 1)"},
{"speed": 20, "Position": "Point(2 2)"}]} */
Temporal JSONB deletion
jsonbset - {int,text,text[]} → jsonbset
jsonbset #- text[] → jsonbset
SELECT jsonbset '{[{"unit": "km", "speed": 10},
{"unit": "km", "speed": 20}]}' - 'unit';
-- {[{"speed": 10}, {"speed": 20}]}
SELECT jsonbset '[["Grand Place", "La Bourse"],
["Palais Royal", "Manneken Pis"]]' - 1;
-- [["Grand Place"], ["Palais Royal"]]
SELECT jsonbset '[{"location":"Point(1 1)", "PoIs": ["Grand Place", "La Bourse"],
"location":"Point(2 2)", "PoIs": ["Palais Royal", "Manneken Pis"]]' #-
ARRAY[text "\"PoIs\"", "1"];
-- ["La Bourse", "Manneken Pis"]
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 jsonbset '{[{"unit": "km", "speed": 10, "light": true},
{"unit": "km", "speed": 20}]}' - ARRAY[text 'unit', 'speed'];
-- {[{"light": true}, {}]}
SELECT jsonbset '[["Grand Place", "La Bourse"],
["Palais Royal"]]' - 0;
-- [["La Bourse"], []]
SELECT minusValues(jsonbset '{[{"unit": "km", "speed": 10, "light": true},
{"unit": "km", "speed": 20}]}' - ARRAY[text 'unit', 'speed'],
jsonbset '{"[]","{}"}');
-- {[{"light": true}, {"light": true})}
SELECT minusValues(jsonbset '[["Grand Place", "La Bourse"],
["Palais Royal"]]' - 0, jsonbset '{"[]","{}"}');
-- {[["La Bourse"], ["La Bourse"])}
Temporal JSONB exists
jsonbset ? jsonb → tbool
jsonbset ?| jsonb[] → tbool
jsonbset ?& 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 jsonbset '"{\"geom\": \"Point(1 1)\"}"' ? text 'geom';
-- t
SELECT jsonbset '{{"geom": "Point(1 1)"}, {"geom": "Point(2 2)"},
{"geom": "Point(1 1)"}}' ?| ARRAY[text 'geom'];
-- {t, t, t}
SELECT jsonbset '[{"speed": 10, "units": "km/h"},
{"speed": 20, "units": "km/h"}]' ?& ARRAY['speed', 'units'];
-- {[t, t]}
Temporal JSONB contains/contained
{jsonbset,jsonb} @> {jsonbset,jsonb} → tbool
{jsonbset,jsonb} <@ {jsonbset,jsonb} → tbool
SELECT jsonbset '"{\"geom\": \"Point(1 1)\"}"' @> jsonb '{"geom": "Point(1 1)"}';
-- t
SELECT jsonbset '{{"geom": "Point(1 1)"}, {"geom": "Point(2 2)"},
{"geom": "Point(1 1)"}}' @> jsonb '{"geom": "Point(1 1)"}';
-- {t, f, t}
SELECT jsonb '{"geom": "Point(1 1)"}' <@ jsonbset '{[{"geom": "Point(1 1)"},
{"geom": "Point(1 1)"}, {"geom": "Point(1 1)"}],
[{"geom": "Point(2 2)"}, {"geom": "Point(2 2)"}]}';
-- {[t, t], [f, f]}
Temporal JSONB set
jsonbset_set(jsonbset,path text[],jsonb,create boolean=true) → jsonbset
jsonbset_set_lax(jsonbset,path text[],jsonb,create boolean=true,handle_null text) → jsonbset
Return the jsonbset 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 jsonbset_set_lax behaves identically to jsonbset_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 jsonbset_set(jsonbset '[{"speed":10}, {"speed":20}]',
ARRAY['units'], '"km/h"'::jsonb);
-- [{"speed": 10, "units": "km/h"}, {"speed": 20, "units": "km/h"}]
SELECT jsonbset_set(jsonbset '[{"speed":10}, {"speed":20}]',
ARRAY['units'], '"km/h"'::jsonb, false);
-- [{"speed": 10}, {"speed": 20}]
SELECT jsonbset_set(jsonbset '[{"speed": 10, "units": "km/h"},
{"speed": 20, "units": "km/h"}]', ARRAY['units'], '"mi/h"'::jsonb);
-- [{"speed": 10, "units": "mi/h"}, {"speed": 20, "units": "mi/h"}]
SELECT jsonbset_set_lax(jsonbset '[{"speed": 10, "units": "km/h"},
{"speed": 20}]', ARRAY['units'], 'null'::jsonb, true, 'delete_key');
-- [{"speed": 10, "units": "km/h"}, {"speed": 20}]
Temporal JSONB insert
jsonbset_insert(jsonbset,path text[],jsonb,after boolean=false) → jsonbset
Return the jsonbset 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 jsonbset_insert(jsonbset '[{"speed":10}, {"speed":20}]',
ARRAY['units'], '"km/h"'::jsonb);
-- [{"speed": 10, "units": "km/h"}, {"speed": 20, "units": "km/h"}]
SELECT jsonbset_insert(jsonbset '[{"speed":10}, {"speed":20}]',
ARRAY['units'], '"km/h"'::jsonb, false);
-- [{"speed": 10}, {"speed": 20}]
SELECT jsonbset_insert(jsonbset '[{"speed": 10, "units": "km/h"},
{"speed": 20, "units": "km/h"}]', ARRAY['units'], '"mi/h"'::jsonb);
-- [{"speed": 10, "units": "mi/h"}, {"speed": 20, "units": "mi/h"}]
Return a JSONB set without nulls
jsonbset_strip_nulls(jsonbset,strip_in_arrays bool=false) → jsonbset
The last argument states whether null array elements are also stripped. Bare null values are never stripped.
SELECT jsonbset_strip_nulls(textset '[{"speed": 10, "lights": null},
{"speed": 20, "PoIs": ["Grand Place", "La Bourse", null]}]');
/* [{"speed": 10},
{"PoIs": ["Grand Place", "La Bourse", null], "speed": 20}] */
SELECT jsonbset_strip_nulls(jsonbset '[{"speed": 10, "lights": null},
{"speed": 20, "PoIs": ["Grand Place", "La Bourse", null]}]', true);
/* [{"speed": 10},
{"PoIs": ["Grand Place", "La Bourse"], "speed": 20}] */
SELECT jsonbset_strip_nulls(jsonbset
'{{"road": "Bvd Gén. Jacques", "category": "primary"},
{"road": "Bvd de la Cambre", "category": "primary"},
{"road": "rue de l''Abbaye", "category": null}}');
/* [{"road": "Bvd Gén. Jacques", "category": "primary"},
{"road": "Bvd de la Cambre", "category": "primary"},
{"road": "rue de l'Abbaye"}] */
As shown below, the function does not strip bare null values. Function minusValue can be used for this purpose.
SELECT jsonbset_strip_nulls(textset '[{"speed": 10}, null,
{"speed": 20, "PoIs": ["Grand Place", "La Bourse"]}]');
/* [{"speed":10}, null,
{"speed":20,"PoIs":["Grand Place","La Bourse"]}] */
SELECT minusValues(textset '[{"speed": 10}, null,
{"speed": 20, "PoIs": ["Grand Place", "La Bourse"]}]', 'null');
/* {[{"speed": 10}, {"speed": 10}),
[{"speed": 20, "PoIs": ["Grand Place", "La Bourse"]}]}
Does the JSON path return any item for the specified JSONB set?
jsonbset @? jsonpath → tbool
jsonbset_path_exists(jsonbset,vars jsonb='{}',silent boolean=false) → tbool
jsonbset_path_exists_tz(jsonbset,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 jsonbset '[{"speed":10}, {"speed": 20, "units": "km/h"}]' @?
'$.units ? (@ == "km/h")';
-- [f, t]
SELECT jsonbset_path_exists(jsonbset
'{{"road": "Bvd Gén. Jacques", "category": "primary"},
{"road": "Bvd Gén. Jacques", "category": "primary"},
{"road": "Bvd de la Cambre", "category": "residential"}}',
'$.category ? (@ == "residential")');
-- {f, f, t}
-- TODO, it works without ".datetime()"
SELECT jsonbset_path_exists_tz(jsonbset
'[{"speed":10, "cameraId": "25", "lastInspection": "2000-08-01 12:00:00"},
{"speed":20, "cameraId": "35", "lastInspection": "2000-03-01 12:00:00"}]',
'$.lastInspection ? (@.datetime() > "2000-06-01".datetime() )');
-- [t, f]
Return the result of a JSON path predicate check for a JSONB set
jsonbset @@ jsonpath → tbool
jsonbset_path_match(jsonbset,vars jsonb='{}',silent boolean=false) → tbool
jsonbset_path_match_tz(jsonbset,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 jsonbset '[{"speed":10}, {"speed": 20, "units": "km/h"}]' @@
'$.units == "km/h"';
-- [f, t]
SELECT jsonbset_path_match(jsonbset
'{{"road": "Bvd Gén. Jacques", "category": "primary"},
{"road": "Bvd Gén. Jacques", "category": "primary"},
{"road": "Bvd de la Cambre", "category": "residential"}}',
'$.category == "residential"');
-- {f, f, t}
-- TODO, it works without ".datetime()"
SELECT jsonbset_path_match_tz(jsonbset
'[{"speed":10, "cameraId": "25", "lastInspection": "2000-08-01 12:00:00"},
{"speed":20, "cameraId": "35", "lastInspection": "2000-03-01 12:00:00"}]',
'$.lastInspection.datetime() > "2000-06-01".datetime()');
-- [t, f]
Return all items returned by a JSON path from a JSONB set, as a JSON array
jsonbset_path_query_array(jsonbset,vars jsonb='{}',silent boolean=false) → jsonbset
jsonbset_path_query_array_tz(jsonbset,vars jsonb='{}',silent boolean=false) → jsonbset
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 jsonbset_path_query_array(jsonbset
'[{"speed":10}, {"speed": 20, "units": "km/h"}]',
'$ ? (@.speed >= $min && @.speed <= $max)', '{"min":10, "max":30}');
-- [[{"speed": 10}], [{"speed": 20, "units": "km/h"}]]
-- TODO, it works without "_tz"
SELECT jsonbset_path_query_array_tz(jsonbset
'[{"cameraId":25, "inspections":["2000-01-03", "2000-01-06", "2000-01-09"]},
{"cameraId":35, "inspections":["2000-01-04", "2000-01-07", "2000-01-10"]}]',
'$.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"], ["2000-01-07"]]
Return the fist item returned by a JSON path from a JSONB set
jsonbset_path_query_first(jsonbset,vars jsonb='{}',silent boolean=false) → jsonbset
jsonbset_path_query_first_tz(jsonbset,vars jsonb='{}',silent boolean=false) → jsonbset
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 jsonbset_path_query_first(jsonbset
'[{"speed":10}, {"speed": 20, "units": "km/h"}]',
'$.speed ? (@ >= $min && @ <= $max'), '{"min":10, "max":30}');
-- [[{"speed": 10}], [{"speed": 20, "units": "km/h"}]]
-- TODO, it works without "_tz"
SELECT jsonbset_path_query_array_tz(jsonbset
'[{"cameraId":25, "inspections":["2000-01-03", "2000-01-06", "2000-01-09"]},
{"cameraId":35, "inspections":["2000-01-04", "2000-01-07", "2000-01-10"]}]',
'$.inspections ? (@.datetime() >= $min.datetime() && @.datetime() <= $max.datetime())',
'{"min":"2000-01-05", "max":"2000-01-09"}');
-- [["2000-01-06", "2000-01-09"], ["2000-01-07"]]