Ezequiel, parte de este gran staff, nos hizo el favor de escribir esta reseña porque controla a la perfección el tema.
Solución:
tienes que desatar los array de json-objects primero usando la función (json_array_elementso jsonb_array_elements si tiene tipo de datos jsonb), entonces puede acceder a los valores especificando el key.
WITH json_test (col) AS (
values (json '["name":"Mickey Mouse","age":10,"name":"Donald Duck","age":5]')
)
SELECT
y.x->'name' "name"
FROM json_test jt,
LATERAL (SELECT json_array_elements(jt.col) x) y
-- outputs:
name
--------------
"Mickey Mouse"
"Donald Duck"
Para obtener un recuento de nombres únicos, es una consulta similar a la anterior, excepto que se aplica la función agregada de recuento distinto a y.x->>name
WITH json_test (col) AS (
values (json '["name":"Mickey Mouse","age":10,"name":"Donald Duck","age":5]')
)
SELECT
COUNT( DISTINCT y.x->>'name') distinct_names
FROM json_test jt,
LATERAL (SELECT json_array_elements(jt.col) x) y
Es necesario usar ->> en lugar de -> como el anterior (->>) convierte el valor extraído como texto, lo que admite la comparación de igualdad (necesaria para un recuento distinto), mientras que el último (->) extrae el valor como json, que no admite la comparación de igualdad.
Alternativamente, convierta el json como jsonb y use jsonb_array_elements. JSONB admite la comparación de igualdad, por lo que es posible usar COUNT DISTINCT junto con la extracción a través de ->es decir
COUNT(DISTINCT (y.x::jsonb)->'name')
Puedes usar jsonb_array_elements (cuando usas jsonb) o json_array_elements (cuando usas json) para ampliar la array elementos.
Por ejemplo:
WITH sample_data_array(arr) AS (
VALUES ('["name":"Mickey Mouse","age":10,"name":"Donald Duck","age":5]'::jsonb)
)
, sample_data_elements(elem) AS (
SELECT jsonb_array_elements(arr) FROM sample_data_array
)
SELECT elem->'name' AS extracted_name FROM sample_data_elements;
En este ejemplo, sample_data_elements es equivalente a una mesa con un solo jsonb columna llamada elemcon dos filas (las dos array elementos en los datos iniciales).
El resultado consta de dos filas (una jsonb columna, o de tipo text si usaste ->>'name' en lugar de):
extracted_name
----------------
"Mickey Mouse"
"Donald Duck"
(2 rows)
Debería poder agruparlos y agregarlos como de costumbre para devolver el recuento de nombres individuales.
Hazlo así:
SELECT * FROM json_test WHERE (column_name @> '["name": "Mickey Mouse"]');