PostgreSQL 务实应用（四/5）JSON

2021-12-17 10:05:22

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

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

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

读取单个 JSON 的属性值

遍历单个 JSON 的所有属性

遍历一个 JSON 数组

创建一个 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!

码农公寓