對(duì)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 --數(shù)組不去重
[1, 2, 2]

sq-- 簡(jiǎn)單標(biāo)量/基本值
-- 基本值可以是數(shù)字、帶引號(hào)的字符串、true、false或者null
SELECT '5'::json;
-- 有零個(gè)或者更多元素的數(shù)組（元素不需要為同一類型）
SELECT '[1, 2, "foo", null]'::json;
-- 包含鍵值對(duì)的對(duì)象
-- 注意對(duì)象鍵必須總是帶引號(hào)的字符串
SELECT '{"bar": "baz", "balance": 7.77, "active": false}'::json;
-- 數(shù)組和對(duì)象可以被任意嵌套
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}}

-- 簡(jiǎn)單的標(biāo)量/基本值只包含相同的值：
SELECT '"foo"'::jsonb @> '"foo"'::jsonb; --得 真t
-- 右邊的數(shù)字被包含在左邊的數(shù)組中：
SELECT '[1, 2, 3]'::jsonb @> '[1, 3]'::jsonb; --t 
-- 數(shù)組元素的順序沒(méi)有意義，因此這個(gè)例子也返回真：
SELECT '[1, 2, 3]'::jsonb @> '[3, 1]'::jsonb; --t
-- 重復(fù)的數(shù)組元素也沒(méi)有關(guān)系：
SELECT '[1, 2, 3]'::jsonb @> '[1, 2, 2]'::jsonb; --t
-- 右邊具有一個(gè)單一鍵值對(duì)的對(duì)象被包含在左邊的對(duì)象中：
SELECT '{"product": "PostgreSQL", "version": 9.4, "jsonb": true}'::jsonb @> '{"version": 9.4}'::jsonb; --t

-- 字符串作為一個(gè)數(shù)組元素存在：
SELECT '["foo", "bar", "baz"]'::jsonb ? 'bar'; --t
-- 字符串作為一個(gè)對(duì)象鍵存在：
SELECT '{"foo": "bar"}'::jsonb ? 'foo'; --t
-- 不考慮對(duì)象值：
SELECT '{"foo": "bar"}'::jsonb ? 'bar'; -- 得到假

-> 數(shù)組中是查找第幾項(xiàng)，json中是取某key的元素object
->> json中是取某key的元素text
#>  json中是通過(guò)路徑取元素object
#>>  json中是通過(guò)路徑取元素text

json:='[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json
--獲取json數(shù)組中的某一項(xiàng)元素
--下標(biāo)從0開(kāi)始
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某值通過(guò)路徑 object
 '{"a": {"b":{"c": "foo"}}}'::json#>'{a,b}' --{"c": "foo"}
--獲取json某值通過(guò)路徑 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