Chapter 15. JSON Types in MobilityDB

Extract a JSON object field specified by a key

jsonbObjectField(jsonb,text) → jsonb
jsonbObjectFieldText(jsonb,text) → text

SELECT jsonbObjectField(jsonb '{"a": {"b": 1}}', 'a');
-- {"b": 1}
SELECT jsonbObjectFieldText(jsonb '{"a": "x"}', 'a');
-- x

Extract a JSON value specified by a path

jsonbExtractPath(jsonb,path text[]) → jsonb
jsonbExtractPathText(jsonb,path text[]) → text

SELECT jsonbExtractPath(jsonb '{"a": {"b": [10, 20]}}', ARRAY['a', 'b', '1']);
-- 20
SELECT jsonbExtractPathText(jsonb '{"a": {"b": "z"}}', ARRAY['a', 'b']);
-- z

Extract an element from a JSON array

jsonbArrayElement(jsonb,integer) → jsonb
jsonbArrayElementText(jsonb,integer) → text

Elements are numbered from zero, and a negative integer counts from the end of the array.

SELECT jsonbArrayElement(jsonb '[1, [2, 3]]', 1);
-- [2, 3]
SELECT jsonbArrayElement(jsonb '[1, 2, 3]', -1);
-- 3
SELECT jsonbArrayElementText(jsonb '["x", "y"]', 0);
-- x

Return the number of elements of a JSON array

jsonbArrayLength(jsonb) → integer

SELECT jsonbArrayLength(jsonb '[1, 2, 3]');
-- 3

Return the elements of a JSON array as rows

jsonbArrayElements(jsonb) → setof jsonb
jsonbArrayElementsText(jsonb) → setof text

SELECT * FROM jsonbArrayElements(jsonb '[1, "two", {"three": 3}]');
-- 1
-- "two"
-- {"three": 3}
SELECT * FROM jsonbArrayElementsText(jsonb '[1, "two"]');
-- 1
-- two

Return the fields of a JSON object as rows of a key and a value

jsonbEach(jsonb) → setof (key text,value jsonb)
jsonbEachText(jsonb) → setof (key text,value text)

SELECT * FROM jsonbEach(jsonb '{"a": 1, "b": [2]}') ORDER BY key;
-- a | 1
-- b | [2]
SELECT * FROM jsonbEachText(jsonb '{"a": 1, "b": "x"}') ORDER BY key;
-- a | 1
-- b | x

Return the keys of a JSON object as rows

jsonbObjectKeys(jsonb) → setof text

SELECT * FROM jsonbObjectKeys(jsonb '{"b": 1, "a": 2}') ORDER BY 1;
-- a
-- b

Return the indented text of a JSON value

jsonbPretty(jsonb) → text

SELECT jsonbPretty(jsonb '{"a": 1}');
-- {
--     "a": 1
-- }

Concatenate two JSON values

jsonbConcat(jsonb,jsonb) → jsonb

SELECT jsonbConcat(jsonb '{"a": 1}', jsonb '{"b": 2}');
-- {"a": 1, "b": 2}

Delete a key, the keys of an array, an array element or the item at a path from a JSON value

jsonbDelete(jsonb,text) → jsonb
jsonbDeleteArray(jsonb,text[]) → jsonb
jsonbDeleteIndex(jsonb,integer) → jsonb
jsonbDeletePath(jsonb,path text[]) → jsonb

SELECT jsonbDelete(jsonb '{"a": 1, "b": 2}', 'a');
-- {"b": 2}
SELECT jsonbDeleteArray(jsonb '{"a": 1, "b": 2, "c": 3}', ARRAY['a', 'c']);
-- {"b": 2}
SELECT jsonbDeleteIndex(jsonb '[1, 2, 3]', -1);
-- [1, 2]
SELECT jsonbDeletePath(jsonb '{"a": {"b": 1, "c": 2}}', ARRAY['a', 'b']);
-- {"a": {"c": 2}}

Does a text, or any or all the texts of an array, exist as a top-level key or array element of a JSON value?

jsonbExists(jsonb,text) → boolean
jsonbExistsAny(jsonb,text[]) → boolean
jsonbExistsAll(jsonb,text[]) → boolean

SELECT jsonbExists(jsonb '{"a": 1}', 'a');
-- true
SELECT jsonbExistsAny(jsonb '{"a": 1}', ARRAY['b', 'a']);
-- true
SELECT jsonbExistsAll(jsonb '{"a": 1}', ARRAY['b', 'a']);
-- false

Does the first JSON value contain, or is it contained in, the second one?

jsonbContains(jsonb,jsonb) → boolean
jsonbContained(jsonb,jsonb) → boolean

SELECT jsonbContains(jsonb '{"a": 1, "b": 2}', jsonb '{"a": 1}');
-- true
SELECT jsonbContained(jsonb '{"a": 1}', jsonb '{"a": 1, "b": 2}');
-- true

Return a JSON value with the item at a path set to a value

