如何在Postgres 9.3中将json数组转换为postgres int数组
我遇到需要将json数组转换为postgres int数组并查询结果的情况.下面是我的数组
I have scenario where i need to convert a json array into postgres int array and query it for the result. Below is my array
ID DATA
1 {"bookIds" : [1,2,3,5], "storeIds": [2,3]}
2 {"bookIds" : [4,5,6,7], "storeIds": [1,3]}
3 {"bookIds" : [11,12,10,9], "storeIds": [4,3]}
我想将booksId数组转换为int数组,然后再查询它. postgres 9.3中可能吗?我知道9.4 +提供了更多的JSON支持,但目前无法更新我的数据库.
I want convert booksId array into int array and later query it. Is it possible in postgres 9.3? I know 9.4 + provides much more JSON support but i can't update my db at the moment.
以下查询给我错误
Select data::json->>'bookIds' :: int[] from table
ERROR: malformed array literal: "bookIds"
LINE 1: Select data::json->>'bookIds' :: int[] from table
是否可以在postgres 9.3中查询json数组中的元素..预先感谢...
Is it possible to query elements inside json array in postgres 9.3.. Thanks in advance ...
问题中的设置应如下所示:
The setup in the question should look like this:
create table a_table (id int, data json);
insert into a_table values
(1, '{"bookIds": [1,2,3,5], "storeIds": [2,3]}'),
(2, '{"bookIds": [4,5,6,7], "storeIds": [1,3]}'),
(3, '{"bookIds": [11,12,10,9], "storeIds": [4,3]}');
请注意json值的正确语法.
Note the proper syntax of json values.
您可以使用函数 json_array_elements()
You can use the function json_array_elements()
select id, array_agg(e::text::int)
from a_table, json_array_elements(data->'bookIds') e
group by 1
order by 1;
id | array_agg
----+--------------
1 | {1,2,3,5}
2 | {4,5,6,7}
3 | {11,12,10,9}
(3 rows)
使用 any()
在数组中搜索元素,例如:
Use any()
to search for an element in the arrays, e.g.:
select *
from (
select id, array_agg(e::text::int) arr
from a_table, json_array_elements(data->'bookIds') e
group by 1
) s
where
1 = any(arr) or
11 = any(arr);
id | arr
----+--------------
1 | {1,2,3,5}
3 | {11,12,10,9}
(2 rows)
还请阅读有关 <@ operator
的信息.
Read also about <@ operator
.
您还可以通过检查其元素来搜索json数组(无需将其转换为int数组),例如:
You can also search in json array (without converting it to int array) by examine its elements, e.g.:
select t.*
from a_table t, json_array_elements(data->'bookIds') e
where e::text::int in (1, 11);
id | data
----+-----------------------------------------------
1 | {"bookIds" : [1,2,3,5], "storeIds": [2,3]}
3 | {"bookIds" : [11,12,10,9], "storeIds": [4,3]}
(2 rows)