如何搜索PostgreSQL中的JSON数组中的多个项目9.3
问题描述:
我有我需要在JSON数组中搜索多个值的场景。以下是我的模式。如何搜索PostgreSQL中的JSON数组中的多个项目9.3
ID DATA
1 {"bookIds" : [1,2,3,5], "storeIds": [2,3]}
2 {"bookIds" : [1,2], "storeIds": [1,3]}
3 {"bookIds" : [11,12,10,9], "storeIds": [4,3]}
我希望所有行的值为1,2。下面是我正在使用的查询(这是查询是由他的一个同事*用户先生klin信贷给他写的)。
select t.*
from JSONTest t, json_array_elements(data->'bookIds') books
where books::text::int in (1, 2);
但是,输出我在输出中重复行,下面是我的输出。
id data
1 {"bookIds" : [1,2,3,5], "storeIds": [2,3]}
1 {"bookIds" : [1,2,3,5], "storeIds": [2,3]}
2 {"bookIds" : [1,2], "storeIds": [1,3]}
2 {"bookIds" : [1,2], "storeIds": [1,3]}
我只想输出两行是id 1,2。我怎样才能做到这一点?我不想使用distinct由于其他方面的限制,
答
不幸的是,从一个JSON数组到一个“真实”的Postgres数组没有直接的转换功能。 (data ->'bookIds')::text
返回几乎是Postgres数组的文字:例如[1,2,3,5]
。如果将[]
替换为{}
,则该值可以转换为整数数组。一旦我们有一个适当的整数数组,我们可以使用@>
来测试它是否包含另一个数组:
select *
from jsontest
where translate((data ->'bookIds')::text, '[]', '{}')::int[] @> array[1,2];
translate((data ->'bookIds')::text, '[]', '{}')
将转换[1,2,3,5]
到{1,2,3,5}
然后被转换为使用::int[]
SQLFiddle数组:http://sqlfiddle.com/#!15/6457a/4
感谢这给了我一些继续:)这种操作是多么昂贵? –