PostgreSQL 可以将 json 数组中的某一个值做为条件查询吗?

2019-10-11 16:11:56 +08:00
 imherer

如下字段: id,name,category,其中 category 类型是 jsonb 假如有如下 3 行数据:

想要的结果是:

where category=11 的时候返回 1001 这一行数据

where category=33 的时候返回 1001 和 1003 两行数据

6014 次点击
所在节点    PostgreSQL
16 条回复
javapythongo
2019-10-11 16:27:11 +08:00
可以的
imherer
2019-10-11 16:31:16 +08:00
@javapythongo 该怎么写上面的 sql 呢?

我现在用 jsonb_array_elements_text 函数实现了想要的结果了,但是不知道有没有什么问题
reus
2019-10-11 16:34:16 +08:00
where category && array[11]
randm
2019-10-11 16:37:57 +08:00
数据都没有 KEY 键,很难定位
https://www.postgresql.org/docs/12/functions-json.html
reus
2019-10-11 16:38:43 +08:00
看错了,json 要用 @>

select category @> '11'::jsonb
reus
2019-10-11 16:39:18 +08:00
@randm @> 可以用在 array 上
imherer
2019-10-11 16:39:31 +08:00
@reus 报语法错误 operator does not exist: jsonb && integer[]
reus
2019-10-11 16:40:40 +08:00
@imherer 看后面的,用 @>

&& 是用在数组类型的,不是 jsonb 类型
optional
2019-10-11 16:40:55 +08:00
category @> '22'::jsonb 这样。
如果是字符串数组就更简单 category ? '22'
imherer
2019-10-11 16:41:31 +08:00
@reus 感谢,这样写可以的。

我现在是这样写的,也能得到想要的结果,不知道有没有什么问题
SELECT * FROM table,jsonb_array_elements_text(category) c WHERE c =111;

@randm
reus
2019-10-11 16:44:58 +08:00
@imherer @> 加上索引,是效率最高的。你这个又要 join 又要转换成 text,可能会出现效率很低的执行计划。
randm
2019-10-11 16:51:49 +08:00
补上,有字段名,那应该可以的
WHERE category @> '[11]'::jsonb
imherer
2019-10-11 16:53:31 +08:00
@optional 我尝试把字段改成字符串数组后,报语法错误呢 operator does not exist: character varying[] ? unknown

改了之后的源数据是这样的

1001, name1, {11, 22, 33}
optional
2019-10-11 17:28:28 +08:00
@imherer text[] 用 @> array['11'] , 带 orderby 的时候 text[] 优于 jsonb array
imherer
2019-10-11 17:36:00 +08:00
@optional ?只能 jsonb 才能用把 好像?
encro
2020-01-02 16:33:58 +08:00
category 可以用 array[int]类型,然后加 Gist 索引

这是一个专为移动设备优化的页面(即为了让你能够在 Google 搜索结果里秒开这个页面),如果你希望参与 V2EX 社区的讨论,你可以继续到 V2EX 上打开本讨论主题的完整版本。

https://www.v2ex.com/t/608286

V2EX 是创意工作者们的社区,是一个分享自己正在做的有趣事物、交流想法,可以遇见新朋友甚至新机会的地方。

V2EX is a community of developers, designers and creative people.

© 2021 V2EX