Como transformar o array JSON em array Postgres?

Postgres 9.4 ou mais recente

Inspirado por este post , o Postgres 9.4 adicionou as funções ausentes para desaninhar arrays JSON.
_{Obrigado a Laurence Rowe pelo patch e Andrew Dunstan por se comprometer!}

• json_array_elements_text(json)
• jsonb_array_elements_text(jsonb)

Use array_agg()ou um construtor ARRAY para construir um array Postgres (type text[]) a partir do conjunto resultante de text.
Ou string_agg()para construir uma string com uma lista de valores (tipo text).

Focando na saída do arraytext[] ( ), não na string ( text). Diferença importante : nullos elementos são preservados em arrays reais . Isso não é possível em uma string, que não pode conter nullvalores. A verdadeira representação é uma matriz.

Substitua 'jsonb' por 'json' para digitar jsonem todo o código SQL a seguir.

TLDR: use uma função personalizada

Encapsule a lógica em uma função para uso repetido:

CREATE OR REPLACE FUNCTION jsonb_array_to_text_array(_js jsonb)
  RETURNS text[]
  LANGUAGE sql IMMUTABLE PARALLEL SAFE STRICT AS
'SELECT ARRAY(SELECT jsonb_array_elements_text(_js))';

Ligar:

SELECT tbl_id, jsonb_array_to_text_array(data->'tags')
FROM   tbl;

• LANGUAGE sqlpara a função simples. (Mais rápido em meus últimos testes com o Postgres 14.)
• IMMUTABLE(porque é) para evitar avaliações repetidas em consultas maiores e permitir seu uso em expressões de índice.
• PARALLEL SAFE(no Postgres 9.6 ou posterior!) para permitir a execução paralela em grandes consultas. Ver:
  • Quando marcar funções como PARALLEL RESTRICTED vs PARALLEL SAFE?
• STRICTpara retornar nullpara nullentrada. Também: mais rápido. A função não pode ser embutida de qualquer maneira por causa do construtor ARRAY, portanto, STRICTnão pode prejudicar isso.

Essa função com um STRICTmodificador também é o mais fiel possível ao original , pois retorna nullpara nullentrada e uma matriz vazia para entrada de matriz vazia. Melhor do que todas as consultas abaixo.

Para completar: use to_jsonb()para o array SQL reverso → jsonbconversão.

Várias soluções, passo a passo

Agregue imediatamente por linha em uma LATERALou subconsulta correlacionada, então a ordem original é preservada e não precisamos ORDER BY, GROUP BYou mesmo uma chave exclusiva na consulta externa. Ver:

• Como aplicar ORDER BY e LIMIT em combinação com uma função agregada?
• Qual é a diferença entre LATERAL e uma subconsulta no PostgreSQL?
• Como aplicar ORDER BY e LIMIT em combinação com uma função agregada?
• Por que array_agg() é mais lento que o construtor ARRAY() não agregado?

Consulta básica, retorna nullpara matriz ou nullentrada vazia:

SELECT t.tbl_id, d.txt_arr
FROM   tbl t
CROSS  JOIN LATERAL (
   SELECT array_agg(d.elem) AS txt_arr
   FROM   jsonb_array_elements_text(t.data->'tags') AS d(elem)
   ) AS d;

Sintaxe curta, retorna nullpara matriz ou nullentrada vazia:

SELECT t.tbl_id, d.txt_arr
FROM   tbl t, LATERAL (
   SELECT array_agg(value) AS txt_arr
   FROM   jsonb_array_elements_text(t.data->'tags')  -- default name is "value"
   ) d;

Mais curto (e mais rápido) com o construtor ARRAY, retorna uma matriz vazia para uma matriz ou nullentrada vazia:

SELECT t.tbl_id, t.data->'tags' AS jsonb_arr, d.txt_arr
FROM   tbl t, LATERAL (
   SELECT ARRAY(SELECT jsonb_array_elements_text(t.data->'tags'))
   ) d(txt_arr);

Ainda mais curto (e mais rápido) com subconsulta correlacionada, retorna matriz vazia para matriz ou nullentrada vazia:

SELECT tbl_id, ARRAY(SELECT jsonb_array_elements_text(t.data->'tags')) AS txt_arr
FROM   tbl t;

db<>fique aqui

Todos os itens acima preservam a ordem original dos elementos.

Postgres 9.3 ou anterior

