postgresql：在 JSON 数组中过滤

假设我们有一张桌子items其中有列name and attributes:

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

其中，属性是一组（总是结构相同的）JSON 文档，例如

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

我现在想找到所有属性值与某些内容匹配的学生（例如Foo%). 这是一个游乐场的例子 https://www.db-fiddle.com/f/w9DRUoELG43Z4FdCXtsieo/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%'

Edit

现在的问题是交叉联接会“重复”行。是 有更好的方法来避免这种情况

use 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