PostgreSQL 務(wù)實應(yīng)用（四/5）JSON

JSON 可謂風(fēng)靡互聯(lián)網(wǎng)颜骤，在數(shù)據(jù)交換使用上，其優(yōu)勢特別明顯捣卤，其結(jié)構(gòu)簡潔忍抽、可讀易讀八孝、形式靈活。很多 API 接口的數(shù)據(jù)都采用 JSON 來表示鸠项。

PostgreSQL 對 JSON 提供了良好的支持干跛。具體的相關(guān)函數(shù)可參考：JSON類型和函數(shù)

從使用的角度而言，個人覺得常見的應(yīng)用場景為：

讀取單個 JSON 的屬性值

遍歷單個 JSON 的所有屬性

遍歷一個 JSON 數(shù)組

創(chuàng)建一個 JSON 作為返回值

之所以僅這些簡單的場景祟绊，原因在于楼入，在應(yīng)用中使用高級語言處理 JSON 與在數(shù)據(jù)庫中使用那些高級的 JSON 函數(shù)相比，從操作上和可讀性上均爽很多牧抽。在不支持 JSON 的數(shù)據(jù)庫中嘉熊，我們也常使用單個文本字段存儲 JSON 字符串，然后在應(yīng)用中加以解析處理扬舒。

四個場景

我們以以下的 JSON 字符串作為輸入阐肤，來了解 PostgreSQL 在各場景中的應(yīng)用實現(xiàn)。

{
    "label": {
        "names": ["Amy", "Kala", "Lily"]
    },
    "color": "red",
    "count": 3
}

// 寫成一行即是
{"label":{"names":["Amy","Kala","Lily"]},"color":"red","count":3}

1.讀取屬性

首先呼巴，通過下面的表格，感受一下 JavaScript 與 PostgreSQL 中讀取 color 屬性與 label 屬性中 names 的第二個值的形式御蒲。

讀屬性	JavaScript	PostgreSQL
定義	var jsonObj = {"label":{"names":["Amy","Kala","Lily"]}, "color":"red","count":3};	jsonObj := '{"label":{"names":["Amy","Kala","Lily"]}, "color":"red","count":3}'::json;
讀取 JSON 的 color 屬性	jsonObj.color	jsonObj -> 'color'
讀取 JSON 的 label 中 names 的第二個值	jsonObj.label.names[1]	jsonObj -> 'label' -> 'names' -> 1

在 PostgreSQL 中我們可以使用以下語句逐層指定屬性路徑（屬性名稱需要使用字符串需單引號衣赶，數(shù)組索引使用數(shù)字）來獲取值：

-- 取得 color 屬性
SELECT '{"label":{"names":["Amy","Kala","Lily"]},"color":"red","count":3}'::json 
       -> 'color';

-- 取得 label 屬性下的 names 的第二個值
SELECT '{"label":{"names":["Amy","Kala","Lily"]},"color":"red","count":3}'::json 
       -> 'label' -> 'names' -> 1;

此時取得的值仍然為 json 類型，如果需要取得值的文本形式厚满，則把最后一個 "->" 變成 "->>" 即可府瞄。

當(dāng)然，路徑的表示碘箍，也可以通過 #> '{label,names,1}' 的形式表示：

SELECT '{"label":{"names":["Amy","Kala","Lily"]},"color":"red","count":3}'::json 
       #> '{label,names,1}';

2.遍歷屬性

使用 json_each 函數(shù)遵馆，即可返回屬性鍵值對的數(shù)據(jù)集，數(shù)據(jù)集包括兩列丰榴，key 表示屬性货邓，value 表示屬性值。如下語句輸出所有結(jié)果：

DO $$
DECLARE 
    lv_row record; 
    jsonObj json := '{"label":{"names":["Amy","Kala","Lily"]},"color":"red","count":3}'::json;
BEGIN   
    FOR lv_row IN SELECT * FROM json_each(jsonObj) LOOP  
        raise notice 'key is %, value is %', lv_row.key, lv_row.value;
    END LOOP;  
END $$;

輸出

NOTICE: key is label, value is {"names":["Amy","Kala","Lily"]}

NOTICE: key is color, value is "red"

NOTICE: key is count, value is 3

3.遍歷數(shù)組

通過使用 json_array_length 函數(shù)獲取數(shù)組的長度四濒，然后根據(jù)索引遍歷整個數(shù)組即可换况。

DO $$
DECLARE 
    lv_row record; 
    lv_size int;
    jsonObj json := '{"label":{"names":["Amy","Kala","Lily"]},"color":"red","count":3}'::json;
BEGIN
    -- 取得label 下names 這個json數(shù)組
    jsonObj := jsonObj #> '{label,names}';
    -- 取得數(shù)組的長度
    lv_size := json_array_length(jsonObj);
    -- 按索引遍歷整個數(shù)組
    FOR i IN 0..lv_size-1 LOOP
        raise notice '%', jsonObj -> i;
    END LOOP;
END $$;

輸出：

NOTICE: "Amy"

NOTICE: "Kala"

NOTICE: "Lily"

4.創(chuàng)建一個 JSON

使用 json_build_object 函數(shù)，傳遞 key, value 成對的參數(shù)即可創(chuàng)建一個 json盗蟆，如以下語句形成一個 api 常用的返回執(zhí)行情況的 json戈二。

SELECT json_build_object('code', 200, 'err_msg', 'run success!');

應(yīng)用示例

我們以填寫學(xué)生地址為例，傳遞給存儲過程的是一個 json 數(shù)組喳资，每個數(shù)組中的 json 對象包括了學(xué)生標(biāo)識與地址信息觉吭。

以下語句創(chuàng)建數(shù)據(jù)表

-- student_id 學(xué)生標(biāo)識, address 地址
CREATE TABLE student_address (student_id varchar(10) PRIMARY KEY, address varchar(100));

image.gif

以下為處理過程

CREATE OR REPLACE FUNCTION save_student_addresses_json( 
    v_array_json json)
    RETURNS json
    LANGUAGE 'plpgsql' 
AS $$
DECLARE 
    lv_row_json json; 
    lv_length int;
    lv_field_student_id varchar;
    lv_field_address    varchar;
BEGIN   
    -- 取得數(shù)組的長度
    lv_length := json_array_length(v_array_json);
    FOR i IN 0..lv_length-1 LOOP
        -- 取得第 i 行的 json 值
        lv_row_json := v_array_json -> i;
        lv_field_student_id := lv_row_json ->> 'student_id';
        lv_field_address := lv_row_json ->> 'address';

        -- 插入學(xué)生地址信息，如果存在則更新地址
        INSERT INTO student_address (student_id, address)
        VALUES (lv_field_student_id, lv_field_address)
            ON CONFLICT (student_id) 
        DO UPDATE SET address = excluded.address;
    END LOOP; 

    RETURN json_build_object(
        'err_code', 200, 
        'err_msg', '保存或更新 ' || lv_length || ' 條記錄'
    ); 
end 
$$

我們執(zhí)行以下操作

SELECT save_student_addresses_json(
    '[
     {"student_id":"01","address":"街道A"},
     {"student_id":"02","address":"街道B"}
    ]'
);

運行結(jié)果：{"err_code":200,"err_msg":"保存或更新 2 條記錄"}

小結(jié)一下

PostgreSQL 對 JSON 的操作支持特性很豐富仆邓，但文檔中那么多函數(shù)一下映入眼簾鲜滩，讓人覺得復(fù)雜凌亂伴鳖。本文從簡單易理解的幾個應(yīng)用場景出發(fā)，希望能先爽上一把绒北，而后再細細深入黎侈。I love PostgreSQL!