PostgreSQL 12.x中提取符合条件的JSONB列唯一键

我使用PostgreSQL 12.x数据库，表typename中存在一个存储jsonb类型数据的data列，该列的JSON结构不固定，示例数据如下：

{"emt": {"key": " ", "source": "INPUT"}, "id": 1, "fields": {}}

{"emt": {"key": "Stack Overflow", "source": "INPUT"}, "id": 2, "fields": {}}

{"emt": {"key": "https://www.domain.tld/index.html", "source": "INPUT"}, "description": {"key": "JSONB datatype", "source": "INPUT"}, "overlay": {"id": 5, "source": "bOv"}, "fields": {"id": 1, "description": "Themed", "recs ": "1"}}

需求 #

需要提取满足以下条件的JSON键：

• 键对应的对象仅包含key和source两个元素；
• source元素的值必须为"INPUT"。

针对上述示例，预期结果应为：emt、description。

尝试的SQL（未达预期） #

select distinct jsonb_object_keys(data) as keys
from typename
where jsonb_path_exists(data, '$.** ? (@.type() == "string" && @ like_regex "INPUT")');
-- where jsonb_typeof(data -> ???) = 'object'
--   and jsonb_path_exists(data, '$.???.key ? (@.type() == "string")')
--   and jsonb_path_exists(data, '$.???.source ? (@.type() == "string" && @ like_regex "INPUT")');

解决方案 #

要实现需求，需先将JSONB的键值对拆分为行，再逐个验证每个键对应的对象是否符合条件。以下是可行的SQL语句：

SELECT DISTINCT j.key AS target_keys
FROM typename,
     jsonb_each(data) j
WHERE jsonb_typeof(j.value) = 'object'
  AND j.value ?& ARRAY['key', 'source']
  AND cardinality(jsonb_object_keys(j.value)) = 2
  AND (j.value ->> 'source') = 'INPUT';

条件说明

• jsonb_each(data)：将data列的JSONB对象拆分为键（j.key）和对应值（j.value）的行数据；
• jsonb_typeof(j.value) = 'object'：确保当前值是JSON对象类型；
• j.value ?& ARRAY['key', 'source']：检查对象同时包含key和source两个键；
• cardinality(jsonb_object_keys(j.value)) = 2：限制对象仅包含两个键（结合上一条件，即仅包含key和source）；
• (j.value ->> 'source') = 'INPUT'：验证source字段的取值为"INPUT"。

执行该语句后，会从示例数据中得到预期的emt和description两个键。

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