Use a função json_array_elements(). Mas temos strings com aspas duplas dele.

Consulta alternativa com agregação na consulta externa. CROSS JOINremove linhas com matrizes ausentes ou vazias. Também pode ser útil para processar elementos. Precisamos de uma chave exclusiva para agregar:

SELECT t.tbl_id, string_agg(d.elem::text, ', ') AS list
FROM   tbl t
CROSS  JOIN LATERAL json_array_elements(t.data->'tags') AS d(elem)
GROUP  BY t.tbl_id;

Construtor ARRAY, ainda com strings entre aspas:

SELECT tbl_id, ARRAY(SELECT json_array_elements(t.data->'tags')) AS quoted_txt_arr
FROM   tbl t;

Observe que nullé convertido para o valor de texto "null", diferentemente do anterior. Incorreto, estritamente falando, e potencialmente ambíguo.

Pobre homem está sem aspas com trim():

SELECT t.tbl_id, string_agg(trim(d.elem::text, '"'), ', ') AS list
FROM   tbl t, json_array_elements(t.data->'tags') d(elem)
GROUP  BY 1;

Recupere uma única linha de tbl:

SELECT string_agg(trim(d.elem::text, '"'), ', ') AS list
FROM   tbl t, json_array_elements(t.data->'tags') d(elem)
WHERE  t.tbl_id = 1;

Strings formam subconsulta correlacionada:

SELECT tbl_id, (SELECT string_agg(trim(value::text, '"'), ', ')
                FROM   json_array_elements(t.data->'tags')) AS list
FROM   tbl t;

Construtor ARRAY:

SELECT tbl_id, ARRAY(SELECT trim(value::text, '"')
                     FROM   json_array_elements(t.data->'tags')) AS txt_arr
FROM   tbl t;

db<>fiddle aqui
_{Velho sqlfiddle}

Relacionado:

• Precisa selecionar um elemento de matriz JSON dinamicamente de uma tabela PostgreSQL

Notas originais (desatualizadas desde a página 9.4)

Precisaríamos de um json_array_elements_text(json), o gêmeo de json_array_elements(json)para retornar valores apropriados textde uma matriz JSON. Mas isso parece estar faltando no arsenal fornecido de funções JSON . Ou alguma outra função para extrair um textvalor de um valor escalar json. Parece que estou sentindo falta disso também.
Então eu improvisei com trim(), mas isso falhará para casos não triviais ...

PG 9.4+

A resposta aceita é definitivamente o que você precisa, mas por uma questão de simplicidade, aqui está um auxiliar que uso para isso:

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$;

Depois é só fazer:

SELECT jsonb_array_to_text_array('["a", "b", "c"]'::jsonb);

Atualizado em 23/02/2020 em resposta aos comentários : Os comentários estão corretos de que isso poderia ser mais eficiente. Na época em que postei, não havia solução modularizada oferecida, então ofereci uma a sério, se não fosse a ideal. Desde então, Erwin atualizou sua resposta com uma função simples e eficiente, então nunca atualizei a minha. Atualizando agora, pois ainda há atenção para esta resposta

Mais uma atualização, porque isso me mordeu : A função acima retornará nullse não houver valores. Isso pode não ser desejável dependendo da sua situação. Aqui está uma função que retorna uma matriz vazia se o valor não for null, mas ainda retorna null se a entrada for nula.

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$
;

Essa pergunta foi feita nas listas de discussão do PostgreSQL e eu criei essa maneira hackeada de converter texto JSON para o tipo de texto PostgreSQL por meio do operador de extração de campo JSON:

CREATE FUNCTION json_text(json) RETURNS text IMMUTABLE LANGUAGE sql
AS $$ SELECT ('['||$1||']')::json->>0 $$;

db=# select json_text(json_array_elements('["hello",1.3,"\u2603"]'));
 json_text 
-----------
 hello
 1.3
 ☃

Basicamente, ele converte o valor em uma matriz de elemento único e, em seguida, solicita o primeiro elemento.

Outra abordagem seria usar esse operador para extrair todos os campos um por um. Mas para matrizes grandes, isso provavelmente é mais lento, pois precisa analisar toda a string JSON para cada elemento da matriz, levando à complexidade O(n^2).

CREATE FUNCTION json_array_elements_text(json) RETURNS SETOF text IMMUTABLE LANGUAGE sql
AS $$ SELECT $1->>i FROM generate_series(0, json_array_length($1)-1) AS i $$;

