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
But below query
select
id,
name,
array_agg(skill ->> 'description') as skill
from table1, json_array_elements(skills) AS skill
group by 1, 2
is missing third row.
How to include third row having empty json([]
)? to get desired output