對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