Aggregating values of particular key in json array - PostgreSQL

Question

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"
    }
]');

SELECT version() returns PostgreSQL 13.3, compiled by Visual C++ build 1914, 64-bit — Suraj
– Suraj, Commented Jul 15, 2021 at 8:20
So you want {e2eagent01,N/A,e2eagent05,N/A,agent5,agent6,N/A} as the result? — user330315
– user330315, Commented Jul 15, 2021 at 8:22
Yes, the same with all the N/A removed. So {e2eagent01,e2eagent05,agent5,agent6} — Suraj
– Suraj, Commented Jul 15, 2021 at 8:23

user330315 · Accepted Answer · 2021-07-15 08:25:02Z

3

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

answered Jul 15, 2021 at 8:25

user330315

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

Collectives™ on Stack Overflow

Aggregating values of particular key in json array - PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related