This page is in progress. Please contact us if you need more SQL functions to be documented.
knife_extract_text(data::jsonb, paths::jsonb) -> text[]
Extract elements from jsonb data given jsonb array of paths paths.
Path is a jsonb array each element of which is either path element or filter.
Path element is a jsonb string. It specifies keyword to follow in current map.
Filter is a jsonb object. Only objects which contain filter objects are left in current array.
knife_extract iterates over each array while following path and flattens results.
Example:
select knife_extract('{"telecom": [{"system": "phone", "value": "abc"}, {"system": "email", "value": "def"}]}', '[["telecom", {"system": "email"}]]');
knife_extract
-------------------------------------------------
{"{\"value\": \"def\", \"system\": \"email\"}"}
(1 row)
knife_extract_text(data::jsonb, paths::jsonb) -> text[]
Extract strings from jsonb data given jsonb array of paths paths.
Like knife_extract but return only strings.
Example:
SELECT knife_extract_text(
'{"a": {"b": [{"c": "l", "d": "o"}, {"c": 1, "d": ["b", "o", "k"]}]}}',
'[["a","b","c"],["a","b","d"]]'
);
knife_extract_text
--------------------
{l,o,b,o,k}
(1 row)
select knife_extract_text(
'{"telecom": [{"system": "phone", "value": "abc"}, {"system": "email", "value": "def"}]}',
'[["telecom", {"system": "email"}, "value"]]'
);
knife_extract_text
--------------------
{def}
(1 row)
aidbox_text_search
aidbox_text_search(texts::text[]) -> text
Concatenates strings texts with spaces, removes accents, adds leading and trailing spaces.
Example:
SELECT aidbox_text_search('{abc,def}');
?column?
----------
abc def
(1 row)
SELECT aidbox_text_search('{abc,def}') ILIKE '% ab%';
?column?
----------
t
(1 row)
