PostgreSQL Jsonb 雜項紀錄

Jsonb CRUD 會這幾個基本就夠了  

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

Jsonb column 必學內容操作

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

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;