PostgreSQL - Flatten nested JSON structure

Question

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.

Answer 1

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;