jsonbSet(jsonb,path text[],val jsonb,create_missing boolean=true) → jsonb
jsonbSetLax(jsonb,path text[],val jsonb,create_missing boolean=true,
  handle_null text='use_json_null') → jsonb

The item is added when it does not exist and create_missing is true. Function jsonbSetLax treats a NULL value according to handle_null, which is one of 'raise_exception', 'use_json_null', 'delete_key' or 'return_target'.

SELECT jsonbSet(jsonb '{"a": 1}', ARRAY['b'], jsonb '2');
-- {"a": 1, "b": 2}
SELECT jsonbSet(jsonb '{"a": 1}', ARRAY['b'], jsonb '2', false);
-- {"a": 1}
SELECT jsonbSetLax(jsonb '{"a": 1}', ARRAY['a'], NULL);
-- {"a": null}
SELECT jsonbSetLax(jsonb '{"a": 1}', ARRAY['a'], NULL, true, 'delete_key');
-- {}

Return a JSON value with a value inserted at a path

jsonbInsert(jsonb,path text[],val jsonb,after boolean=false) → jsonb

SELECT jsonbInsert(jsonb '[1, 3]', ARRAY['1'], jsonb '2');
-- [1, 2, 3]
SELECT jsonbInsert(jsonb '[1, 3]', ARRAY['1'], jsonb '2', true);
-- [1, 3, 2]

Return a JSON value without its object fields whose value is null

jsonbStripNulls(jsonb,strip_in_arrays boolean=false) → jsonb

The last argument states whether null array elements are also stripped.

SELECT jsonbStripNulls(jsonb '{"a": null, "b": [1, null]}');
-- {"b": [1, null]}
SELECT jsonbStripNulls(jsonb '{"a": null, "b": [1, null]}', true);
-- {"b": [1]}

Does the JSON path return any item for a JSON value?

jsonbPathExists(jsonb,jsonpath,vars jsonb='{}',silent boolean=false) → boolean
jsonbPathExistsTz(jsonb,jsonpath,vars jsonb='{}',silent boolean=false) → boolean

The last argument suppresses the following errors: missing object field or array element, unexpected JSON item type, datetime and numeric errors; the result of a suppressed error is NULL. The functions ending in Tz compare date and time values across time zones.

SELECT jsonbPathExists(jsonb '{"a": [1, 2, 3]}', '$.a[*] ? (@ > 2)');
-- true
SELECT jsonbPathExists(jsonb '{"a": [1, 2, 3]}', '$.a[*] ? (@ > $x)', '{"x": 5}');
-- false

Return the result of a JSON path predicate check for a JSON value

jsonbPathMatch(jsonb,jsonpath,vars jsonb='{}',silent boolean=false) → boolean
jsonbPathMatchTz(jsonb,jsonpath,vars jsonb='{}',silent boolean=false) → boolean

SELECT jsonbPathMatch(jsonb '{"a": [1, 2, 3]}', 'exists($.a[*] ? (@ > 2))');
-- true

Return the items a JSON path returns for a JSON value, as rows, as a JSON array, or the first one

jsonbPathQuery(jsonb,jsonpath,vars jsonb='{}',silent boolean=false) → setof jsonb
jsonbPathQueryTz(jsonb,jsonpath,vars jsonb='{}',silent boolean=false) → setof jsonb
jsonbPathQueryArray(jsonb,jsonpath,vars jsonb='{}',silent boolean=false) → jsonb
jsonbPathQueryArrayTz(jsonb,jsonpath,vars jsonb='{}',silent boolean=false) → jsonb
jsonbPathQueryFirst(jsonb,jsonpath,vars jsonb='{}',silent boolean=false) → jsonb
jsonbPathQueryFirstTz(jsonb,jsonpath,vars jsonb='{}',silent boolean=false) → jsonb

SELECT * FROM jsonbPathQuery(jsonb '{"a": [1, 2, 3]}', '$.a[*] ? (@ >= 2)');
-- 2
-- 3
SELECT jsonbPathQueryArray(jsonb '{"a": [1, 2, 3]}', '$.a[*] ? (@ >= 2)');
-- [2, 3]
SELECT jsonbPathQueryFirst(jsonb '{"a": [1, 2, 3]}', '$.a[*] ? (@ >= 2)');
-- 2

Compare two JSON values, or return the hash value of a JSON value

jsonb {=, <>, <, <=, >=, >} jsonb → boolean
cmp(jsonb,jsonb) → integer
hash(jsonb) → integer
hashExtended(jsonb,seed bigint) → bigint

The operators are those of PostgreSQL, and the functions eq, ne, lt, le, ge and gt answer as them, together with cmp, which returns -1, 0, or 1. Functions hash and hashExtended answer as the functions jsonb_hash and jsonb_hash_extended of PostgreSQL.

SELECT eq(jsonb '{"a": 1}', jsonb '{"a": 1}');
-- true
SELECT cmp(jsonb '1', jsonb '2');
-- -1