CODEWITHSUNDEEP
sqljsonpostgresqlpostgresql-13
Suraj
Suraj

Reputation: 2477

Aggregating values of particular key in json array - PostgreSQL

I have a table of the similar format :

chat_id |                                                                                                                                         agent_details                                                   
---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 chat_1  | [{"agentId": "agent01", "transferFromAgentName": "e2eagent01"}, {"agentId": "nemo-user", "transferFromAgentName": "N/A"}, {"agentId": "salesdemo-nemo-user-e2eagent01", "transferFromAgentName": "e2eagent05"}, {"agentId": "salesdemo-nemo-user-e2eagent01", "transferFromAgentName": "N/A"}]
 chat_1  | [{"agentId": "agent01", "transferFromAgentName": "agent5"}, {"agentId": "nemo-user", "transferFromAgentName": "agent6"}, {"agentId": "salesdemo-nemo-user-e2eagent01", "transferFromAgentName": "N/A"}]

I need to extract all the transferFromAgentName associated with each chat_id in a GROUP BY statement.

I tried the following query, but I'm able to read only the first transferFromAgentName from the agent_details column.

select 
chat_id, 
array_remove(ARRAY_AGG(DISTINCT agent_details::json->0  ->> 'transferFromAgentName'), 'N/A') 
FROM 
temp.chatsession 
GROUP BY chat_id;

which gives the following output :

chat_id |    array_remove
---------+---------------------
 chat_1  | {agent5,e2eagent01}

I require all transferFromAgentName to be present in the second column, i.e. {e2eagent01,e2eagent05,agent5,agent6}

CREATE AND INSERT QUERIES :

CREATE TABLE chatsession (
  chat_id varchar(20),
  agent_details JSONB
);

INSERT INTO chatsession
VALUES ('chat_1', '[
    {
        "agentId": "agent01",
        "transferFromAgentName": "e2eagent01"
    },
    {
        "agentId": "nemo-user",
        "transferFromAgentName": "N/A"
    },
    {
        "agentId": "salesdemo-nemo-user-e2eagent01",
        "transferFromAgentName": "e2eagent05"
    },
    {
        "agentId": "salesdemo-nemo-user-e2eagent01",
        "transferFromAgentName": "N/A"
    }
]'),
('chat_1', '[
    {
        "agentId": "agent01",
        "transferFromAgentName": "agent5"
    },
    {
        "agentId": "nemo-user",
        "transferFromAgentName": "agent6"
    },
    {
        "agentId": "salesdemo-nemo-user-e2eagent01",
        "transferFromAgentName": "N/A"
    }
]');

Upvotes: 0

Views: 682

Answers (1)

user330315
user330315

Reputation:

You need to unnest the array before you can aggregate the elements from different rows.

select c.chat_id, 
       array_agg(d.item ->> 'transferFromAgentName')
from chatsession c
  cross join jsonb_array_elements(c.agent_details) as d(item)
where d.item ->> 'transferFromAgentName' <> 'N/A'  
group by c.chat_id

Upvotes: 3

Related Questions