Think I'm in need of a cross lateral, been looking at examples but can't figure it out...

Since Casino has many "Deposit", I get multiple result rows returned, one per each related deposit.

I would like to only get 1 row/result per Casino and then all the Deposit/d."name" in json array format in a column.

I would like the result to look something like this:

Casino.id  |  Casino Name | [{ deposit1 }, {deposit 2}, deposit 3}]  etc...

Not:

Casino.id  |  Casino Name | deposit1
Casino.id  |  Casino Name | deposit2
Casino.id  |  Casino Name | deposit3

How?

SELECT casino.id
      , casino."name"
      , d."name"
FROM public."Casino" casino

left join "CategoryOnCasinos" coc 
on coc."casinoId" = casino.id 

left join "Category" category 
on category.id = coc."categoryId" 

left join "DepositsOnCasinos" doc 
on doc."casinoId" = casino.id 

left join "Deposit" d 
on d.id = doc."depositId" 

where category.slug = 'all-casinos';

CodePudding user response：

Try using the JSON_AGG function.

SELECT casino.id, casino."name", json_agg(d."name") as deposits
FROM public."Casino" casino
LEFT JOIN "CategoryOnCasinos" coc 
ON coc."casinoId" = casino.id 
LEFT JOIN "Category" category 
ON category.id = coc."categoryId" 
LEFT JOIN "DepositsOnCasinos" doc 
ON doc."casinoId" = casino.id 
LEFT JOIN "Deposit" d 
ON d.id = doc."depositId" 
WHERE category.slug = 'all-casinos'
GROUP BY casino.id, casino."name"

This should return one row per casino and a JSON array of deposits in the deposit column.

ref