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 set(ARRAY[jsonb '{"unit": "km", "speed": 10}',
'{"unit": "km", "speed": 20}']) -> text 'speed';
-- {10, 20}
SELECT set(ARRAY[jsonb '{"position":"Point(1 1)", "speed":10}',
'{"position":"Point(2 2)", "speed":20}']) ->> text 'position';
-- {"Point(1 1)", "Point(2 2)"}
SELECT set(ARRAY[jsonb '{"unit": "km", "speed": 10}', '{"unit": "km"}',
'{"unit": "km", "speed": 10}']) -> text 'speed';
-- {"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(set(ARRAY[jsonb '{"unit": "km", "speed": 10}',
'{"unit": "km"}', '{"unit": "km", "speed": 10}']), 'speed', 'raise_exception');
-- ERROR: The lifted operation returned NULL
SELECT jsonbsetObjectField(set(ARRAY[jsonb '{"unit": "km", "speed": 10}',
'{"unit": "km"}', '{"unit": "km", "speed": 10}']), 'speed', 'use_json_null');
-- {"null", 10}
SELECT jsonbsetObjectField(set(ARRAY[jsonb '{"unit": "km", "speed": 10}',
'{"unit": "km"}', '{"unit": "km", "speed": 10}']), 'speed', 'delete_key');
-- {10}
SELECT jsonbsetObjectField(set(ARRAY[jsonb '{"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,path text[],null_handle text='use_json_null') → jsonbset
jsonbsetExtractPathText(jsonbset,path text[],null_handle text='use_json_null') → textset
SELECT set(ARRAY[jsonb '{"speed": {"unit": "km", "value": 10}}',
'{"speed": {"unit": "km", "value": 20}}']) #> ARRAY[text 'speed', 'value'];
-- {10, 20}
SELECT set(ARRAY[jsonb '{"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') → textset
SELECT jsonbsetArrayElement(set(ARRAY[jsonb '["Grand Place", "La Bourse"]',
'["Palais Royal", "Manneken Pis"]']), 0);
-- {"\"Grand Place\"", "\"Palais Royal\""}
SELECT jsonbsetArrayElement(set(ARRAY[jsonb '["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"}
JSONB set concatenation
{jsonb,jsonbset} || {jsonbset,jsonb} → jsonbset
The value is concatenated with every element of the set, and the operand order is free.
SELECT set(ARRAY[jsonb '{"speed":10}', '{"speed":20}']) || '{"unit":"km"}'::jsonb;
-- {"{\"unit\": \"km\", \"speed\": 10}", "{\"unit\": \"km\", \"speed\": 20}"}
SELECT jsonb '{"unit":"km"}' || set(ARRAY[jsonb '{"speed":10}', '{"speed":20}']);
-- {"{\"unit\": \"km\", \"speed\": 10}", "{\"unit\": \"km\", \"speed\": 20}"}
JSONB set deletion
jsonbset - {integer,text,text[]} → jsonbset
jsonbset #- text[] → jsonbset
SELECT set(ARRAY[jsonb '{"unit": "km", "speed": 10}',
'{"unit": "km", "speed": 20}']) - text 'unit';
-- {"{\"speed\": 10}", "{\"speed\": 20}"}
SELECT set(ARRAY[jsonb '["Grand Place", "La Bourse"]',
'["Palais Royal", "Manneken Pis"]']) - 1;
-- {"[\"Grand Place\"]", "[\"Palais Royal\"]"}
SELECT set(ARRAY[jsonb '{"location":"Point(1 1)", "PoIs": ["Grand Place", "La Bourse"]}',
'{"location":"Point(2 2)", "PoIs": ["Palais Royal", "Manneken Pis"]}'])
#- ARRAY[text 'PoIs', '1'];
/* {"{\"PoIs\": [\"Grand Place\"], \"location\": \"Point(1 1)\"}", "{\"PoIs\":
[\"Palais Royal\"], \"location\": \"Point(2 2)\"}"} */
As shown below, the result of a delete operation may be an empty record or an empty array. To remove these values, the function setMinus can be used.
SELECT set(ARRAY[jsonb '{"unit": "km", "speed": 10, "light": true}',
'{"unit": "km", "speed": 20}']) - ARRAY[text 'unit', 'speed'];
-- {"{}", "{\"light\": true}"}
SELECT set(ARRAY[jsonb '["Grand Place", "La Bourse"]', '["Palais Royal"]']) - 0;
-- {[], "[\"La Bourse\"]"}
SELECT setMinus(set(ARRAY[jsonb '{"unit": "km", "speed": 10, "light": true}',
'{"unit": "km", "speed": 20}']) - ARRAY[text 'unit', 'speed'],
set(ARRAY[jsonb '{}', '[]']));
-- {"{\"light\": true}"}
SELECT setMinus(set(ARRAY[jsonb '["Grand Place", "La Bourse"]', '["Palais Royal"]']) - 0,
set(ARRAY[jsonb '{}', '[]']));
-- {"[\"La Bourse\"]"}
JSONB set exists
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\": 10}", "{\"speed\": 20, \"units\": \"km/h\"}"}
SELECT jsonbset '{"{\"speed\": 10}",
"{\"speed\": 20, \"units\": \"km/h\"}"}' ? text 'units';
-- {f,t}
SELECT jsonbset '{"{\"speed\": 10}",
"{\"speed\": 20, \"units\": \"km/h\"}"}' ?| ARRAY[text 'units', text 'lights'];
-- {f,t}
SELECT jsonbset '{"{\"speed\": 10}",
"{\"speed\": 20, \"units\": \"km/h\"}"}' ?& ARRAY[text 'speed', text 'units'];
-- {f,t}
JSONB set assignment
jsonbsetSet(jsonbset,path text[],val jsonb,create_missing boolean=true) → jsonbset jsonbsetSetLax(jsonbset,path text[],val jsonb,create_missing boolean=true, handle_null text='use_json_null') → 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_target'. As stated above, we kept PostgreSQL behavior for this function enabling the value 'return_target' whereas for all other query operations, this value has been replaced with 'return_null'.
SELECT jsonbsetSet(set(ARRAY[jsonb '{"speed":10}', '{"speed":20}']), ARRAY['units'],
'"km/h"'::jsonb);
-- {"{\"speed\": 10, \"units\": \"km/h\"}", "{\"speed\": 20, \"units\": \"km/h\"}"}
SELECT jsonbsetSet(set(ARRAY[jsonb '{"speed":10}', '{"speed":20}']), ARRAY['units'],
'"km/h"'::jsonb, false);
-- {"{\"speed\": 10}", "{\"speed\": 20}"}
SELECT jsonbsetSet(set(ARRAY[jsonb '{"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(set(ARRAY[jsonb '{"speed": 10, "units": "km/h"}', '{"speed": 20}']),
ARRAY['units'], NULL, true, 'delete_key');
-- {"{\"speed\": 10}", "{\"speed\": 20}"}
Return the jsonbset value with jsonb inserted
jsonbsetInsert(jsonbset,path text[],val jsonb,after boolean=false) → jsonbset
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(set(ARRAY[jsonb '{"speed":10}', '{"speed":20}']), ARRAY['units'],
'"km/h"'::jsonb);
-- {"{\"speed\": 10}", "{\"speed\": 20}"}
SELECT jsonbsetInsert(set(ARRAY[jsonb '{"speed":10}', '{"speed":20}']), ARRAY['units'],
'"km/h"'::jsonb, false);
-- {"{\"speed\": 10}", "{\"speed\": 20}"}
SELECT jsonbsetInsert(set(ARRAY[jsonb '{"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 boolean=false) → jsonbset
The last argument states whether null array elements are also stripped. Bare null values are never stripped.
SELECT jsonbsetStripNulls(set(ARRAY[jsonb '{"speed": 10, "lights": null}',
'{"speed": 20, "PoIs": ["Grand Place", "La Bourse", null]}']));
/* {"{\"speed\": 10}", "{\"PoIs\": [\"Grand Place\", \"La Bourse\", null], \"speed\":
20}"} */
SELECT jsonbsetStripNulls(set(ARRAY[jsonb '{"speed": 10, "lights": null}',
'{"speed": 20, "PoIs": ["Grand Place", "La Bourse", null]}']), true);
-- {"{\"speed\": 10}", "{\"PoIs\": [\"Grand Place\", \"La Bourse\"], \"speed\": 20}"}
SELECT jsonbsetStripNulls(set(ARRAY[jsonb
'{"road": "Bvd Gén. Jacques", "category": "primary"}',
'{"road": "Bvd de la Cambre", "category": "primary"}',
'{"road": "rue de l''Abbaye", "category": null}']));
/* {"{\"road\": \"rue de l'Abbaye\"}", "{\"road\": \"Bvd Gén. Jacques\", \"category\":
\"primary\"}", "{\"road\": \"Bvd de la Cambre\", \"category\": \"primary\"}"} */
As shown below, the function does not strip a bare null value. The function setMinus can be used for this purpose.
SELECT jsonbsetStripNulls(set(ARRAY[jsonb '{"speed": 10}', 'null',
'{"speed": 20, "PoIs": ["Grand Place", "La Bourse"]}']));
/* {"null", "{\"speed\": 10}", "{\"PoIs\": [\"Grand Place\", \"La Bourse\"], \"speed\":
20}"} */
SELECT setMinus(set(ARRAY[jsonb '{"speed": 10}', 'null',
'{"speed": 20, "PoIs": ["Grand Place", "La Bourse"]}']), set(ARRAY[jsonb 'null']));
-- {"{\"speed\": 10}", "{\"PoIs\": [\"Grand Place\", \"La Bourse\"], \"speed\": 20}"}
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\": 10}", "{\"speed\": 20, \"units\": \"km/h\"}"}
SELECT jsonbset '{"{\"speed\": 10}",
"{\"speed\": 20, \"units\": \"km/h\"}"}' @? '$.units';
-- {f,t}
SELECT jsonbsetPathExists(jsonbset '{"{\"speed\": 10}",
"{\"speed\": 20, \"units\": \"km/h\"}"}', '$.speed ? (@ > $min)', '{"min": 15}');
-- {f,t}
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\": 10}", "{\"speed\": 20, \"units\": \"km/h\"}"}
SELECT jsonbset '{"{\"speed\": 10}",
"{\"speed\": 20, \"units\": \"km/h\"}"}' @@ '$.speed > 15';
-- {f,t}
SELECT jsonbsetPathMatch(jsonbset '{"{\"speed\": 10}",
"{\"speed\": 20, \"units\": \"km/h\"}"}', '$.speed > $min', '{"min": 15}');
-- {f,t}
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(set(ARRAY[jsonb '{"speed":10}',
'{"speed": 20, "units": "km/h"}']), '$ ? (@.speed >= $min && @.speed <= $max)',
'{"min":10, "max":30}');
-- {"[{\"speed\": 10}]", "[{\"speed\": 20, \"units\": \"km/h\"}]"}
SELECT jsonbsetPathQueryArrayTz(set(ARRAY[jsonb
'{"cameraId":25, "inspections":["2001-01-03", "2001-01-06", "2001-01-09"]}',
'{"cameraId":35, "inspections":["2001-01-04", "2001-01-07", "2001-01-10"]}']),
'$.inspections ? (@.timestamp_tz() >= $min.timestamp_tz() &&
@.timestamp_tz() <= $max.timestamp_tz())', '{"min":"2001-01-05", "max":"2001-01-09"}');
-- {"[\"2001-01-07\"]", "[\"2001-01-06\", \"2001-01-09\"]"}
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(set(ARRAY[jsonb '{"speed":10}',
'{"speed": 20, "units": "km/h"}']), '$.speed ? (@ >= $min && @ <= $max)',
'{"min":10, "max":30}');
-- {10, 20}
SELECT jsonbsetPathQueryArrayTz(set(ARRAY[jsonb
'{"cameraId":25, "inspections":["2001-01-03", "2001-01-06", "2001-01-09"]}',
'{"cameraId":35, "inspections":["2001-01-04", "2001-01-07", "2001-01-10"]}']),
'$.inspections ? (@.datetime() >= $min.datetime() && @.datetime() <= $max.datetime())',
'{"min":"2001-01-05", "max":"2001-01-09"}');
-- {"[\"2001-01-07\"]", "[\"2001-01-06\", \"2001-01-09\"]"}