PostgreSQL中如何通过JSON键关联两张数据表？

现有两张数据表：

CREATE TABLE json_data_table(d_id int, d1_json json, d2_json json);
CREATE TABLE user_data(uid int, username varchar);

对应的数据如下：

INSERT INTO user_data VALUES
(1,'test_user_1'),
(2,'test_user_2'),
(3,'test_user_3');

INSERT INTO json_data_table VALUES 
  (1,'{"stage1":1,"stage2":2 }', '{
    "stage1": {
        "date": "12-01-2023",
        "status": "open",
        "uid": "2"
    },
    "stage2": {
        "date": "22-01-2023",
        "status": "close",
        "uid": "1"
    }
}'),
  (2,'{"stage1":11,"stage2":21 }', '{
    "stage1": {
        "date": "21-2-2023",
        "status": "open",
        "uid": "3"
    },
    "stage2": {
        "date": "2-2-2023",
        "status": "close",
        "uid": "2"
    }
}');

需要通过uid将user_data表与json_data_table关联，获取对应username字段。目前已能通过json->>key_name获取其他字段，现有查询语句如下：

SELECT d1_json->>'stage1' as stage1, 
       d1_json->>'stage2' as stage2, 
       d2_json->'stage1'->>'date' as s1_date, 
       d2_json->'stage1'->>'status' as s1_status, 
       d2_json->'stage1'->>'uid' as s1_uid,  
       d2_json->'stage2'->>'date' as s2_date, 
       d2_json->'stage2'->>'status' as s2_status, 
       d2_json->'stage2'->>'uid' as s2_uid 
FROM json_data_table AS jd;

求编写关联查询以得到包含username的预期输出表。

由于json_data_table的d2_json字段中，stage1和stage2各自对应一个uid，需要分别关联user_data表来获取对应的用户名。可以通过两次左连接（或内连接，根据实际需求）user_data表，并使用别名区分两次关联的表：

SELECT 
    d1_json->>'stage1' as stage1, 
    d1_json->>'stage2' as stage2, 
    d2_json->'stage1'->>'date' as s1_date, 
    d2_json->'stage1'->>'status' as s1_status, 
    d2_json->'stage1'->>'uid' as s1_uid,
    u1.username as s1_username, -- stage1对应的用户名
    d2_json->'stage2'->>'date' as s2_date, 
    d2_json->'stage2'->>'status' as s2_status, 
    d2_json->'stage2'->>'uid' as s2_uid,
    u2.username as s2_username  -- stage2对应的用户名
FROM json_data_table AS jd
LEFT JOIN user_data u1 ON CAST(d2_json->'stage1'->>'uid' AS INT) = u1.uid
LEFT JOIN user_data u2 ON CAST(d2_json->'stage2'->>'uid' AS INT) = u2.uid;

说明 #

• 使用CAST(xxx AS INT)是因为JSON中存储的uid是字符串类型，而user_data表的uid是整数类型，需要统一类型才能正确关联。
• 采用LEFT JOIN可以保证即使JSON中的uid在user_data表中不存在，对应的记录也不会被过滤掉；如果只需要关联存在的记录，可以替换为INNER JOIN。

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