Joining JSONB array objects to an attribute on the same object

Consider the following table:

CREATE TABLE organization_data(data JSONB);

And it has the following data:

INSERT INTO organization_data (data) VALUES ('[{"department_name": "sales", "employees": [{"employee_id": 1, "name": "Joe", "salary": 10000}, {"employee_id": 3, "name": "Linda", "salary": 30000}]}, {"department_name": "sales", "employees": [{"employee_id": 2, "name": "Mary", "salary": 12000}, {"employee_id": 4, "name": "Jack", "salary": 11000}]}]'::JSONB);

How can I write a single SQL query (without having to write a custom function) to produce the following results (in rows)

department_name, employee_id, name
--------------------------------------------------------
sales, 1, Joe
sales, 3, Linda
accounting, 2, Mary
accounting, 4, Jack

note: I don't care about the salary attribute, but it's OK if it is there.

I am using PostgreSQL 14.17

You seem to be looking for the jsonb_array_elements function that you can use in lateral queries to turn the (nested) array(s) of objects into multiple rows:

SELECT
  dep->>'department_name' AS department_name,
  (emp->>'employee_id')::int AS employee_id,
  emp->>'name' AS name
FROM
  organization_data,
  jsonb_array_elements(data) AS departments(dep),
  jsonb_array_elements(dep->'employees') AS employees(emp);

Alternatively, I recommend the jsonb_to_recordset function that simplifies type and name assignment a bit:

SELECT
  department_name,
  employee_id,
  name
FROM
  organization_data,
  jsonb_to_recordset(data) AS dep(department_name text, employees jsonb),
  jsonb_to_recordset(employees) AS emp(employee_id int, name text);