jsonpostgresqlnested-jsonjson-flattener

PostgreSQL - Flatten nested JSON structure


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.


Solution

  • 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;