How to turn json array into postgres json array?
I have a column data of type json that holds JSON documents like this:
{
"name": "foo",
"tags": ["foo", "bar"]
}
I would like to turn the nested tags array into a concatenated string ('foo, bar'). That would be easily possible with the array_to_string() function in theory. However, this function does not accept json input. So I wonder how to turn this JSON array into a Postgres array (type text[])?
To turn postgres json array -
PG 9.4+
The accepted answer is definitely what you need, but for the sake of simplicity here is a helper I use for this:
CREATE OR REPLACE FUNCTION jsonb_array_to_text_array(p_input jsonb)
RETURNS text[]
LANGUAGE sql
IMMUTABLE
AS $function$
SELECT array_agg(ary)::text[] FROM jsonb_array_elements_text(p_input) AS ary;
$function$;
Then just do:
SELECT jsonb_array_to_text_array('["a", "b", "c"]'::jsonb);
Updated 2/23/2020 in response to comments: Comments are correct that this could be more efficient. At the time I posted there was no modularized solution offered so I offered one in earnest, if non-optimal. Since then Erwin has updated his answer with a simple and efficient function so I never updated mine. Updating it now since there is still attention coming to this answer
One more update, because this just bit me: The above function will return null if there are no values. This may not be desirable depending on your situation. Here's a function which returns an empty array if the value is not null, but still returns null if the input is null.
CREATE OR REPLACE FUNCTION jsonb_array_to_text_array_strict(p_input jsonb)
RETURNS text[]
LANGUAGE sql
IMMUTABLE
AS $function$
SELECT
CASE
WHEN p_input IS null
THEN null
ELSE coalesce(ary_out, ARRAY[]::text[])
END
FROM (
SELECT array_agg(ary)::text[] AS ary_out
FROM jsonb_array_elements_text(p_input) AS ary
) AS extracted;
$function$;