EXTRACT_VALUES_FROM_JSON Function

Parameters (String)json, (String)key, (String)seperator ;

Returns: String;

Function to extract all values from a JSON string for a given key. The function will retrieve values in all levels of the JSON structure, processing embedded JSON objects and Arrays recursively.

• The first parameter is the input JSON string.
• The second parameter is the key for which values will be retrieved.
• The third parameter is the separator used to separate the values in the final result. To output values on separate lines, use '\n' as the separator.
• If the input string is not in the JSON format, it will be returned unchanged.
• If the key is set to NULL, values for all keys will be obtained.

One use of this function is to retrieve the text value of comments from rich text where the formatting is part of the JSON object.

Example 1: EXTRACT_VALUES_FROM_JSON('[{"text":"Hello"},{"text","World"}]','text',' ') = 'Hello World'
Example 2: EXTRACT_VALUES_FROM_JSON('[{"prefix":"Hello"},{"text","World"}]','text',' ') = 'World'
Example 3: EXTRACT_VALUES_FROM_JSON('[{"prefix":"Hello"},{"text","World"}]',NULL,'-') = 'Hello-World'
Example 4: EXTRACT_VALUES_FROM_JSON('Hello World!',text,'-') = 'Hello World!'