一、postgresql實用函數

（只列舉本次用到的函數，其他函數在文章后面有詳解）

1.1、將jsonb格式的數組中的值展開/分解成單獨的數據/對象？

SELECT answer_id,? jsonb_array_elements(option_ids)::INTEGER? AS option_id

FROM db_live_answer?

WHERE survey_code='No.20230605165428002'

?

?

1.2、如何根據對象id將jsonb數組展開到每個jsonb列的對象中？

?如果所有json數組都包含兩個新列的兩個元素，那么使用固定路徑，如dmfay的答案。 否則，你應該使用jsonb_array_elements（）兩次取消數組，分別為text_id_one和text_id_two 。

update sample t set
    text_id_one = value1,
    text_id_two = value2
from sample s, 
    jsonb_array_elements(s.metadata_array) as e1(value1),
    jsonb_array_elements(s.metadata_array) as e2(value2)
where s.id = t.id
    and value1->>'id' = 'textIdOne'
    and value2->>'id' = 'textIdTwo'
returning t.*

如果數組中有兩個以上的元素，則此變體可能更高效（也更方便）：

update sample t
set 
    text_id_one = arr1->0,
    text_id_two = arr2->0
from (
    select 
        id,
        jsonb_agg(value) filter (where value->>'id' = 'textIdOne') as arr1,
        jsonb_agg(value) filter (where value->>'id' = 'textIdTwo') as arr2
    from sample,
    jsonb_array_elements(metadata_array)
    group by id
    ) s
where t.id = s.id
returning t.*

例子來源：如何根據對象id將jsonb數組展開到每個jsonb列的對象中？

2、postgresql操作符的使用

一、 -> 和 ->>? （適用于操作數組） ：

-> 表示獲取一個JSON數組元素，支持下標值(下標從0開始)、Key獲取。

->> 表示獲取一個JSON對象字符串。

代碼：
SELECT '[{"a":1},{"b":2},{"c":3}]'::JSON -> 1;
結果：
{"b":2}
以上，::JSON 表示聲明前面的字符串為一個JSON字符串對象，而且PostgreSQL中的JSON、JSONB對象 Key的聲明必須是字符串 。同時，1表示獲取JSON數組中下標值為1的JSON對象。

接下來，看下 ->> 的用法：

代碼：
SELECT '[{"a":1},{"b":2},{"c":3}]'::JSON ->> 1;
結果：
{"b":2}
以上，->> 的查詢結果和 -> 對比不太直觀，我們可以進一步驗證。

連用：

代碼：
SELECT '[{"a":1},{"b":2},{"c":3}]'::JSON -> 1 -> 'b';
結果：
2
以上，我們可以看到首先我們使用下標的方式，獲取JSON數組中下標值為1的JSON對象 {"b":2}。然后，我們通過Key的方式來獲取這個JSON對象的Value值，結果是 2。

接下來，我們測試下 ->> 的方式來獲取：

代碼：
SELECT '[{"a":1},{"b":2},{"c":3}]'::JSON ->> 1 -> 'b';
?
報錯：
[SQL]SELECT '[{"a":1},{"b":2},{"c":3}]'::JSON ->> 1 -> 'b';
?
[Err] 錯誤: ?操作符不存在: text -> unknown
LINE 1: SELECT '[{"a":1},{"b":2},{"c":3}]'::JSON ->> 1 -> 'b';
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?^
HINT: ?No operator matches the given name and argument types. You might need to add explicit type casts.
以上，可以看到錯誤提示操作符不存在，因為第一步查詢出來的是一個字符串，不是JSON對象。

改成正確提取方式：

代碼：
SELECT ('[{"a":1},{"b":2},{"c":3}]'::JSON ->> 1)::JSON -> 'b';
結果：
2
以上，可以看到現(xiàn)在可以正確查詢出結果，我們將第一步查詢出來的字符串轉成JSON對象，然后通過 Key 的方式來獲取 Value。不過，這種查詢方式相對于 -> 來說還是比較繁瑣的。

二、 #> 和 #>>?（適用于操作對象） ：

在前一步，我們在一個JSON數組中可以使用 -> 下標值的方式來獲取一個JSON對象。但是，如果我們我們檢索的不是JSON數組，而是一個JSON對象中的JSON對象。很顯然，這種下標獲取的方式不再適用。不過，我們可以使用下面的方式來獲取。

#> 表示獲取指定路徑的一個JSON對象，

#>>表示獲取指定路徑的一個JSON對象的字符串。

代碼：
SELECT '{"a":1,"b":{"ba":"b1","bb":"b2"},"c":3}'::JSON #> ''
結果：
{"ba":"b1","bb":"b2"}
以上，我們使用 #> 方式來獲取一個JSON對象中的JSON對象。

注意：

在獲取一個JSON對象時，除非是JSON數組中的下標，必須要要用 { } 將JSON對象的 Key 包裹起來，否則會拋出異常。

代碼：
SELECT '{"a":1,"b":{"ba":"b1","bb":"b2"},"c":3}'::JSON#>'b'
結果：
[Err] 錯誤: ?有缺陷的數組常量:"b"
LINE 1: SELECT '{"a":1,"b":{"ba":"b1","bb":"b2"},"c":3}'::JSON#>'b'
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ^
DETAIL: ?數組值必須以 "{" 或者維度信息開始。

同樣的，我們還可以在 #> 的基礎上，繼續(xù)獲取這個JSON對象內的相關信息。

代碼：
SELECT '{"a":1,"b":{"ba":"b1","bb":"b2"},"c":3}'::JSON#>''->'ba'
結果：
"b1"
??????以上，可以看到 -> 獲取的是一個JSON對象。->>是獲取的一個text 文本

二、PostgreSQL 操作符、函數

json和jsonb 操作符

額外的jsonb操作符

JSON 創(chuàng)建函數

JSON 處理?

?參考：PostgreSQL中JSON函數和操作符_function jsonb_set(json, unknown, unknown) does no_EngineerForSoul的博客-CSDN博客

postgresql 關于JSON、JSONB操作符以處理函數_postgresql json函數_谷滿滿的博客-CSDN博客文章來源地址http://www.zghlxwxcb.cn/news/detail-477774.html

到了這里，關于Postgresql關于JSON、JSONB的操作符、處理函數（持續(xù)更新）的文章就介紹完了。如果您還想了解更多內容，請在右上角搜索TOY模板網以前的文章或繼續(xù)瀏覽下面的相關文章，希望大家以后多多支持TOY模板網！