sql - PostgreSQL 在 json 字段上连接表

Question

假设我们有 2 个表-

员工:

• id 整数 (pk)
• 名称字符
• 代码字符

状态:

• id 整数 (pk)
• 键字符
• 数据jsnob

下面是上面表格的示例数据:

员工

+----+--------+------+
| id |  name  | code |
+----+--------+------+
|  1 | Brian  | BR1  |
|  2 | Andrew | AN1  |
|  3 | Anil   | AN2  |
|  4 | Kethi  | KE1  |
|  5 | Smith  | SM1  |
+----+--------+------+

状态

+----+---------+---------------------------------------+
| id |   key   |                 data                  |
+----+---------+---------------------------------------+
|  1 | Admin   | {'BR1':true, 'AN1':true,'KE1':false}  |
|  2 | Staff   | {'SM1':true, 'AN2':true,'KE1':false}  |
|  3 | Member  | {'AN2':false, 'AN1':true,'KE1':false} |
|  4 | Parking | {'BR1':true, 'AN1':true,'KE1':false,  |
|    |         | 'AN2':true,'SM1':true}                |
|  5 | System  | {'AN2':false, 'AN1':true,'KE1':true}  |
|  6 | Ticket  | {'AN2':false, 'AN1':true,'KE1':false}  |
+----+---------+---------------------------------------+

现在我的目标是根据员工代码获取故障键的状态和名称。例如:-

我不是 sql 复杂查询方面的专家，所以非常感谢任何帮助。

注意:以上仅为示例表格(名称和数据已更改)，但设计与原始表格相似。

Answer 1

您可以使用函数 jsonb_each_text 从 jsonb 类型获取键和值，如果我们混合使用 sql 和...瞧瞧，下面的查询是您的示例:

select employee.code,
case 
when dat2.count is null then 'TRUE'
else
'FALSE'
end as status, 
case 
when dat2.count is null then 0
else
dat2.count 
end as failures, string_agg as key  from employee left join 
(
select key, count(*), string_agg(code,',')  from (
select key code , (jsonb_each_text(data)).key,(jsonb_each_text(data)).value 
from status) as dat 
where value='false'
group by 1 ) dat2 on  (employee.code=dat2.key)