对Postgresql中的json和array使用介绍

-----------1.键的顺序
SELECT '{"bar": "baz", "balance": 7.77, "active":false}'::json;
           json            
-------------------------------------------------
 {"bar": "baz", "balance": 7.77, "active":false}
(1 row)
SELECT '{"bar": "baz", "balance": 7.77, "active":false}'::jsonb;
           jsonb            
--------------------------------------------------
 {"bar": "baz", "active": false, "balance": 7.77}
(1 row)
---------2.去重
SELECT '{"bar": "baz", "balance": 7.77, "balance":false}'::jsonb; --去重
{"bar": "baz", "balance": false}
SELECT '{"bar": "baz", "balance": 7.77, "balance":false}'::json; --不去重
{"bar": "baz", "balance": 7.77, "balance":false}
select '[1, 2, 2]'::jsonb --数组不去重
[1, 2, 2]

sq-- 简单标量/基本值
-- 基本值可以是数字、带引号的字符串、true、false或者null
SELECT '5'::json;
-- 有零个或者更多元素的数组（元素不需要为同一类型）
SELECT '[1, 2, "foo", null]'::json;
-- 包含键值对的对象
-- 注意对象键必须总是带引号的字符串
SELECT '{"bar": "baz", "balance": 7.77, "active": false}'::json;
-- 数组和对象可以被任意嵌套
SELECT '{"foo": [true, "bar"], "tags": {"a": 1, "b": null}}'::json;

5
[1, 2, “foo”, null]
{“bar”: “baz”, “balance”: 7.77, “active”: false}
{“foo”: [true, “bar”], “tags”: {“a”: 1, “b”: null}}

-- 简单的标量/基本值只包含相同的值：
SELECT '"foo"'::jsonb @> '"foo"'::jsonb; --得 真t
-- 右边的数字被包含在左边的数组中：
SELECT '[1, 2, 3]'::jsonb @> '[1, 3]'::jsonb; --t 
-- 数组元素的顺序没有意义，因此这个例子也返回真：
SELECT '[1, 2, 3]'::jsonb @> '[3, 1]'::jsonb; --t
-- 重复的数组元素也没有关系：
SELECT '[1, 2, 3]'::jsonb @> '[1, 2, 2]'::jsonb; --t
-- 右边具有一个单一键值对的对象被包含在左边的对象中：
SELECT '{"product": "PostgreSQL", "version": 9.4, "jsonb": true}'::jsonb @> '{"version": 9.4}'::jsonb; --t

-- 字符串作为一个数组元素存在：
SELECT '["foo", "bar", "baz"]'::jsonb ? 'bar'; --t
-- 字符串作为一个对象键存在：
SELECT '{"foo": "bar"}'::jsonb ? 'foo'; --t
-- 不考虑对象值：
SELECT '{"foo": "bar"}'::jsonb ? 'bar'; -- 得到假

-> 数组中是查找第几项，json中是取某key的元素object
->> json中是取某key的元素text
#>  json中是通过路径取元素object
#>>  json中是通过路径取元素text

json:='[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json
--获取json数组中的某一项元素
--下标从0开始
select json->2 from test where name='jsonarray' --{"c": "baz"}
--获取json某一key的值为object
select (json->2)->'c' from test where name='jsonarray' --"baz"
--获取json某一key的值为text
select (json->2)->>'c' from test where name='jsonarray' --"baz"
--获取json某值通过路径 object
 '{"a": {"b":{"c": "foo"}}}'::json#>'{a,b}' --{"c": "foo"}
--获取json某值通过路径 text
 '{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}' --3

CREATE OR REPLACE FUNCTION json_arr2text_arr(_js json) RETURNS text[] AS
$$
DECLARE
 anyArray text[];
begin
SELECT ARRAY(SELECT json_array_elements_text(_js)) INTO anyArray;
RETURN anyArray;
end
$$ 
LANGUAGE plpgsql;

select * from tb_template_area_safe WHERE json_arr2text_arr(area_functions) @> array['NP1'];

CREATE OR REPLACE FUNCTION json_arr2int_arr(_js json) RETURNS int[] AS 
$$
DECLARE
 anyArray int[];
begin
SELECT ARRAY(SELECT json_array_elements_text(_js)::int) INTO anyArray;
RETURN anyArray;
end;
$$ 
LANGUAGE plpgsql;

SELECT tdnm.mid, tdnm.title, tdnm.content, tdnm.ui_id, tdnm.create_time, tdnm.cancel_time, 
tdnm.job_ids, tdnm.remarks, tdnm.message_level_code, tdnm.channels FROM tb_data_notify_message 
tdnm WHERE 1=1 and json_arr2text_arr(tdnm.job_ids) 
&&array[['1','10']] ORDER BY create_time DESC