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

V2EX = way to explore

V2EX 是一个关于分享和探索的地方

For Existing Member Sign In

This topic created in 2394 days ago, the information mentioned may be changed or developed.

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

1001, name1, [11, 22, 33]
1002, name2, [44, 55, 66]
1003, name3, [33, 77, 88]

想要的结果是：

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

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

category

Where

字段

jsonb

16 replies • 2020-01-02 16:33:58 +08:00

javapythongo

Oct 11, 2019

可以的

imherer

Oct 11, 2019

@javapythongo 该怎么写上面的 sql 呢？

我现在用 jsonb_array_elements_text 函数实现了想要的结果了，但是不知道有没有什么问题

reus

Oct 11, 2019

where category && array[11]

randm

Oct 11, 2019

数据都没有 KEY 键，很难定位
https://www.postgresql.org/docs/12/functions-json.html

reus

Oct 11, 2019

看错了，json 要用 @>

select category @> '11'::jsonb

reus

Oct 11, 2019

@randm @> 可以用在 array 上

imherer

Oct 11, 2019

@reus 报语法错误 operator does not exist: jsonb && integer[]

reus

Oct 11, 2019

@imherer 看后面的，用 @>

&& 是用在数组类型的，不是 jsonb 类型

optional

Oct 11, 2019

category @> '22'::jsonb 这样。
如果是字符串数组就更简单 category ? '22'

imherer

Oct 11, 2019

@reus 感谢，这样写可以的。

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

@randm

reus

Oct 11, 2019

@imherer @> 加上索引，是效率最高的。你这个又要 join 又要转换成 text，可能会出现效率很低的执行计划。

randm

Oct 11, 2019

补上，有字段名，那应该可以的
WHERE category @> '[11]'::jsonb

imherer

Oct 11, 2019

@optional 我尝试把字段改成字符串数组后，报语法错误呢 operator does not exist: character varying[] ? unknown

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

1001, name1, {11, 22, 33}

optional

Oct 11, 2019

@imherer text[] 用 @> array['11'] ，带 orderby 的时候 text[] 优于 jsonb array

imherer

Oct 11, 2019

@optional ？只能 jsonb 才能用把好像？

encro

Jan 2, 2020

category 可以用 array[int]类型，然后加 Gist 索引