PostgreSQL 务实应用（四/5）JSON

JSON 可谓风靡互联网，在数据交换使用上，其优势特别明显，其结构简洁、可读易读、形式灵活。很多 API 接口的数据都采用 JSON 来表示。

PostgreSQL 对 JSON 提供了良好的支持。具体的相关函数可参考：JSON类型和函数

从使用的角度而言，个人觉得常见的应用场景为：

1. 读取单个 JSON 的属性值
2. 遍历单个 JSON 的所有属性
3. 遍历一个 JSON 数组
4. 创建一个 JSON 作为返回值

之所以仅这些简单的场景，原因在于，在应用中使用高级语言处理 JSON 与在数据库中使用那些高级的 JSON 函数相比，从操作上和可读性上均爽很多。在不支持 JSON 的数据库中，我们也常使用单个文本字段存储 JSON 字符串，然后在应用中加以解析处理。

四个场景

我们以以下的 JSON 字符串作为输入，来了解 PostgreSQL 在各场景中的应用实现。

{
    "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 中我们可以使用以下语句逐层指定属性路径（属性名称需要使用字符串需单引号，数组索引使用数字）来获取值：

-- 取得 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 类型，如果需要取得值的文本形式，则把最后一个 "->" 变成 "->>" 即可。

当然，路径的表示，也可以通过 #> '{label,names,1}' 的形式表示：

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

2.遍历属性

使用 json_each 函数，即可返回属性键值对的数据集，数据集包括两列，key 表示属性，value 表示属性值。如下语句输出所有结果：

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.遍历数组

通过使用 json_array_length 函数获取数组的长度，然后根据索引遍历整个数组即可。

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

输出：

NOTICE: "Amy"

NOTICE: "Kala"

NOTICE: "Lily"

4.创建一个 JSON

使用 json_build_object 函数，传递  key, value 成对的参数即可创建一个 json，如以下语句形成一个 api 常用的返回执行情况的 json。

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

应用示例

我们以填写学生地址为例，传递给存储过程的是一个 json 数组，每个数组中的 json 对象包括了学生标识与地址信息。

以下语句创建数据表

-- student_id 学生标识, address 地址
CREATE TABLE student_address (student_id varchar(10) PRIMARY KEY, address varchar(100));

以下为处理过程

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 	
    -- 取得数组的长度
    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';

        -- 插入学生地址信息，如果存在则更新地址
        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 
$$

我们执行以下操作

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

运行结果：{"err_code":200,"err_msg":"保存或更新 2 条记录"}

小结一下

PostgreSQL 对 JSON 的操作支持特性很丰富，但文档中那么多函数一下映入眼帘，让人觉得复杂凌乱。本文从简单易理解的几个应用场景出发，希望能先爽上一把，而后再细细深入。I love PostgreSQL!