PostgreSQL 15.4
Below is the postgres table table1:
CREATE TABLE table1 (
id INT PRIMARY KEY,
name TEXT,
skills JSON
);
with below rows inserted:
INSERT INTO table1 (id, name, skills) VALUES
(1, 'Alice', ‘[
{“sid" : 11, "description" : “Cardio"},
{"sid" : 13, "description" : “Anesthist"}
]'
),
(2, ‘Bob', ‘[
{“sid" : 10, "description" : “Gastro"},
{"sid" : 9, "description" : “Oncology"}
]’
),
(3, ‘Sam', ‘[
]’
);
Below is the desired output, upon running select query:
id name skill
---------------------
1 Alice [“Cardio”,“Anestisht”]
2 Bob ["Gastro","Oncology"]
3 Sam []
where skill column is TEXT type
Tried below query
select
id, name, d ->> 'description' as skill
from table1,
json_array_elements(skills) as d
that gives duplicate rows(as shown below) with a missing row, which is incorrect
id name skill
---------------------
1 Alice Cardio
1 Alice Anestisht
2 Bob Gastro
2 Bob Oncology
Use array_agg() to aggregate the descriptions into an array:
select
id,
name,
array_agg(skill ->> 'description') as skill
from table1, json_array_elements(skills) AS skill
group by 1, 2
See live demo.
To return rows for people with no skills as well, here's one way:
id,
name,
array_agg(skill ->> 'description')
from table1, json_array_elements(skills) AS skill
group by 1, 2
union all
select
id,
name,
array[]::text[]
from table1
where json_array_length(skills) = 0
See live demo.