I am trying to write a Postgres query that will output my json data in a particular format.
Postgres table has a JSON column that has the below data structure:
{
"employee_data": {
"records": [
{
"comment": "group1",
"emp_file": {
"employees": [
"CNTA",
"CNTB",
"CNTC"
],
"number_of_employees": 3
}
},
{
"comment": "group2",
"emp_file": {
"employees": [
"CNTA",
"CNTC"
],
"number_of_employees": 2
}
}
]
}
}
output: I am trying to get the list of employees from the employees[]:
"CNTA"
"CNTB"
"CNTC"
"CNTA"
"CNTC"
I have been trying to get it with JSON_ARRAY_ELEMENTS but no luck so far.
Any help would be greatly appreciated.
tried JSON_ARRAY_ELEMENTS.
You can tackle this using a CTE and json_array_elements_text for extracting data. Try this code, it should work. I've tested it on my end.
CREATE TABLE employee_table (
id serial primary key,
employee_data jsonb
);
INSERT INTO employee_table (employee_data)
VALUES (
'{
"employee_data": {
"records": [
{
"comment": "group1",
"emp_file": {
"employees": [
"CNTA",
"CNTB",
"CNTC"
],
"number_of_employees": 3
}
},
{
"comment": "group2",
"emp_file": {
"employees": [
"CNTA",
"CNTC"
],
"number_of_employees": 2
}
}
]
}
}'
);
WITH data AS (
SELECT jsonb_array_elements(employee_data->'employee_data'->'records') AS record
FROM employee_table
)
SELECT jsonb_array_elements_text(record->'emp_file'->'employees') AS employee
FROM data;