How to turn json array into postgres json array?

300    Asked by DominicPoole in SQL Server , Asked on Oct 3, 2022

 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[])?


Answered by Donna Chapman

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$

;



Your Answer

Interviews

Parent Categories