PostgreSQL中如何查询表列与JSON数组元素同行展示

我有一张employees.employee_data表，表中每条记录包含emp_id（员工ID）和associations（JSONB类型字段，内部包含group_tags数组，数组元素是含orgId、locationId的对象）。需要编写SELECT语句，将每个员工ID与group_tags数组的每个元素展开为单独行，输出结果每行包含emp_id、orgId、locationId三个字段。

CREATE TABLE employees.employee_data (
    emp_id              varchar(50)  PRIMARY KEY,
    associations        JSONB NOT NULL default '{}'::jsonb
);

INSERT INTO employees.employee_data
(emp_id, associations)
VALUES('1001', '{
                              "group_tags": [
                                {
                                  "orgId": "XYZCompany",
                                  "locationId": "Location01"
                                },
                                {
                                  "orgId": "XYZCompany",
                                  "locationId": "Location02"
                                }                               
                              ]
                            }'::jsonb);
                            

INSERT INTO employees.employee_data
(emp_id, associations)
VALUES('1002', '{
                              "group_tags": [
                                {
                                  "orgId": "ABCCompany",
                                  "locationId": "Location03"
                                },
                                {
                                  "orgId": "ABCCompany",
                                  "locationId": "Location04"
                                }                               
                              ]
                            }'::jsonb);

利用PostgreSQL的jsonb_array_elements函数拆分JSONB数组，配合->>操作符提取对象字段值，SQL语句如下：

SELECT 
    emp_id,
    (tag->>'orgId') AS org_id,
    (tag->>'locationId') AS location_id
FROM 
    employees.employee_data,
    jsonb_array_elements(associations->'group_tags') AS tag;

说明 #

• jsonb_array_elements(associations->'group_tags') AS tag：把associations字段内的group_tags数组拆分为多行，每行对应数组中的一个对象，用tag作为该对象的别名。
• tag->>'orgId'：从拆分出的tag对象中提取orgId的字符串值，作为org_id列；同理提取locationId作为location_id列。

内容的提问来源于stack exchange，提问作者user203687