PostgreSQL：在JSON数组中过滤

Question

假设我们有一张表 items，它有两列 name 和 attributes：

CREATE TABLE students (
  name VARCHAR(100),
  attributes JSON
)

其中attributes是一个包含一系列（始终具有相同结构）JSON文档的数组，例如：

[{"name":"Attribute 1","value":"Value 1"},{"name":"Attribute 2","value":"Value 2"}]

我现在想查找所有学生，其中任何属性值都与某些内容匹配（例如Foo%）。这里是一个示例。

我意识到这不是最直截了当的设计，但现在这是我要处理的东西，虽然这样搜索的性能显然非常低效，这可能是一个有效的问题。

- Ingo Bürk

---

json[] 没有意义。最好在 json 列中存储一个“真正”的 JSON 数组。 - user330315

@a_horse_with_no_name 那也可以。我会将问题改成那样。 - Ingo Bürk

Answer 1

您可以使用json_array_elements函数来访问元素，然后使用->> json运算符根据某个值进行搜索。

select s.*,j from 
  students  s 
   cross join lateral json_array_elements ( attributes ) as j
WHERE j->>'value' like 'Foo%'

演示

编辑

现在的问题是交叉连接将“复制”行。 有没有更好的方法来避免这种情况

使用 WITH ORDINALITY 为每个元素生成 ID，然后使用 DISTINCT ON 获取每个学生的第一/最后匹配项。

select DISTINCT ON (name) s.*,j.attr from 
students  s 
cross join lateral json_array_elements ( attributes ) WITH ORDINALITY as j(attr,id)
WHERE j.attr->>'value' like 'Value%'
ORDER BY name,j.id

演示2

- Kaushik Nayak

---

谢谢！交叉连接侧是我所缺少的（需要查找）。 - Ingo Bürk

现在的问题是交叉连接会“复制”行。除了选择一个唯一的ID列表并将其放入外部查询中，然后在其中选择具有这些ID的实际行，还有更好的避免方法吗？ - Ingo Bürk

@IngoBürk：不确定您所说的重复是什么，数组中每个JSON元素只有一行。您可以使用基于某些“order by”的DISTINCT ON来将它们限制为每个匹配项的最高/最低值。您应该提出另一个问题并提供详细信息。 - Kaushik Nayak

是的，它会针对数组中的每个元素返回一行，但这不是预期的输出；预期的输出是匹配任何元素的学生（即每个学生仅出现一次）。我对匹配的JSON元素不太感兴趣，只要至少有一个匹配即可。我认为这部分问题是我的问题陈述方式。 - Ingo Bürk

@IngoBürk ：那么，你可以使用GROUP BY / DISTINCT ON根据顺序获取聚合/顶部元素。请参阅我的其他答案。这里 或者 这个 - Kaushik Nayak

Answer 2

您可以使用EXISTS函数来过滤子JSON数据。

SELECT s.* FROM
students s 
WHERE EXISTS(
SELECT 1 FROM json_array_elements ( attributes ) as att WHERE att ->> 'value' ILIKE 'Foo%')

- Chhunly Lim

---

在这种情况下，您不会过滤JSON数组“attributes”的内容。 - Chevelle

哦，好的，我明白了。我以为过滤JSON数组是不必要的。 - Chhunly Lim

Answer 3

我为了在 Laravel 中更简洁地使用 WHERE 子句而进行了很多搜索，但是没有找到合适的解决方案，所以我想出了以下思路。

SELECT * FROM students where lower(attributes::text) ilike '%"<key>"%"<value>"%'

如果你正确解析JSON，那么键和值都会被存储在双引号（"）中。

请注意，我添加了双引号（"）到键和值，以便与确切的键和值匹配。

- Sayed Ajmal