¿Como puedo hacer un sql que contenga un array de objetos y que esos objetos contengan 2 datos de columnas?

publicado por: Anonymous

Llevo este sql

El resultado de las 2 columnas modelos_id y modelos_nombre las quisiera unir dentro de objetos, lo intento hacer como dice la documentación.

Acá lo que intento hacer, la consulta es la siguiente:

SELECT mar.nombre AS marcas, json_object(array_agg(mod.id), array_agg(mod.nombre)) as modelos
FROM marcas AS mar
INNER JOIN modelos AS mod ON (mar.id=mod.marcas_id)
GROUP BY mar.nombre
ORDER BY mar.nombre

Pero este me retorna:

ERROR: no existe la función json_object(integer[], character varying[])

solución

Devolviendo un object jsonb

Si lo que quieres es respetar el GROUP BY con mar.nombre y en un solo campo tener un objeto con el id como clave y el nombre como valor se puede usar la función jsonb_object_agg:

SELECT mar.nombre AS marcas, 
    jsonb_object_agg(mod.id,mod.nombre) as modelos_nombre
FROM marcas AS mar
INNER JOIN modelos AS mod ON (mar.id=mod.marcas_id)
GROUP BY mar.nombre
ORDER BY mar.nombre

Esto obtiene como resultado

LG       {"1":"LG-001","2":"LG-002","3":"LG-003"}
Samsung  {"4":"Sam-004","5":"Sam-005","6":"Sam-006"}
Sonyview {"7":"Sony-007","8":"Sony-008","9":"Sony-009"}

Devolviendo un array json

Si lo que quieres es respetar el GROUP BY con mar.nombre y en un solo campo tener la lista (o array) de id y nombre de marca se puede usar la función json_agg para la función de agregado y json_build_object para crear el elemento individual:

SELECT mar.nombre AS marcas, 
    json_agg(json_build_object('id',mod.id,'mod',mod.nombre)) as modelos_nombre
FROM marcas AS mar
INNER JOIN modelos AS mod ON (mar.id=mod.marcas_id)
GROUP BY mar.nombre
ORDER BY mar.nombre

dinstintas posibilidades en json_build_object te van dando distintos resultados. Puedes probar la que desees. Con esta el resultado es:

LG       [{"id":1,"mod":"LG-001"},{"id":2,"mod":"LG-002"},{"id":3,"mod":"LG-003"}]
Samsung  [{"id":4,"mod":"Sam-004"},{"id":5,"mod":"Sam-005"},{"id":6,"mod":"Sam-006"}]
Sonyview [{"id":7,"mod":"Sony-007"},{"id":8,"mod":"Sony-008"},{"id":9,"mod":"Sony-009"}]

puedes probar con json_build_object(mod.id::text,mod.nombre) para que el id sea la key del par y la marca el valor.

json vs jsonb

A los efectos de este problema da lo mismo, se puede usar array u object en ambas variantes. Recomiendo jsonb, pero ese es otro tema.

Devolviendo un ARRAY puro de registros

Lo siguiente devuelve un array de array, otra vez acá se puede poner dentro del array_agg lo que desees para que el elemento interno sea del tipo que necesites.

SELECT mar.nombre AS marcas, 
    array_agg(array[mod.id::text,mod.nombre]) as modelos_nombre
FROM marcas AS mar
INNER JOIN modelos AS mod ON (mar.id=mod.marcas_id)
GROUP BY mar.nombre
ORDER BY mar.nombre

¿Qué se puede meter dentro de array_agg?

Alternativas para meter dentro de array_agg. El problema de meter un array dentro es que hay que pasar todo al mismo tipo, si se quieren respetar los tipos una buena alternativa es usar row:

  • row(mod.id,mod.nombre) que devuelve una registro (tupla) respetando los tipos

Cualquier cosa que devuelva un único valor se puede meter dentro de array_agg, por ejemplo cualquier función que devuelva un json (lo cual no tendría mucho sentido, porque sería mezclar json dentro de array comunes).

Respondido por: Anonymous

Leave a Reply

Your email address will not be published. Required fields are marked *