sql - json字段上的PosgtreSQL连接表

Question

假设我们有 2 张桌子-

员工：

• id integer(PK)
• name char
• code char

状态：

• id integer(PK)
• key char
• data 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 复杂查询方面的专家，因此非常感谢任何帮助。

注意：以上只是示例表（名称和数据已更改），但设计与原始表相似。

标签： sqlpostgresqljsonb

Answer 1

在这些表之间使用左连接查询，并为类型列应用jsonb_each_text()函数。jsonb

诀窍是对case when (js).value = 'false' then .. else .. end聚合列使用条件：

select e.id, e.code, 
       min(case when (js).value = 'false' then 'FALSE' else 'TRUE' end ) as status,
       count(case when (js).value = 'false' then 1 end) as failures,
       coalesce(
         string_agg(case when (js).value = 'false' then s.key end, ',' ORDER BY s.id),'NA'
       ) as key
  from Employee e
  left join
  (
   select *, jsonb_each_text(data) as js
     from Status
  ) s on e.code = (js).key
 group by e.id, e.code
 order by e.id;

从类型列中(js).value提取的位置jsonbStatus.data

Demo