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 jsonbsetSetLax, 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 jsonbsetObjectField, the last argument specifies the behavior in the case the function returns NULL. We illustrate this behavior for function jsonbsetObjectField below.
Extract a JSON object field specified by a key
{textset,jsonbset} -> text → {textset,jsonbset}
jsonbset ->> text → textset
jsonbsetObjectField(jsonbset,text,null_handle text='use_json_null') → jsonbset
jsonbsetObjectFieldText(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 jsonbsetObjectField for the possible values of the null_handle argument. All JSON functions that may return a NULL value behave similarly in MobilityDB.
SELECT jsonbsetObjectField(textset '{[{"unit": "km", "speed": 10},
{"unit": "km"}, {"unit": "km", "speed": 10}]}', 'speed',
'raise_exception');
-- ERROR: The lifted operation returned NULL
SELECT jsonbsetObjectField(textset '{[{"unit": "km", "speed": 10},
{"unit": "km"}, {"unit": "km", "speed": 10}]}', 'speed',
'use_json_null');
-- {[10, null, 10]}
SELECT jsonbsetObjectField(textset '{[{"unit": "km", "speed": 10},
{"unit": "km"}, {"unit": "km", "speed": 10}]}', 'speed',
'delete_key');
-- {[10, 10), [10]}
SELECT jsonbsetObjectField(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}
jsonbsetExtractPath(jsonbset,text[],null_handle text='use_json_null') → jsonbset
jsonbsetExtractPathText(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
jsonbsetArrayElement(jsonbset,integer,null_handle text='use_json_null') → jsonbset
jsonbsetArrayElementText(jsonbset,integer,null_handle text='use_json_null') → jsonbset
SELECT jsonbsetArrayElement(textset '[["Grand Place", "La Bourse"], ["Palais Royal", "Manneken Pis"]]', 0); -- ["Grand Place", "Palais Royal"] SELECT jsonbsetArrayElement(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"])}
JSONB set exists
jsonbset ? text → boolean[]
jsonbset ?| text[] → boolean[]
jsonbset ?& text[] → boolean[]
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.
The result is a Boolean for every value of the set, in the order of the values of the set.
SELECT jsonbset '{"{\"speed\": 10}", "{\"speed\": 20, \"units\": \"km/h\"}"}';
-- {"{\"speed\": 20, \"units\": \"km/h\"}", "{\"speed\": 10}"}
SELECT jsonbset '{"{\"speed\": 10}", "{\"speed\": 20, \"units\": \"km/h\"}"}' ? text 'units';
-- {t,f}
SELECT jsonbset '{"{\"speed\": 10}", "{\"speed\": 20, \"units\": \"km/h\"}"}'
?| ARRAY[text 'units', text 'lights'];
-- {t,f}
SELECT jsonbset '{"{\"speed\": 10}", "{\"speed\": 20, \"units\": \"km/h\"}"}'
?& ARRAY[text 'speed', text 'units'];
-- {t,f}
Temporal JSONB set
jsonbsetSet(jsonbset,path text[],jsonb,create boolean=true) → jsonbset
jsonbsetSetLax(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 jsonbsetSetLax behaves identically to jsonbsetSet 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 jsonbsetSet(jsonbset '[{"speed":10}, {"speed":20}]',
ARRAY['units'], '"km/h"'::jsonb);
-- [{"speed": 10, "units": "km/h"}, {"speed": 20, "units": "km/h"}]
SELECT jsonbsetSet(jsonbset '[{"speed":10}, {"speed":20}]',
ARRAY['units'], '"km/h"'::jsonb, false);
-- [{"speed": 10}, {"speed": 20}]
SELECT jsonbsetSet(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 jsonbsetSetLax(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
jsonbsetInsert(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 jsonbsetInsert(jsonbset '[{"speed":10}, {"speed":20}]',
ARRAY['units'], '"km/h"'::jsonb);
-- [{"speed": 10, "units": "km/h"}, {"speed": 20, "units": "km/h"}]
SELECT jsonbsetInsert(jsonbset '[{"speed":10}, {"speed":20}]',
ARRAY['units'], '"km/h"'::jsonb, false);
-- [{"speed": 10}, {"speed": 20}]
SELECT jsonbsetInsert(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
jsonbsetStripNulls(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 jsonbsetStripNulls(textset '[{"speed": 10, "lights": null},
{"speed": 20, "PoIs": ["Grand Place", "La Bourse", null]}]');
/* [{"speed": 10},
{"PoIs": ["Grand Place", "La Bourse", null], "speed": 20}] */
SELECT jsonbsetStripNulls(jsonbset '[{"speed": 10, "lights": null},
{"speed": 20, "PoIs": ["Grand Place", "La Bourse", null]}]', true);
/* [{"speed": 10},
{"PoIs": ["Grand Place", "La Bourse"], "speed": 20}] */
SELECT jsonbsetStripNulls(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 jsonbsetStripNulls(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 → boolean[]
jsonbsetPathExists(jsonbset,vars jsonb='{}',silent boolean=false) → boolean[]
jsonbsetPathExistsTz(jsonbset,vars jsonb='{}',silent boolean=false) → boolean[]
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.
The result is a Boolean for every value of the set, in the order of the values of the set.
SELECT jsonbset '{"{\"speed\": 10}", "{\"speed\": 20, \"units\": \"km/h\"}"}';
-- {"{\"speed\": 20, \"units\": \"km/h\"}", "{\"speed\": 10}"}
SELECT jsonbset '{"{\"speed\": 10}", "{\"speed\": 20, \"units\": \"km/h\"}"}' @? '$.units';
-- {t,f}
SELECT jsonbsetPathExists(jsonbset '{"{\"speed\": 10}", "{\"speed\": 20, \"units\": \"km/h\"}"}',
'$.speed ? (@ > $min)', '{"min": 15}');
-- {t,f}
Return the result of a JSON path predicate check for a JSONB set
jsonbset @@ jsonpath → boolean[]
jsonbsetPathMatch(jsonbset,vars jsonb='{}',silent boolean=false) → boolean[]
jsonbsetPathMatchTz(jsonbset,vars jsonb='{}',silent boolean=false) → boolean[]
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.
The result is a Boolean for every value of the set, in the order of the values of the set.
SELECT jsonbset '{"{\"speed\": 10}", "{\"speed\": 20, \"units\": \"km/h\"}"}';
-- {"{\"speed\": 20, \"units\": \"km/h\"}", "{\"speed\": 10}"}
SELECT jsonbset '{"{\"speed\": 10}", "{\"speed\": 20, \"units\": \"km/h\"}"}' @@ '$.speed > 15';
-- {t,f}
SELECT jsonbsetPathMatch(jsonbset '{"{\"speed\": 10}", "{\"speed\": 20, \"units\": \"km/h\"}"}',
'$.speed > $min', '{"min": 15}');
-- {t,f}
Return all items returned by a JSON path from a JSONB set, as a JSON array
jsonbsetPathQueryArray(jsonbset,vars jsonb='{}',silent boolean=false) → jsonbset
jsonbsetPathQueryArrayTz(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 jsonbsetPathQueryArray(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 jsonbsetPathQueryArrayTz(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
jsonbsetPathQueryFirst(jsonbset,vars jsonb='{}',silent boolean=false) → jsonbset
jsonbsetPathQueryFirstTz(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 jsonbsetPathQueryFirst(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 jsonbsetPathQueryArrayTz(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"]]