跳至主要内容

PostgreSQL Jsonb 雜項紀錄

Jsonb CRUD 會這幾個基本就夠了  ​

  • 寫在最前面: 基本上每個操作都有一個 operator 與一個相對應的 內建函數 可使用，但會下面這兩個已經可以大殺四方了。
  • -> + ->> operator: 查詢內容
  • jsonb_set() 函數: 新增、修改、與 刪除 萬用，且多版本支援
  • 其他的就先當作是 PostgreSQL 提供的語法糖。

Jsonb column 必學內容操作​

CRUD操作範例
insertjsonb_set()update table SET jasonb_col =
jsonb_set( jasonb_col, Array['path_element'], '{ "k1": "v1"'}'::jsonb );
updatejsonb_set()同上範例
deletejsonb_set()同上範例但 value 改為 null, 'null'::jsonb
update table SET jasonb_col =
jsonb_set( jasonb_col, Array['path_element'], 'null'::jsonb );
select->, ->>select jasonb_col -> 'attribute' ->> 'attribute' from table

PostgreSQL Json/Jsonb 差異​

PostgreSQL 內建 JSON/JSONB 型態的 Columns。
JSON :
以文字儲存的 JSON 格式資料。

JSONB :
PostgreSQL 中的一種特殊欄位，儲存的是 JSON 型態但是 Compiled 過的 binary 資料。
特性是，可以讓 PostgreSQL 經由 mata data 加速查詢 的速度。
但反過來說，在 儲存時較花時間 ，因為要先轉 mata data 所以相對上會比較耗時。
因此主要用在，少編輯但卻需要經常查詢的請境下。

PostgreSQL Jsonb 雜項紀錄  ​

  • 範例情境

假設有一個紀錄標本資訊 table ，其中以 json_data 用來記錄分類樹資料 (界門綱目科屬種)。

    --table: insect_specimen
    create table insect_specimen (
      id serial primary key, 
      sample_id text, 
      collect_date timestamp without time zone, 
      commom_name character varying(100),
      classification jsonb -- 測試用的 jsonb column  
    );
    
    INSERT INTO public.insect_specimen(
                id, sample_id, collect_date, commom_name, classification)
        VALUES (nextVal('insect_specimen_id_seq'), 'A0000000001', '2025-11-20', '蟑螂',
                '{"Kingdom" : "Animalia", "Phylum" : "Arthropoda", "Class" : "Insecta","Superorder" : "Dictyoptera","Order" : "Blattodea"}');
                
    
    INSERT INTO public.insect_specimen(
                id, sample_id, collect_date, commom_name, classification)
        VALUES (nextVal('insect_specimen_id_seq'), 'A0000000002', '2025-11-22', '白線斑蚊',
                '{"Kingdom" : "Animalia","Phylum" : "Arthropoda","Class" : "Insecta","Order" : "Diptera","Family" : "Culicidae" }');  
    -- "Genus" : "Aedes"
    -- "Subgenus": "Stegomyia"
    -- "Species' : "albopictus"

關於 append 新值​

  • JSONB column 要先有 空 JSON 物件,如 '{  }' 才能 set 值
    • 若商業邏輯上 jsonb 欄位非初始便有資料則可先給予空值 '{  }'
    INSERT INTO public.insect_specimen(
                id, sample_id, collect_date, commom_name, 
                classification)
        VALUES (nextVal('insect_specimen_id_seq'), 'A0000000001', '2025-11-20', '蟑螂',
                '{}');

自動縮排 Jsonb 查詢結果 jsonb_pretty​

  • 以 jsonb_pretty(jsonb) 進行自動縮排，方便 人工閱讀
    select jsonb_pretty(insect.classification) classification from insect_specimen insect where id = 1;
    
    ==== 查詢結果是無序的
    "{
        "Class": "Insecta",
        "Order": "Blattodea",
        "Phylum": "Arthropoda",
        "Kingdom": "Animalia",
        "Superorder": "Dictyoptera"
    }"

取代既有資料 insert/update: jsonb_set  ​

  • PostgreSQL 10+ 可使用 jsonb_insert : 插入新值，不覆蓋。
  • 置入並取代物件, Replacement(取代既有資料)
    • arguments
      • 1st: jsob 欄位
      • 2nd: Array Path 路徑, Array['hierarchy', 'nested']。 找得到路徑才會 update
      • 3rd: Jsonb Content 新內容，需轉成 jsonb 格式。quote 用來告知是 String, ::jsonb 用來告知轉成 jsonb 格式

