Solución:
Postgres 9.4 o más reciente
Obviamente inspirado en esta publicación, Postgres 9.4 agregó las funciones faltantes:
¡Gracias a Laurence Rowe por el parche y a Andrew Dunstan por comprometerse!
json_array_elements_text(json)jsonb_array_elements_text(jsonb)
Para desanidar la matriz JSON. Entonces usa array_agg() o un constructor ARRAY para construir un Postgres formación de eso. O string_agg() para construir un text cuerda.
Agregue elementos no anidados por fila en un LATERAL o subconsulta correlacionada. Luego se conserva el orden original y no necesitamos ORDER BY, GROUP BY o incluso una clave única en la consulta externa. Ver:
- ¿Cómo aplicar ORDER BY y LIMIT en combinación con una función agregada?
Reemplace ‘json’ con ‘jsonb’ para jsonb en todos los siguientes códigos SQL.
SELECT t.tbl_id, d.list
FROM tbl t
CROSS JOIN LATERAL (
SELECT string_agg(d.elem::text, ', ') AS list
FROM json_array_elements_text(t.data->'tags') AS d(elem)
) d;
Sintaxis corta:
SELECT t.tbl_id, d.list
FROM tbl t, LATERAL (
SELECT string_agg(value::text, ', ') AS list
FROM json_array_elements_text(t.data->'tags') -- col name default: "value"
) d;
Relacionado:
- ¿Cuál es la diferencia entre LATERAL y una subconsulta en PostgreSQL?
Constructor ARRAY en subconsulta correlacionada:
SELECT tbl_id, ARRAY(SELECT json_array_elements_text(t.data->'tags')) AS txt_arr
FROM tbl t;
Relacionado:
- ¿Cómo aplicar ORDER BY y LIMIT en combinación con una función agregada?
Sutil diferencia: null los elementos se conservan en matrices. Esto no es posible en las consultas anteriores que producen un text cadena, que no puede contener null valores. los verdadera representación es una matriz.
Envoltorio de función
Para uso repetido, para hacer esto aún más simple, encapsule la lógica en una función:
CREATE OR REPLACE FUNCTION json_arr2text_arr(_js json)
RETURNS text[] LANGUAGE sql IMMUTABLE PARALLEL SAFE AS
'SELECT ARRAY(SELECT json_array_elements_text(_js))';
Que sea un Función SQL, entonces puede ser entretejido en consultas más grandes.
Hazlo IMMUTABLE (porque lo es) para evitar la evaluación repetida en consultas más grandes y permitirlo en expresiones de índice.
Hazlo PARALLEL SAFE (en Postgres 9.6 ¡o más tarde!) para no obstaculizar el paralelismo. Ver:
- ¿Cuándo marcar funciones como PARALELO RESTRINGIDO vs PARALELO SEGURO?
Llama:
SELECT tbl_id, json_arr2text_arr(data->'tags')
FROM tbl;
db <> violín aquí
Postgres 9.3 o anterior
Usa la función json_array_elements(). Pero tenemos cadenas entre comillas dobles de eso.
Consulta alternativa con agregación en la consulta externa. CROSS JOIN elimina filas con matrices vacías o faltantes. También puede ser útil para procesar elementos. Necesitamos una clave única 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;
Constructor de ARRAY, todavía con cadenas entre comillas:
SELECT tbl_id, ARRAY(SELECT json_array_elements(t.data->'tags')) AS quoted_txt_arr
FROM tbl t;
Tenga en cuenta que null se convierte al valor de texto “nulo”, a diferencia de lo anterior. Incorrecto, estrictamente hablando y potencialmente ambiguo.
Pobre desquitar con 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 una sola fila 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;
Las cadenas forman una subconsulta correlacionada:
SELECT tbl_id, (SELECT string_agg(trim(value::text, '"'), ', ')
FROM json_array_elements(t.data->'tags')) AS list
FROM tbl t;
Constructor de ARRAY:
SELECT tbl_id, ARRAY(SELECT trim(value::text, '"')
FROM json_array_elements(t.data->'tags')) AS txt_arr
FROM tbl t;
Original (desactualizado) Violín SQL.
db <> violín aquí.
Relacionado:
- Necesita seleccionar un elemento de matriz JSON dinámicamente de una tabla postgresql
Notas (desactualizado desde la página 9.4)
Necesitaríamos un json_array_elements_text(json), el gemelo de json_array_elements(json) para volver correctamente text valores de una matriz JSON. Pero eso parece faltar en el arsenal provisto de funciones JSON. O alguna otra función para extraer un text valor de un escalar JSON valor. Parece que también me estoy perdiendo ese.
Así que improvisé con trim(), pero eso fallará para casos no triviales …
PG 9.4+
La respuesta aceptada es definitivamente lo que necesita, pero en aras de la simplicidad, aquí hay un ayudante que uso para esto:
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$;
Entonces solo haz:
SELECT jsonb_array_to_text_array('["a", "b", "c"]'::jsonb);
Actualizado el 23/02/2020 en respuesta a los comentarios: Los comentarios son correctos de que esto podría ser más eficiente. En el momento en que publiqué, no se ofrecía una solución modular, por lo que ofrecí una en serio, aunque no era la óptima. Desde entonces, Erwin ha actualizado su respuesta con una función simple y eficiente, por lo que nunca actualicé la mía. Actualizándolo ahora ya que todavía se está prestando atención a esta respuesta
Una actualización más, porque esto solo me mordió: La función anterior volverá null si no hay valores. Esto puede no ser deseable dependiendo de su situación. Aquí hay una función que devuelve una matriz vacía si el valor no es null, pero aún devuelve nulo si la entrada es 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$
;
Esta pregunta se hizo en las listas de correo de PostgreSQL y se me ocurrió esta forma hackear de convertir texto JSON a tipo de texto PostgreSQL a través del operador de extracción 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
☃
Básicamente, convierte el valor en una matriz de un solo elemento y luego solicita el primer elemento.
Otro enfoque sería utilizar este operador para extraer todos los campos uno por uno. Pero para matrices grandes, esto probablemente sea más lento, ya que necesita analizar toda la cadena JSON para cada elemento de la matriz, lo que genera una complejidad 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
☃