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]
]
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"]
]
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 ;
regular jsonthough it is valid. AFAIK jsob function will not give any output without quotes.