example - No se pudo identificar el operador de igualdad de tipo json[] cuando se usa UNION

postgresql jsonb example

Question

Estoy tratando de realizar múltiples consultas en una sola tabla usando una regla UNION

Tengo dos mesas:

• proyecto (id, nombre, pineado BOOLEAN)
• Habilidades (m2m a proyectos)

Estoy buscando primero obtener una serie de filas que se hayan pinned en true y rellenar las restantes con las últimas entradas ( pinned en false )

SELECT
project.id AS project_id,
project.name AS project_name,
array_agg(json_build_object(''skill_id'', project_skills.id,''name'', project_skills.skill)) AS skills
from project
LEFT OUTER JOIN project_skills on project.name = project_skills.project
WHERE project.pinned = true
GROUP BY project_id,project_name
UNION
SELECT
project.id AS project_id,
project.name AS project_name,
array_agg(json_build_object(''skill_id'', project_skills.id,''name'', project_skills.skill)) AS skills
from project
LEFT OUTER JOIN project_skills on project.name = project_skills.project
WHERE project.id != 1 AND project.pinned = false
GROUP BY project_id,project_name
ORDER BY project.create_date DESC LIMIT 5

Al realizar esta consulta, me sale el siguiente error

ERROR: could not identify an equality operator for type json[]
LINE 7: array_agg(json_build_object(''skill_id'', project_skills.id,...

No entiendo este error. ¿Está fallando porque está tratando de comparar las columnas json de ambos resultados?

Estoy usando Postgres 9.4.

Answer 1

Cuando usa un UNION , el DBMS elimina cualquier fila duplicada, y para hacerlo necesita identificar si dos filas son iguales o idénticas. Esto a su vez significa mirar cada columna de las dos filas que está comparando y decidir si son iguales.

El mensaje de error que está viendo es donde se construye una de sus columnas usando array_agg(json_build_object(...)) que produce un valor de tipo json[] , que significa "matriz de valores json". Debido a que Postgres no sabe cómo comparar dos matrices de valores JSON, no puede decidir si su UNION produjo duplicados.

Si realmente no le importa eliminar los duplicados, la solución más sencilla es usar UNION ALL que omite este paso.

Como se señaló en los comentarios, si desea eliminar duplicados, puede convertir los valores en algo que tenga un operador de comparación definido. La solución más general es convertir a texto (por ejemplo, some_value::text o CAST(some_value as text) ) pero para JSON específicamente, probablemente desee el tipo jsonb , que ignorará el formato al comparar.

Podría lanzar json a jsonb , o json[] a jsonb[] , o en este ejemplo podría compilar jsonb directamente con array_agg(jsonb_build_object(...)) lugar de array_agg(json_build_object(...)) .

Answer 2

Resulta que todo lo que tenía que hacer era usar UNION ALL - supongo que esto ignora intentar comparar los tipos de json entre las consultas.