SQL how to get select attribute that is array by join?

Question

I am using postgresql. How to get from first table to second? Thank you

 id  | type | sum         
-----+------+-----
 1   | a    | 100
 2   | a    | 200
 3   | b    | 500


 t_sum | type | history          
-------+------+---------------
 300   | a    | ['id' => 1, 'sum' => 100], ['id' => 2, 'sum' => 200]
 500   | b    | ['id' => 3, 'sum' => 500]

I tried this, no results:

SELECT DISTINCT(a.type), SUM(a.sum) as t_sum, b.* as history FROM mytable a LEFT JOIN mytable b ON a.id = b.id GROUP BY a.type

DISTINCT is not a function, it's a part of SELECT DISTINCT - and works in the whole selected rows. Not needed anyway since the GROUP BY returns no duplicates. — jarlh
– jarlh, Commented Oct 10, 2016 at 9:40

Laurenz Albe · Accepted Answer · 2016-10-10 09:51:38Z

3

The following solution returns the last column as a json array:

SELECT sum(sum) AS t_sum,
       type,
       array_to_json(
          array_agg(
             json_build_object('id', id, 'sum', sum)
          )
       ) history
FROM history
GROUP BY type
ORDER BY type;

┌───────┬──────┬───────────────────────────────────────────────────┐
│ t_sum │ type │                      history                      │
├───────┼──────┼───────────────────────────────────────────────────┤
│   300 │ a    │ [{"id" : 1, "sum" : 100},{"id" : 2, "sum" : 200}] │
│   500 │ b    │ [{"id" : 3, "sum" : 500}]                         │
└───────┴──────┴───────────────────────────────────────────────────┘
(2 rows)

answered Oct 10, 2016 at 9:51

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Tim Biegeleisen Over a year ago

Brilliant answer, I just checked this on Postgres and it works.

sirjay Over a year ago

Thank you. Why simple array not working, only json format? I am using php.

Laurenz Albe Over a year ago

Sure, you can use a simple array, this is just an example. But what data type should ['id' => 1, 'sum' => 100] be in that case?

sirjay Over a year ago

Honestly, I don't know what datatypes Postgresql returns. I guess simple array like Array([1] => ['id' => 1, 'sum' => 100], [2] => ['id' => 2, 'sum' => 200])

Laurenz Albe Over a year ago

Yes, but what is the ['id' => 1, 'sum' => 100]? A string? A hstore value? I decided to go for JSON, but if you tell me what you need, I can adapt the answer.

|

Collectives™ on Stack Overflow

SQL how to get select attribute that is array by join?

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related