postgresql 如何在Postgres中用json键连接2个表？

7cwmlq89 于 2023-03-12 发布在 PostgreSQL

关注(0)|答案(2)|浏览(168)

我有两张table

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"
    }
}');

现在，我要将user_data表与json_data_table和uid连接起来，以获得username

预期表格输出：

我通过简单地获取json->>key_name来获得其他列，这里是select查询。

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;

如何连接表以获得如图所示的预期表？

postgresql

来源：https://stackoverflow.com/questions/75693327/how-to-join-2-table-with-json-key-in-postgres

2条答案

按热度按时间

3ks5zfa01#

试试这个：

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, 
    s1_user.username as s1_username, 
  d2_json -> 'stage2' ->> 'date' as s2_date, 
  d2_json -> 'stage2' ->> 'status' as s2_status, 
  d2_json -> 'stage2' ->> 'uid' as s2_uid, 
  s2_user.username as s2_username 
FROM 
  json_data_table AS jd 
  JOIN user_data s2_user ON s2_user.uid :: TEXT = d2_json -> 'stage2' ->> 'uid' :: TEXT 
  JOIN user_data s1_user ON s1_user.uid :: TEXT = d2_json -> 'stage1' ->> 'uid' :: TEXT;

赞(0）回复(0）举报 2023-03-12

pxq42qpu2#

您只需连接它并使用表达式提取连接条件中的uid。由于需要不同的user_data集合，因此需要连接表两次。最后，由于uid是数字，而JSON由文本组成，因此必须在两者之间进行转换。我选择在JSON端进行转换。因为这使得能够使用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,  
       s1ud.username as s1_username,
       d2_json->'stage2'->>'date' as s2_date, 
       d2_json->'stage2'->>'status' as s2_status, 
       d2_json->'stage2'->>'uid' as s2_uid,  
       s2ud.username as s2_username 
FROM json_data_table AS jd
join user_data s1ud
on s1ud.uid = to_number( d2_json->'stage1'->>'uid', '999')
join user_data s2ud
on s2ud.uid = to_number( d2_json->'stage2'->>'uid', '999');

DB-Fiddle to play with .

赞(0）回复(0）举报 2023-03-12

我来回答

postgresql 如何在Postgres中用json键连接2个表？

2条答案

相关问题

热门标签

最新问答