By parsing JSON array, you get values in brackets and double quotas and might need to make the string more human readable. Regular expression REGEXP_REPLACE can be used for it in your sql.

Input string: [“Blue”], [“Pink”], [“Black”]

Output string: Blue; Pink; Black

Action: Get rid of brackets with double quotas and replace comma by semicolon.

Code:

SELECT
 '["Blue"], ["Pink"], ["Black"]' as original_string,
 REGEXP_REPLACE(original_string, '[^A-Za-z,]', '') AS alpha_and_comma_chars_only,
 REGEXP_REPLACE(alpha_and_comma_chars_only, ',', '; ') AS replaced_comma
;

Result:

original_string	alpha_and_comma_chars_only	replaced_comma
[“Blue”], [“Pink”], [“Black”]	Blue,Pink,Black	Blue; Pink; Black

Explanation: