結合近期接觸到的知識點，做了一個歸納。會持續更新
json
官網文檔 http://www.postgres.cn/docs/12/datatype-json.html
json的兩種格式
總結：json輸入快，處理慢。是精準拷貝，所以能準確存儲遺留對象的原格式，如對象鍵順序。jsonb輸入慢，處理快。會被重新解析成json數據，不保存原對象的鍵順序，并且去重相同的鍵值，以最后一個為準。通常，除非有特別特殊的需要（例如遺留的對象鍵順序假設），大多數應用應該 更愿意把 JSON 數據存儲為jsonb
官網：
json 和 jsonb數據類型接受***幾乎***完全相同的值集合作為輸入。 主要的實際區別之一是效率。json數據類型存儲輸入文本的精準拷貝，處理函數必須在每 次執行時必須重新解析該數據。而jsonb數據被存儲在一種分解好的 二進制格式中，它在輸入時要稍慢一些，因為需要做附加的轉換。但是 jsonb在處理時要快很多，因為不需要解析。jsonb也支 持索引，這也是一個令人矚目的優勢。
由于json類型存儲的是輸入文本的準確拷貝，其中可能會保留在語法 上不明顯的、存在于記號之間的空格，還有 JSON 對象內部的鍵的順序。還有， 如果一個值中的 JSON 對象包含同一個鍵超過一次，所有的鍵/值對都會被保留（ 處理函數會把最后的值當作有效值）。相反，jsonb不保留空格、不 保留對象鍵的順序并且不保留重復的對象鍵。如果在輸入中指定了重復的鍵，只有 最后一個值會被保留。
通常，除非有特別特殊的需要（例如遺留的對象鍵順序假設），大多數應用應該 更愿意把 JSON 數據存儲為jsonb
由于json類型存儲的是輸入文本的準確拷貝，其中可能會保留在語法 上不明顯的、存在于記號之間的空格，還有 JSON 對象內部的鍵的順序。還有， 如果一個值中的 JSON 對象包含同一個鍵超過一次，所有的鍵/值對都會被保留（ 處理函數會把最后的值當作有效值）。相反，jsonb不保留空格、不 保留對象鍵的順序并且不保留重復的對象鍵。如果在輸入中指定了重復的鍵，只有 最后一個值會被保留。
-----------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]
將字符串轉為json格式
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
jsonb還有一個存在操作符，它是包含的一種 變體：它測試一個字符串（以一個text值的形式給出）是否出 現在jsonb值頂層的一個對象鍵或者數組元素中。
除非特別注解， 下面這些例子返回真：
-- 字符串作為一個數組元素存在：
SELECT '["foo", "bar", "baz"]'::jsonb ? 'bar'; --t
-- 字符串作為一個對象鍵存在：
SELECT '{"foo": "bar"}'::jsonb ? 'foo'; --t
-- 不考慮對象值：
SELECT '{"foo": "bar"}'::jsonb ? 'bar'; -- 得到假
當涉及很多鍵或元素時，JSON 對象比數組更適合于做包含或存在測試， 因為它們不像數組，進行搜索時會進行內部優化，并且不需要被線性搜索。
索引
-> 數組中是查找第幾項，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
補充：postgresql 數據庫 jsonb/json中 array或int 類型進行的交集比較 存儲過程字符串、整數數組條件查詢
首先要新增這兩個存儲過程
新增存儲過程字符串數組條件查詢
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;
查詢字符串數組中存在NP1的記錄
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
數組操作符:
| Operator |
Description |
Example |
Result |
| = |
equal |
ARRAY[1.1,2.1,3.1]::int[] = ARRAY[1,2,3] |
t |
| > |
not equal |
ARRAY[1,2,3] > ARRAY[1,2,4] |
t |
|
less than |
ARRAY[1,2,3] ARRAY[1,2,4] |
t |
| > |
greater than |
ARRAY[1,4,3] > ARRAY[1,2,4] |
t |
| = |
less than or equal |
ARRAY[1,2,3] = ARRAY[1,2,3] |
t |
| >= |
greater than or equal |
ARRAY[1,4,3] >= ARRAY[1,4,3] |
t |
| @> |
contains |
ARRAY[1,4,3] @> ARRAY[3,1] |
t |
| @ |
is contained by |
ARRAY[2,7] @ ARRAY[1,7,4,2,6] |
t |
|
overlap (have elements in common) |
ARRAY[1,4,3] ARRAY[2,1] |
t |
| || |
array-to-array concatenation |
ARRAY[1,2,3] || ARRAY[4,5,6] |
{1,2,3,4,5,6} |
| || |
array-to-array concatenation |
ARRAY[1,2,3] || ARRAY[[4,5,6],[7,8,9]] |
{{1,2,3},{4,5,6},{7,8,9}} |
| || |
element-to-array concatenation |
3 || ARRAY[4,5,6] |
{3,4,5,6} |
| || |
array-to-element concatenation |
ARRAY[4,5,6] || 7 |
{4,5,6,7} |
PostgreSQL 9.4.4 中文手冊
以上為個人經驗，希望能給大家一個參考，也希望大家多多支持腳本之家。如有錯誤或未考慮完全的地方，望不吝賜教。
您可能感興趣的文章:- 解決PostgreSQL Array使用中的一些小問題
- postgresql數據庫使用說明_實現時間范圍查詢
- postgresql 實現將數組變為行
- PostgreSQL 對數組的遍歷操作
- postgres array_to_string和array的用法講解
