时间戳

首页 > 解决方案 > 从组中获取 JSONB 中冲突值的 SQL 查询

sql - 从组中获取 JSONB 中冲突值的 SQL 查询

问题描述

我有一个定义类似于下面的表。location_id是另一个表的 FK。报告以 N+1 的方式保存：对于一个位置，有 N 个报告者可用，如果您愿意，可以使用一份报告作为事实来源。来自记者的报道有一个单字母代码（比方说R），真相来源有不同的代码（比方说T）。JSONB 列的键是常规字符串，值是字符串、整数和整数数组的任意组合。

create table report (
  id integer not null primary key,
  location_id integer not null,
  report_type char(1),
  data jsonb
)

鉴于上述所有信息，我如何获取所有位置 ID，其中data给定键集的值（在查询时提供）对于report_type R?

标签： sqlpostgresqljsonb

解决方案

至少有两种可靠的方法，具体取决于您想要获得的复杂程度以及密钥的数量和/或动态。第一个非常简单：

select location_id
from report
where report_type = 'R'
group by location_id
having count(distinct data->'key1') > 1 
  or count(distinct data->'key2') > 1
  or count(distinct data->'key3') > 1

第二种构造更复杂，但具有提供非常简单的键列表的优点：

--note that we also need distinct on location id to return one row per location
select distinct on(location_id) location_id
 --jsonb_each returns the key, value pairs with value in type JSON (or JSONB) so the value field can handle integers, text, arrays, etc 
from report, jsonb_each(data)
where report_type = 'R'
 and key in('key1', 'key2', 'key3')
group by location_id, key
having count(distinct value) > 1
order by location_id

推荐阅读