I have table which contains specified columns:

id - bigint
decision - varchar(80)
type - varchar(258)

I want to make a select query which in result returns something like this(id, decisionsValues with counts as json, type):

id  decisions                                                                         type

1  {"firstDecisionsValue":countOfThisValue, "secondDecisionsValue": countOfThisValue} entryType

I heard that I can try play with json_agg but it does not allow COUNT method, tried to use json_agg with query:

SELECT ac.id,
       json_agg(ac.decision),
       ac.type
    FROM myTable ac
    GROUP BY ac.id, ac.type;

but ends with this(for entry with id 1 there are two occurences of firstDecisionsValue, one occurence of secondDecisionsValue):

id  decisions                                                             type

1  {"firstDecisionsValue", "firstDecisionsValue", "secondDecisionsValue"} entryType

minimal reproducible example

CREATE TABLE myTable
(
    id        bigint,
    decisions varchar(80),
    type      varchar(258)
);
INSERT INTO myTable
VALUES (1, 'firstDecisionsValue', 'myType');
INSERT INTO myTable
VALUES (1, 'firstDecisionsValue', 'myType');
INSERT INTO myTable
VALUES (1, 'secondDecisionsValue', 'myType');

Can you provide me any tips how to make it as expected?

1, {"fistDecisionsValue":2, "secondDecisionsValue":1}, entryType

CodePudding user response：

You can try this

SELECT a.id, jsonb_object_agg(a.decisions, a.count), a.type
  FROM 
     ( SELECT id, type, decisions, count(*) AS count
         FROM myTable
        GROUP BY id, type, decisions
     ) AS a
 GROUP BY a.id, a.type

see the result in dbfiddle.

CodePudding user response：

First, you should calculate the count of id, type, decisions for each decisions after that, you should use jsonb_object_agg to create JSON.

Demo

with data as (
select 
  ac.id,
  ac.type,
  ac.decisions,
  count(*)
from 
  myTable ac
 group by 
   ac.id, 
   ac.type,
   ac.decisions
)
select 
  d.id,
  d.type,
  json_object_agg(d.decisions, d.count)
from 
  data d
 group by 
   d.id, 
   d.type