Syntax: jsonb_set

    jsonb_set(
          target JSONB, path TEXT[], new_value JSONB[, create_if_missing BOOLEAN]
        ) -> JSONB
  • 注意事項:
    • Array Path :
      • 語法: Array['hierarchy', 'nested']
      • 若 Path 查無標的 element，則不動作。但會有成功回應。
      • element 下全部結構，都會被新值 (新 Jsonb Content 取代)
    • Jsonb Content :
      • 格式須為 Jsonb format，內容後方需加 ::jsonb 宣告。
      • Jsonb String format content 表示法: "new_value"::jsonb
      • Jsonb Object format content 表示法: " '{ "key1": "value1", "key2": { "key2-2": "value2-2" } }'::jsonb

範例

    -- 指定路徑內容改為簡單字串 : String
    update insect_specimen 
        SET classification = jsonb_set( classification, Array['Genus'], '"Aedes"'::jsonb ) where id = 2; 

    -- 指定路徑內容改為 nested ccontent: Json object
    update insect_specimen SET classification = jsonb_set( 
        classification, 
        Array['Genus'], 
        '{  "name":"Aedes", 
            "Subgenus": { "name": "Stegomyia" }
         }'::jsonb 
        
        ) where id = 2; 
        
    -- 指定 nested 路徑: Array['parent', 'child', 'descendent'] 
    update insect_specimen 
        SET classification = 
            jsonb_set( 
              classification, 
              Array['Genus', 'Subgenus', 'name'], 
              '{ "value": "Stegomyia"}'::jsonb ) 
        where id = 2;  
        
    ====
    "{
        "Class": "Insecta",
        "Genus": {
            "name": "Aedes",
            "Subgenus": {
                "name": "Stegomyia"
            }
        },
        "Order": "Diptera",
        "Family": "Culicidae",
        "Phylum": "Arthropoda",
        "Kingdom": "Animalia",
        "Species": "albopictus"
    }"

變體: 路徑直接用 object 代替

  • Array Path 以物件轉換

syntax

-- 下列兩種 路徑寫法意思相同
    Array['parent', 'child', 'descendent'] 
    
    '{parent, child, descendent}'

example

   -- 單一階層
    update insect_specimen 
        SET classification = jsonb_set( classification, 
        '{Genus}', -- 等同於 Array['Genus']
        '"Aedes-2"'::jsonb ) where id = 2; 

   -- 三階階層
    update insect_specimen 
         SET classification = jsonb_set( classification, 
         '{Genus, Subgenus, name}', -- 等同於 Array['Genus', 'Subgenus', 'name']
         '{ "value": "Stegomyia-2" }'::jsonb ) where id = 2;

列出所有頂層鍵: jsonb_object_keys()​

  • 使用 jsonb_object_keys() 進行查詢
  • 加上 DISTINCT 去重複
    --列出所有頂層鍵，單一 row
    select jsonb_object_keys(classification) as keys
        from insect_specimen where id = 1;

    --列出 Table 下 Jsonb 所有頂層鍵: 依 Row 查詢，不自動去重複
    select DISTINCT jsonb_object_keys(classification) as keys
        from insect_specimen;

PostgreSQL Jsonb 相關 operators  ​

Operator功用Path Style/R Operand Type
->Jsonb 槽串 路徑連接符號 練續符號串接 Style Path
->>Jsonb 解析成 String練續符號串接 Style Path
#>Jsonb 槽串 路徑連接符號 Json Object Style path
#>>Jsonb 解析成 StringJson Object Style path
-指定頂層結構進行刪除
不可串接		String
#-指定路徑進行刪除Json Object Style path
Text[]
?指定字串是否存在於頂層鍵(top-level key)text
?|Text Array 中清單是否至少一個存在於頂層鍵Json Object Style path
Text[]
?&Text Array 中所有清單是否都存在於頂層鍵Json Object Style path
Text[]
||串接 jsonb 在一起回傳 jsonb
  • 註: sharp operators 或 jsonb_set() function 在使用 Json Object Style path 時，下列兩種風格皆可採用
    -- 標準寫法: 依據 key 的型別做相應字面量修飾
    '{"Genus", "Subgenus", "name", "value"}'
    '{"profile", "phones", 0}'
 
    --簡化版: 省略 key 型別修飾 
    '{Genus, Subgenus, name, value}'
    '{profile, phones, 0}'