getting duplicate values in jsonb query posgresql

Question

In table I have used jsonb to store multiple values in json array. now i want to write a query where day is monday. [{'day':'monday','time':"8 am"},{'day':'tuesday','time':"8 am"}{'day':'monday','time':"11 am"},{'day':'friday','time':"8 am"}]

Query:

SELECT  array_to_json(array_agg(j))
FROM demo  t, jsonb_array_elements(t.di_item ) j
WHERE j->>'day' = 'monday'

Result:

[{'day':'monday','time':"8 am"},{'day':'monday','time':"11 am"},{'day':'monday','time':"8 
am"},{'day':'monday','time':"11 am"}]

Expected:

[{'day':'monday','time':"8 am"},{'day':'monday','time':"11 am"}]

One value getting two times.

Answer 1

First: no need to aggregate the json objects as an array, and then convert it to a json array, you can use json[b]_agg() directly. Then: use distinct to avoid duplicates.

SELECT  jsonb_agg(distinct j)
FROM demo  t
CROSS JOIN LATERAL jsonb_array_elements(t.di_item) j
WHERE j->>'day' = 'monday'