postgresql 如何在 postgres 9.3 中将 json 数组转换为 postgres int 数组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/37686187/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to convert json array into postgres int array in postgres 9.3
提问by Max Maddy
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
我有一个场景，我需要将一个 json 数组转换为 postgres int 数组并查询它的结果。下面是我的数组
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]}
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.
我想将 booksId 数组转换为 int 数组，然后再查询它。在 postgres 9.3 中有可能吗？我知道 9.4 + 提供了更多的 JSON 支持，但我目前无法更新我的数据库。
Below query gives me error
下面的查询给了我错误
Select data::json->>'bookIds' :: int[] from table
ERROR: malformed array literal: "bookIds"
LINE 1: Select data::json->>'bookIds' :: int[] from table
Is it possible to query elements inside json array in postgres 9.3.. Thanks in advance ...
是否可以在 postgres 9.3 中查询 json 数组中的元素.. 提前致谢...
回答by klin
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]}');
Note the proper syntax of json values.
请注意 json 值的正确语法。
You can use the function json_array_elements()
您可以使用该功能 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)
Use any()to search for an element in the arrays, e.g.:
使用any()搜索的阵列，例如一个元素：
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)
Read also about <@ operator.
另请阅读关于<@ operator.
You can also search in json array (without converting it to int array) by examine its elements, e.g.:
您还可以通过检查其元素来搜索 json 数组（无需将其转换为 int 数组），例如：
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)
回答by Joel B
These two functions (for json/jsonb) modified from a fantastic answerto this questionwork perfectly
这两个函数（for json/ jsonb）从对这个问题的精彩答案修改而来，效果很好
CREATE OR REPLACE FUNCTION json_array_castint(json) RETURNS int[] AS $f$
SELECT array_agg(x)::int[] || ARRAY[]::int[] FROM json_array_elements_text() t(x);
$f$ LANGUAGE sql IMMUTABLE;
CREATE OR REPLACE FUNCTION jsonb_array_castint(jsonb) RETURNS int[] AS $f$
SELECT array_agg(x)::int[] || ARRAY[]::int[] FROM jsonb_array_elements_text() t(x);
$f$ LANGUAGE sql IMMUTABLE;
You can use them as follows:
您可以按如下方式使用它们：
SELECT json_array_castint('[1,2,3]')
Which gives the expected return {1,2,3}as in integer[]. If you wonder why I'm concatenating with an empty array in each of the SELECTstatement it's because the cast is lossy and without it, if you try to cast an empty json/jsonbarray to an integer[]you'll get no return (not desired) instead of an empty array (as expected). With the above method when you do
这给预期收益{1,2,3}为integer[]。如果您想知道为什么我在每个SELECT语句中都连接一个空数组，那是因为转换是有损的并且没有它，如果您尝试将空json/jsonb数组转换为一个，integer[]您将不会得到任何回报（不需要）而不是一个空数组（如预期）。用上面的方法做的时候
SELECT json_array_castint('[]')
You'll get {}instead of nothing. See herefor more on why I added that.
你会得到{}而不是一无所有。请参阅此处了解有关我添加该内容的原因的更多信息。
回答by Jorge Campos
I would go a bit simpler:
我会更简单一点：
select * from
(
select t.id, value::text::int as bookvalue
from testjson t, json_array_elements(t.data->'bookIds')
) as t
where bookvalue in (1,11)
See it working here: http://sqlfiddle.com/#!15/e69aa/37
看到它在这里工作：http: //sqlfiddle.com/#!15/e69aa/37
