I've made a fiddle to make things easier: https://www.db-fiddle.com/f/5ELU6xinJrXiQJ6u6VH5/3
I have a table of deals. A deal may have many properties. A deal or property may have many fields. I've written a query that aggregates all the properties, deal fields, and property fields and returns them in it's each row. I'd like each property field to be returned within the properties aggregate column.
Currently, there are errors in the result set and the structure is now how I want it.
For example, in the fiddle I've linked to above, the first row returns this in the properties column:
[{
"id": 1,
"deal_id": 1,
"address": "123 Fake Street"
}, {
"id": 1,
"deal_id": 1,
"address": "123 Fake Street"
}]
It should return this:
[{
"id": 1,
"deal_id": 1,
"address": "123 Fake Street"
}, {
"id": 2,
"deal_id": 1,
"address": "456 Fake Street"
}]
In addition to returning the correct result set, I'd like for the property fields to be returned nested within the result set. Something like so:
[
{
"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"
}
]
}
]
I'm stuck and would appreciate any help
You have to do some nesting to achieve this:
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;
Notes:
1) You have to add PK to your id columns. If so, you don't need to group by all columns in table, like: group by deals.id, deals.name, just group by deals.id; Same for nested properties table.
2) You can use json_agg instead of array functions
3) You probably have to create composite btree index on fields table on two columns: parent+parent_id.
4) Rule of thumb: you have to have as much "WITH" subqueries as much deep is your result set.