代码之家 › 专栏 › 技术社区 › Ingo Bürk

postgresql:json数组中的过滤器

postgresql sql

Ingo Bürk · 技术社区 · 6 年前

假设我们有一张桌子 items 有柱子的 name 和 attributes :

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

其中,属性是一组(始终结构相同)JSON文档,例如

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

现在我要查找所有属性值与某个值匹配的学生(例如 Foo% ) Here's a playground example .

我意识到这并不是最直接的设计,但现在我必须要做的是,尽管这样一个搜索的性能明显非常低效当然是一个值得关注的问题。

1 回复 | 直到 6 年前

Kaushik Nayak 6 年前

您可以使用 json_array_elements 要访问元素,然后使用->>json运算符使用某个值进行搜索。

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

Demo

编辑

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

使用 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

Demo2

推荐文章

Ishaan Adarsh · 未知的META规范,无法验证。[规范v1.0.1]

1 年前

tggtsed · PostgreSQL从平均值中排除值

1 年前

Dawid · 为什么我不能在子查询中使用表别名?

2 年前

LAZ · ORDER BY id DESC LIMIT 100返回第一行而不是最后一行

2 年前

Mohammed Shaheer · 查找列中的数据是否满足sql中的数据类型条件

2 年前

AKang123. · Postgres:重复行,但更新json中的字段

2 年前

CraZyCoDer · 在PostgreSQL中锁定潜在事务

2 年前

ranebec · 计数时如何返回0而不是null?

2 年前

Ramón J Romero y Vigil · 将没有时区列的postgres时间戳转换为日期

2 年前

Flo · 分组依据中的SQL大小写

2 年前