postgresql 数据支持 jsonb/json中 array或int 类型进行的交集比较

1.应用场景

如果一个表格中含有 一个 jsonb 的字段类型 ： 字段中有一个 array 类型的数据 或 number 类型的数据。（注意这个类型是用jsonb_typeof( parent -> children) 方法 显示出来的）

2.个人觉得需要注意的一些细节：

注意一下内容：现在抓取出来的数据仍然是jsonb 类型 。你可以通过 pg_typeof()进行检验。从jsonb 类型中抓取出来的数据仍然是jsonb 如果你要转化为其他类型的话要进行特殊处理

注意不要被 jsonb_typeof（）显示类型number 蒙蔽了。它虽然现实是number，其实现在仍然是jsonb 。

以下为json_typeof 的官方解释。

3.要进行交集比较 可以通过 && 符号进行两个array[]的比较。

4.如何返回JSON中存储的数组

大多数JSON操作符，返回的要么是TEXT要么是JSON.（没有直接返回 integer[]类型的）

例如以下查询
操作符->返回数组

postgres=# select pg_typeof('{"a":[1,2,3],"b":[4,5,6]}'::json->'a'), '{"a":[1,2,3],"b":[4,5,6]}'::json->'a';  
 pg_typeof | ?column?   
-----------+----------  
 json      | [1,2,3]  
(1 row)  

操作符->>返回text

postgres=# select pg_typeof('{"a":[1,2,3],"b":[4,5,6]}'::json->>'a'), '{"a":[1,2,3],"b":[4,5,6]}'::json->>'a';  
 pg_typeof | ?column?   
-----------+----------  
 text      | [1,2,3]  
(1 row)  

如果明确json_typeof或jsonb_typeof是array，则可以通过以下function将数组转换为行
不管是什么数组，都返回text行

json_array_elements_text(json)  

jsonb_array_elements_text(jsonb)  

postgres=# select pg_typeof(col),col from (select json_array_elements_text('{"a":"B","b":[1,2,3,4,5,6]}'::json->'b') col) t;  
 pg_typeof | col   
-----------+-----  
 text      | 1  
 text      | 2  
 text      | 3  
 text      | 4  
 text      | 5  
 text      | 6  
(6 rows)  

使用数组构造器，可以将多条记录构造为数组。

postgres=# SELECT array(select json_array_elements_text('{"a":"B","b":[1,2,3,4,5,6]}'::json->'b'));  
     array       
---------------  
 {1,2,3,4,5,6}  
(1 row)  

postgres=# SELECT pg_typeof(array(select json_array_elements_text('{"a":"B","b":[1,2,3,4,5,6]}'::json->'b')));  
 pg_typeof   
-----------  
 text[]  
(1 row)  

如何何转换JSONB数组的类型

如果想构造int[]，在构造前，可以将行的输出转换为对应的格式，

postgres=# SELECT array(select (json_array_elements_text('{"a":"B","b":[1,2,3,4,5,6]}'::json->'b'))::int );  
     array       
---------------  
 {1,2,3,4,5,6}  
(1 row)  

postgres=# SELECT pg_typeof(array(select (json_array_elements_text('{"a":"B","b":[1,2,3,4,5,6]}'::json->'b'))::int ));  
 pg_typeof   
-----------  
 integer[]  
(1 row)

编写JSON数组转换为SQL数组的函数

可以将以上方法转换为函数来使用，输入json typeof == array的json或jsonb对象，输出text数组。
转换jsonb, json array to text array

CREATE OR REPLACE FUNCTION json_arr2text_arr(_js jsonb)  
   RETURNS text[] AS  
$func$  
SELECT ARRAY(SELECT jsonb_array_elements_text(_js))  
$func$  
LANGUAGE sql IMMUTABLE;  


CREATE OR REPLACE FUNCTION json_arr2text_arr(_js json)  
   RETURNS text[] AS  
$func$  
SELECT ARRAY(SELECT json_array_elements_text(_js))  
$func$  
LANGUAGE sql IMMUTABLE;  

转换jsonb, json array to int array

CREATE OR REPLACE FUNCTION json_arr2int_arr(_js jsonb)  
   RETURNS int[] AS  
$func$  
SELECT ARRAY( SELECT (jsonb_array_elements_text(_js))::int )  
$func$  
LANGUAGE sql IMMUTABLE;  

CREATE OR REPLACE FUNCTION json_arr2int_arr(_js json)  
   RETURNS int[] AS  
$func$  
SELECT ARRAY( SELECT (json_array_elements_text(_js))::int )  
$func$  
LANGUAGE sql IMMUTABLE;  

例子

postgres=# select col, pg_typeof(col) from (select json_arr2text_arr(c1->'f') col from t3) t;  
    col    | pg_typeof   
-----------+-----------  
 {1,2,3,4} | text[]  
(1 row)  

postgres=# select col, pg_typeof(col) from (select json_arr2int_arr(c1->'f') col from t3) t;  
    col    | pg_typeof   
-----------+-----------  
 {1,2,3,4} | integer[]  
(1 row)  

postgres=# select col, pg_typeof(col) from (select json_arr2text_arr(c1->'g') col from t3) t;  
  col  | pg_typeof   
-------+-----------  
 {a,b} | text[]  
(1 row) 

为了配合hibernate-jpa使用 编写如下function，返回的是boolean 类型的结果