Select as array of tuples postgresql

Question

Given table of enums

|id |reaction |
|-- |-------- |
|1  |laugh    |
|2  |love     |
|3  |love     |
|4  |like     |
|5  |like     |
|6  |surprised|
|7  |like     |
|8  |love     |
|9  |like     |
|10 |surprised|

How can I select it to get following JSON array of tuples [reaction, count()]?

[
   [laugh, 1], 
   [love, 3], 
   [like, 4], 
   [surprised, 2]
]

this [question](https://stackoverflow.com/questions/24006291/postgresql-return-result-set-as-json-array/24006432) maybe help you. — spike 王建, Oct 26 '20 at 02:56
@AkhileshMishra it is, why wouldn't it be? yes, no quotes, but array of tuples is valid json — Daniel, Oct 26 '20 at 12:48
I specifically said `regular json` though it is valid. AFAIK jsob function will not give any output without quotes. — Akhilesh Mishra, Oct 26 '20 at 13:50

score 2 · Accepted Answer · 2020-10-26T07:55:14.943

You can aggregate the result of a group by query:

select jsonb_agg(jsonb_build_object(reaction, count))
from (
  select reaction, count(*)
  from the_table
  group by reaction
) t;

This would return:

[
  {"surprised": 2}, 
  {"like": 4}, 
  {"laugh": 1}, 
  {"love": 3}
]

Or if you really want the inner key/value pairs as a JSON array:

select jsonb_agg(array[reaction, "count"])
from (
  select reaction, count(*)::text as "count"
  from the_table
  group by reaction
) t;

This would return

[
  ["surprised","2"],
  ["like","4"],
  ["laugh","1"],
  ["love","3"]
]

Online example

Puneet Kushwah · Answer 2 · 2020-10-26T07:31:48.593

0

You can make use of postgres over partition by and jsonb_build_array function:

SELECT
    jsonb_build_array(json_reactions.reaction, count)
FROM
    (
    SELECT
        DISTINCT reaction, count(*) OVER (PARTITION BY reaction)
    FROM
        reactions r ) AS json_reactions ;

edited Oct 26 '20 at 07:31

answered Oct 26 '20 at 03:32

Puneet Kushwah

1,495
2
17
35

@a_horse_with_no_name thanks, you are correct, updating answer! – Puneet Kushwah Oct 26 '20 at 07:31

Select as array of tuples postgresql

2 Answers2