Hice un violín para facilitar las cosas: https://www.db-fiddle.com/f/5ELU6xinJrXiQJ6u6VH5/3
Tengo una mesa de ofertas. Un trato puede tener muchas propiedades. Un trato o propiedad puede tener muchos campos. Escribí una consulta que agrega todas las propiedades, campos de trato y campos de propiedad y los devuelve en cada fila. Me gustaría que cada campo de propiedad se devuelva dentro de la columna agregada de propiedades.
Actualmente, hay errores en el conjunto de resultados y la estructura ahora es como la quiero.
Por ejemplo, en el violín al que he vinculado anteriormente, la primera fila devuelve esto en la columna de propiedades:
[{ "id": 1, "deal_id": 1, "address": "123 Fake Street" }, { "id": 1, "deal_id": 1, "address": "123 Fake Street" }]Debería devolver esto:
[{ "id": 1, "deal_id": 1, "address": "123 Fake Street" }, { "id": 2, "deal_id": 1, "address": "456 Fake Street" }]Además de devolver el conjunto de resultados correcto, me gustaría que los campos de propiedad se devolvieran anidados dentro del conjunto de resultados. Algo así:
[ { "id": 1, "deal_id": 1, "address": "123 Fake Street", "fields": [ { "id": 2, "parent": "property", "parent_id": 1, "key": "Square Feet", "value": "10,000" }, { "id": 3, "parent": "property", "parent_id": 1, "key": "Maximum Occupancy", "value": "150" } ] }, { "id": 2, "deal_id": 1, "address": "456 Fake Street", "fields": [ { "id": 4, "parent": "property", "parent_id": 1, "key": "Square Feet", "value": "12,000" }, { "id": 5, "parent": "property", "parent_id": 1, "key": "Maximum Occupancy", "value": "175" } ] } ]Estoy atascado y agradecería cualquier ayuda.
Tienes que hacer un poco de anidamiento para lograr esto:
with properties as ( select properties.*, json_agg(property_fields.*) as property_fields from properties left join fields as property_fields on property_fields.parent = 'property' and property_fields.parent_id = properties.id group by properties.id, properties.deal_id, properties.address ) select deals.*, json_agg(properties.*) as deal_properties, json_agg(deal_fields.*) as deal_fields from deals left join properties on deals.id = properties.deal_id left join fields deal_fields on deal_fields.parent = 'deal' and deal_fields.parent_id = deals.id group by deals.id, deals.name;Notas:
1) Debe agregar PK a sus columnas de identificación. Si es así, no necesita agrupar por todas las columnas en la tabla, como: agrupar por tratos.id, tratos.nombre, solo agrupar por tratos.id; Lo mismo para la tabla de propiedades anidadas.
2) Puede usar json_agg en lugar de funciones de matriz
3) Probablemente tenga que crear un índice btree compuesto en la tabla de campos en dos columnas: parent+parent_id.
4) Regla general: debe tener tantas subconsultas "CON" como la profundidad de su conjunto de resultados.