db=# select json_array_elements_text('["hello",1.3,"\u2603"]');
 json_array_elements_text 
--------------------------
 hello
 1.3
 ☃

Já testei algumas opções. Aqui está a minha consulta favorita. Suponha que temos uma tabela contendo o campo id e json. O campo json contém array, que queremos transformar em array pg.

SELECT * 
FROM   test 
WHERE  TRANSLATE(jsonb::jsonb::text, '[]','{}')::INT[] 
       && ARRAY[1,2,3];

Está funcionando em qualquer lugar e mais rápido do que outros, mas parece maluco)

Primeiramente, a matriz json é convertida como texto e, em seguida, apenas alteramos os colchetes para parênteses. Finalmente, o texto está sendo convertido como array do tipo necessário.

SELECT TRANSLATE('[1]'::jsonb::text, '[]','{}')::INT[];

e se você preferir arrays text[]

SELECT TRANSLATE('[1]'::jsonb::text, '[]','{}')::TEXT[];

Essas poucas funções, tiradas das respostas a esta pergunta , são o que estou usando e estão funcionando muito bem

CREATE OR REPLACE FUNCTION json_array_casttext(json) RETURNS text[] AS $f$
    SELECT array_agg(x) || ARRAY[]::text[] FROM json_array_elements_text($1) t(x);
$f$ LANGUAGE sql IMMUTABLE;

CREATE OR REPLACE FUNCTION jsonb_array_casttext(jsonb) RETURNS text[] AS $f$
    SELECT array_agg(x) || ARRAY[]::text[] FROM jsonb_array_elements_text($1) t(x);
$f$ LANGUAGE sql IMMUTABLE;

CREATE OR REPLACE FUNCTION json_array_castint(json) RETURNS int[] AS $f$
    SELECT array_agg(x)::int[] || ARRAY[]::int[] FROM json_array_elements_text($1) t(x);
$f$ LANGUAGE sql IMMUTABLE;

CREATE OR REPLACE FUNCTION jsonb_array_castint(jsonb) RETURNS int[] AS $f$
    SELECT array_agg(x)::int[] || ARRAY[]::int[] FROM jsonb_array_elements_text($1) t(x);
$f$ LANGUAGE sql IMMUTABLE;

Em cada um deles, concatenando com um array vazio, eles lidam com um caso que me fez quebrar a cabeça um pouco, em que se você tentar lançar um array vazio de json/ jsonbsem ele, você não obterá nada retornado, em vez de um array vazio ( {}) como seria de esperar. Tenho certeza de que há alguma otimização para eles, mas eles são deixados como estão para simplificar a explicação do conceito.

Eu gosto da solução de tradução mencionada, então aqui está uma homenagem que não interfere no conteúdo do texto:

SELECT ('{' || RIGHT(LEFT( '[1]'::json::text ,-1),-1) || '}')::INTEGER[]

A solução da resposta aceita:

CREATE OR REPLACE FUNCTION jsonb_array_to_text_array(_js jsonb)
  RETURNS text[]
  LANGUAGE sql IMMUTABLE PARALLEL SAFE STRICT AS
'SELECT ARRAY(SELECT jsonb_array_elements_text(_js))';

é doce e sucinto, mas tem uma ressalva para mim. Quando invocado como:

SELECT jsonb_array_to_text_array(NULL);

Ele retorna NULL, conforme desejado para o meu caso de uso. No entanto, quando passado um valor nulo de uma propriedade JSON, como:

SELECT jsonb_array_to_text_array('{"my_value_is_null": null}'::JSONB -> 'my_value_is_null');

Ele lança um erro informando "não é possível extrair elementos de um escalar".

Talvez eu esteja perdendo uma parte vital da compreensão do manuseio do valor NULL, mas rastreei o problema de volta à função personalizada, onde a propriedade com valor nulo é passada como uma picada de valor 'null'. Então minha versão modificada:

CREATE OR REPLACE FUNCTION jsonb_array_to_text_array(_js jsonb)
  RETURNS text[]
  LANGUAGE sql IMMUTABLE PARALLEL SAFE STRICT AS
$$SELECT CASE WHEN _js = 'null' THEN NULL ELSE ARRAY(SELECT jsonb_array_elements_text(_js)) END$$;

lida com isso também retornando NULL nesse caso, como se passasse um valor SQL NULL regular.