公告版位
需要找什麼嗎? 左邊分類或許可以幫助你...
雖然我標題寫 Json、Jsonb 操作,但實際上我這裡只會用 Jsonb 來示範

會用 Jsonb 實際上就會用 Json ,因為 Jsonb 效能比較好 (資料無排序),但如果你要有排序功能,可以用傳統 Json 來操作

前置作業重點:

插入 Jsonb 無法用 INSERT INTO ,因為他找不到 資料 插....,所以要先用 INSERT INTO 先插入一個 空 資料,這樣未來 UPDATE 才找的到洞插入...

更多資源可以參考 PostgreSQL 官方的 Json 文件:http://www.postgresql.org/docs/9.5/static/function...

前置作業:

先建立以下表格

CREATE TABLE test(data JSONB);

插入空 Jsonb

INSERT INTO test (data) VALUES ('{}');

然後查詢結果:

SELECT * FROM test;

沒意外會輸出:{ }

--------------------------------------------------------------
插入 Jsonb:

1. 基本:

UPDATE test SET data = jsonb_set( data, '{A}', '"aaa"' );  -- 注意 aaa 外面是雙引號,再更外面則是單引號

SELECT jsonb_pretty(data) FROM test;  -- 漂亮查詢,查看剛剛的插入結果

沒意外會顯示:
{
    "A" : "aaa"
}


2. 物件插入:

UPDATE test SET data = jsonb_set( data, '{B}', '{"C":"ccc"}' );

查詢結果

{
    "A" : "aaa",
    "B" : {
        "C" : "ccc"
    }
}

3. 巢狀插入:

UPDATE test SET data = jsonb_set( data, '{B, D}', '"ddd"' );

{
   "A" : "aaa",
   "B" : {
        "C" : "ccc",
        "D" : "ddd"
    }
}

4. 多巢狀插入

先準備一個 E 物件
UPDATE test SET data = jsonb_set( data, '{B, E}', '{"F":"fff"}' );

然後多巢狀插入
UPDATE test SET data = jsonb_set( data, '{B, E, G}', '"ggg"' );

查詢結果:

{
   "A" : "aaa",
   "B" : {
        "C" : "ccc",
        "D" : "ddd"
        "E" : {
            "F", "fff",
            "G", "ggg"
        }
    }
}

-----------------------------------------------------------------
查詢 Jsonb:

1. 物件查詢:

SELECT  data  -> 'A'  FROM  test;

查詢結果 (注意雙引號):

"aaa"

2. 內容查詢:

注意:
        內容查詢 建議 查詢 字串、數字、布林值,也就是最小單位
        不建議 查詢 Json 物件,也就是像 { "A" : "aaa" } 這種東西

SELECT  data  ->> 'A'  FROM  test;

查詢結果 (注意沒有雙引號):

aaa

3. 物件查詢 ( 2 ):

SELECT  data  -> 'B'  FROM  test;

查詢結果:

{
    "C": "ccc",
    "D": "ddd",
    "E": {
        "F": "fff",
        "G": "ggg"
    }
}

4. 巢狀內容查詢:

SELECT  data  -> 'B' -> 'E' ->> 'G'  FROM  test;

查詢結果:

ggg

5. 巢狀物件查詢 ( 1 ):

SELECT  data  -> 'B' -> 'E' -> 'G'  FROM  test;

查詢結果:

"ggg"

5. 巢狀物件查詢 ( 2 ):

SELECT data -> 'B' -> 'E' FROM test;

查詢結果:

{ "F" : "fff", "G" : "ggg" }

---------------------------------------------------------------------------------------
刪除 Jsonb:

1. 刪除根節點的 Key (非巢狀):

UPDATE test SET data = data - 'A';

查詢結果 ( A 被刪掉了 ):

{
    "B": {
        "C": "ccc",
        "D": "ddd",
        "E": {
            "F": "fff",
            "G": "ggg"
        }
    }
}

2. 巢狀刪除

UPDATE test SET data = data #- '{B, E, F}';

查詢結果 ( F 被刪掉了 ):

{
    "B": {
        "C": "ccc",
        "D": "ddd",
        "E": {
            "G": "ggg"
        }
    }
}

----------------------------------------------------------------------------------------------------
操作 Jsonb 陣列

1. 插入陣列:

UPDATE test SET data = jsonb_set( data, '{C}', '["XXX"]' );

執行結果:

{"B": {"C": "ccc", "D": "ddd", "E": {"G": "ggg"}}, "C": ["XXX"]}

2. 插入陣列 (指定位置):

UPDATE test SET data = jsonb_set( data, '{C, 1}', '"YYY"' );
UPDATE test SET data = jsonb_set( data, '{C, 2}', '"ZZZ"' );

執行結果:

{"B": {"C": "ccc", "D": "ddd", "E": {"G": "ggg"}}, "C": ["XXX", "YYY", "ZZZ"]}

3. 自動插入到陣列最尾端 ( Append ):

位置中輸入 INT 最大值 (2147483647) 即可
PostgreSQL 會自動幫你放到正確位置上
這個是我無意中發現的 "偷吃步做法"
正式一點還是要用 jsonb_array_length ( jsonb ) 取得陣列長度 ,然後再 + 1,這樣比較 "正統"

UPDATE test SET data = jsonb_set( data, '{C, 2147483647}', '"PPP"' );
UPDATE test SET data = jsonb_set( data, '{C, 2147483647}', '"QQQ"' );
UPDATE test SET data = jsonb_set( data, '{C, 2147483647}', '"RRR"' );

執行結果:

{"B": {"C": "ccc", "D": "ddd", "E": {"G": "ggg"}}, "C": ["XXX", "YYY", "ZZZ", "PPP", "QQQ", "RRR"]}











創作者介紹
創作者 黃彥霖 的頭像
黃彥霖

彥霖 實驗筆記

黃彥霖 發表在 痞客邦 留言(1) 人氣()


留言列表 (1)

發表留言
  • Tam
  • Hi, I found your post is really useful. May I seek your help about:
    如果我有Array 如下:

    {
    "C": "ccc",
    "D": "ddd",
    "E": {
    "F": "fff",
    "G": [
    {
    "Balance": 1000.0,
    "AccountId": 1700,
    "calc": "645724+24892574",
    "balance1": 1000.0,
    "round": 1
    },
    {
    "Balance": 5500.0,
    "AccountId": 1750,
    "calc": null,
    "balance1": 5500.0,
    "round": 1
    },
    {
    "Balance": 9999.0,
    "AccountId": 1710,
    "calc": null,
    "balance1": 9999.0,
    "round": 1
    }
    ]

    }
    }

    我目的是 如果"calc" = null, 就 Update 成為 "". 請問應如何達成呢?

    謝謝你!