《PostgreSQL 开发指南》第 31 篇 JSON 文档存储
发布网友
发布时间：2024-10-09 01:10
共1个回答
热心网友
时间：2024-10-13 10:03
如果想要学习和了解 JSON，推荐 JSON 官方网站。
JSON 数据类型
PostgreSQL 提供了两种 JSON 数据类型：JSON 以及 JSONB。这两种类型主要的区别在于数据存储格式，JSONB 使用二进制格式存储数据，更易于处理。
PostgreSQL 推荐优先选择 JSONB 数据类型。
下表描述了两种数据类型之间的区别：
由于存储格式的不同，JSONB 输入时稍微慢一些（需要转换），但是查询时快很多。
接下来的内容主要使用 JSONB 数据类型，但是大部分功能也可以使用 JSON 数据类型。
定义 JSON 字段
首先创建一个产品表 product：
产品表 product 中包含一个 JSONB 类型的字段 attributes，用于存储产品的属性。
JSON 字段赋值
我们可以直接使用字符串为 JSON 字段赋值，但是要求数据必须是有效的 JSON 格式，否则将会返回错误。
执行以下语句插入一条产品记录：
接下来我们插入一条不符合 JSON 格式的数据：
以下语句插入了一条包含 JSON 数组的产品信息：
我们使用同样的方法再创建一条记录：
以上方法虽然使用简单，但是输入比较麻烦。为此，PostgreSQL 提供了一些方便生产 JSON 数据的函数。
jsonb_build_object 函数可以通过一系列输入创建二进制的 JSON 对象，例如：
我们可以利用该函数插入数据，而不需要手动输入方括号、逗号、冒号等 JSON 符号。例如：
其他常用的构建 JSON 数据的函数如下：
查询 JSON 字段数据
JSON 字段的查询和普通字段没有什么区别，例如：
获取单个属性
我们不仅可以查询整个 JSON 字段，也可以提取 JSON 数据中指定节点的属性值。例如：
运算符 -> 可以通过指定节点的键获取相应的数据。这种方法返回的数据仍然是 JSON 类型，使用双引号包含。
如果想要以字符串形式返回节点中的数据值，可以使用运算符 ->>. 例如：
如果查询的 JSON 节点不存在，将会返回空值：
获取数组属性
属性 drawers 是一个 JSON 数组，我们同样可以查询它的内容：
属性 drawers 包含了 2 个元素，每个元素代表一个抽屉，每个抽屉都拥有 2 个属性。
如果我们想要查看属性 drawers 中的第一个元素，可以多次使用 -> 运算符：
第一个 -> 运算符返回了 drawers 属性，第二个 -> 运算符返回了该属性中的第 1 个数组元素（数组下标从 0 开始）。
我们也可以使用另外两个运算符获取嵌套的属性，例如：
运算符 #> 以及 #>> 可以通过指定 JSON 节点的路径获取嵌套属性，路径可以包含键的名称或者数组元素下标，返回类型分别为 JSON 和字符串。
基于 JSON 数据的过滤
如果我们想要查看颜色为棕色、材料为实木、高度为 60cm 的椅子，可以尝试使用以下查询语句：
查询返回了我们期望的结果，因为查询条件中的字符串和 attributes 字段完全匹配，我们提供了完整的属性信息。
如果我们只想要基于某个属性（例如颜色）查找产品，这种方法就无法返回正确的数据了。例如：
这种情况下我们可以使用前文查询属性的方法，例如：
我们使用了 ->> 运算符，而不是 -> 运算符，因为前者返回的是字符串类型，后者返回的则是 JSON 数据类型。
JSON 转换为数据行
PostgreSQL 支持将 JSON 字段转换为数据行格式。例如，jsonb_each 函数可以将每个键值对转换为一个记录：
与此类似的函数还有：
我们还可以使用 json_object_keys 或者 jsonb_object_keys 函数获取 JSON 字段中的所有键的名称：
判断属性是否存在
PostgreSQL 还提供了一些用于判断 JSON 属性是否存在的运算符，例如 ? 运算符。
以下语句可以查找拥有 drawers 属性的产品：
其他存在性运算符可以参考 官方文档。
更新 JSON 字段数据
使用 UPDATE 语句更新 JSON 字段时，可以通过 || 运算符将新的键值增加到已有 JSON 数据。例如：
新的属性 width 被添加到了数据的中间，因为 JSONB 数据类型不会保留键的顺序。
另外一种方法就是利用 jsonb_insert 方法，例如：
如果想要更新已有键的数值，可以使用 jsonb_set 函数。例如：
以上语句将 id 等于 1 的产品的属性 height 修改为 75cm。
删除 JSON 字段数据
删除整个 JSON 字段数据可以简单地将其设置为 NULL，例如：
删除 JSON 字段中的某个属性可以使用 - 运算符，例如：
产品 1 中的 height 属性已经被删除了。
另一种删除 JSON 属性的方法是利用 jsonb_set_lax 函数，例如
函数中的第三个参数表示将 width 属性设置为空，第四个参数表示属性不存在时不创建新的属性，第五个参数表示删除被设置为空的属性。
全文索引
当我们基于 JSON 属性查询数据时，可能会存在性能问题。我们生成一批产品数据：
然后查看以下语句的执行计划：
以上语句表示查找拥有黑色的产品。执行计划显示使用了全表顺序扫描，数据量很小的时候没有问题。但是对于数据量大的表，查询速度可能会很慢。
为此，PostgreSQL 提供了支持 JSON 字段的全文索引，可以优化查询的性能。这种索引的类型为 GIN（通用倒排索引），通常用于搜索引擎。
我们可以基于 JSON 字段创建一个全文索引：
关键字 USING GIN 用于指定索引类型。
再次查看执行计划：
此时执行计划使用了索引扫描，成本从 258.93 下降到了 24.01。
