有没有一种方法可以查询PostgreSQL中CTE返回的JSON对象数组

Question

我有一个PostgreSQL查询，它使用CTE，CTE中的SELECT使用json_agg((将数据聚合为json对象。有没有一种方法可以通过基于对象字段的值在数组中搜索特定对象来查询CTE的结果？

例如，假设CTE创建一个名为results的临时表。json_agg((中的值在一个名为owners的字段中可用，并且每个owner对象都有一个称为name的字段。我想从结果中选择*owner.name='John Smith'。我不知道如何编写下面的WHERE子句，以便检查所有者数组中每个对象的名称字段中的值。

WITH results AS (
-- some other fields here
(SELECT json_agg(owners)
FROM (
SELECT id, name, telephone, email
FROM owner
) owners   
) as owners
)
SELECT * 
FROM results 
WHERE owners->>'name' == 'John Smith'

Answer 1

要执行该查询，通常可以在将json数据转换为jsonb数据后使用jsonpath语言(请参阅此处和此处的手册(：

WITH results AS (
-- some other fields here
(SELECT json_agg(owners)
FROM (
SELECT id, name, telephone, email
FROM owner
) owners   
) as owners
)
SELECT * 
FROM results 
WHERE jsonb_path_exists(owners :: jsonb, '$[*] ? (@.name == "John Smith")')