如何在Postgres 9.3中将json数组转换为postgres int数组 [英] How to convert json array into postgres int array in postgres 9.3

问题描述

我遇到需要将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)

这篇关于如何在Postgres 9.3中将json数组转换为postgres int数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！