Looking for Postgres query which can provide output like MongoDB group by function-CodePudding

Product table

|_id|name  |
|---|------|
|3  |Laptop|

Size table

|_id|product_id|size|
|---|----------|----|
|5  |3         |15  |
|6  |3         |17  |

Query:

select tp._id, tp.name, ts.size from test_product tp 
  left join test_size ts on tp._id = ts.product_id 
  group by tp._id, tp.name, ts.size
where tp._id = 3 limit 10 offset 0

Current output:

|_id|name  |size|
|---|------|----|
|3  |Laptop|15  |
|3  |Laptop|17  |

Expected output

|_id|name  |size   |
|---|------|-------|
|3  |Laptop|[15,17]|

Note: Due to current query I'm getting 2 record for the same product and my limit and offset query logic is getting false and not getting proper count. I'm not well aware of Postgres queries for this kind of situation. So I need solution for this so my limit and offset logic will be correct for fetching data and for this query my count of product will be 1.

CodePudding user response：

Use array_agg():

SELECT
    tp._id,
    tp.name,
    ARRAY_AGG(ts.size ORDER BY ts.size) -- ORDER BY to get consistent results
FROM
    test_product tp
    LEFT JOIN test_size ts ON tp._id = ts.product_id 
GROUP BY
    tp._id,
    tp.name 
WHERE
    tp._id = 3 
LIMIT 10 
OFFSET 0;

The ORDER BY within the aggregation is optional, but it's always nice to get consistent results over and over again.