您还可以查看我们与 PostgresConf 合作的在 PostgreSQL 与 MongoDB 中使用 JSON 数据网络研讨会以了解有关该主题的更多信息,并查看我们的 SlideShare 页面下载幻灯片。
为什么在 PostgreSQL 中存储 JSON?
JSONB 模式和反模式
JSONB 数据结构
JSONB 运算符和函数
JSONB 索引
SQL/JSON & JSONPath
为什么关系数据库还要关心非结构化数据?事实证明,它在一些场景中很有用。
使用 JSON 格式存储数据的主要原因之一是架构灵活性。当您的架构是流动的并且经常更改时,将您的数据存储在 JSON 中很有用。如果您将每个键存储为列,则会导致频繁的 DML 操作 - 当您的数据集很大时,这可能会很困难 - 例如,事件跟踪、分析、标签等。注意:如果始终存在特定键在您的文档中,将其存储为第一类列可能是有意义的。我们将在下面的“JSON 模式和反模式”部分讨论更多关于这种方法的信息。
2.####嵌套对象
如果您的数据集具有嵌套对象(单级或多级),在某些情况下,使用 JSON 处理它们比将数据非规范化为列或多个表更容易。
3.####与外部数据源同步
外部系统经常以 JSON 格式提供数据,因此它可能是在将数据摄取到系统的其他部分之前的临时存储。例如,Stripe 事务。
中的 JSON 支持 PostgreSQL是在 9.2 中引入的,并且在以后的每个版本中都稳步改进。
9.2 中的 JSON 数据库相当有限(当时可能被夸大了)——基本上是一个带有一些 JSON 验证的美化字符串。验证传入的 JSON 并存储在数据库中很有用。下面提供了更多详细信息。
JSONB 代表“JSON 二进制”或“JSON 更好”,具体取决于您询问的对象。它是一种用于存储 JSON 的分解二进制格式。 JSONB 支持对 JSON 数据进行索引,在解析和查询 JSON 数据方面非常高效。在大多数情况下,当您在 PostgreSQL 中使用 JSON 时,您应该使用 JSONB。
JSONPath 为 PostgreSQL 带来了强大的 JSON 查询引擎。
在大多数情况下,您应该使用 JSONB。但是,在某些特定情况下 JSON 效果更好:
JSON 保留原始格式(也称为空格)和键的顺序。
JSON 保留重复键。
JSON 比 JSONB 更快地摄取 - 但是,如果您进行任何进一步的处理,JSONB 会更快。
例如,如果您只是摄取 JSON 日志而不以任何方式查询它们,那么 JSON 可能是您更好的选择。就本博客而言,当我们提到 PostgreSQL 中的 JSON 支持时,我们将指的是 JSONB。
如果 PostgreSQL 对 JSONB 有很好的支持,为什么我们还需要列呢?为什么不直接使用 JSONB blob 创建一个表并删除所有列,如下面的模式:
归根结底,列仍然是处理数据的最有效技术。 JSONB 存储与传统列相比有一些缺点:
PostgreSQL 维护有关表中每一列中值分布的统计信息 - 最常见值 (MCV)、NULL 条目、分布直方图。基于这些数据,PostgreSQL 查询计划器对用于查询的计划做出明智的决策。此时,PostgreSQL 不存储 JSONB 列或键的任何统计信息。这有时会导致糟糕的选择,例如使用嵌套循环连接与哈希连接等。此博客文章中提供了更详细的示例 -When To Avoid JSONB In A PostgreSQL Schema。
JSONB 存储不会对 JSON 中的键名进行重复数据删除。与 WiredTiger 上的 MongoDB BSON 或传统列存储相比,这可能会导致更大的存储空间。我使用以下 JSONB 模型运行了一个简单的测试,该模型存储了大约 1000 万行数据,结果如下——在某些方面,这类似于 MongoDB MMAPV1 存储模型,其中 JSONB 中的键按原样存储,没有任何压缩。一个长期的解决方法是将键名移动到表级字典并引用该字典,而不是重复存储键名。在那之前,解决方法可能是使用更紧凑的名称(unix 样式)而不是更具描述性的名称。例如,如果您要存储数百万个特定密钥的实例,则将其命名为“pb”而不是“publisherName”会更好。
在 PostgreSQL 中利用 JSONB 的最有效方法是结合列和 JSONB。如果一个键在 JSONB blob 中出现频率很高,最好将其存储为列。使用 JSONB 作为“包罗万象”来处理架构的可变部分,同时将传统列用于更稳定的字段。
JSONB 和 MongoDB BSON 本质上都是树结构,使用多级节点来存储解析后的 JSONB 数据。 MongoDB BSON 具有非常相似的结构。
存储的另一个重要考虑因素是 JSONB 如何与 TOAST(超大属性存储技术)交互。通常,当列的大小超过 TOAST_TUPLE_THRESHOLD(默认为 2kb)时,PostgreSQL 将尝试压缩数据并适合 2kb。如果这不起作用,则将数据移至离线存储。这就是他们所说的“TOASTing”数据。获取数据时,需要进行反向过程“deTOASTting”。您还可以控制 TOAST 存储策略:
扩展 - 允许离线存储和压缩(使用 pglz)。这是默认选项。
外部 - 允许离线存储,但不允许压缩。
如果您因 TOAST 压缩或解压缩而遇到延迟,一种选择是主动将列存储设置为“扩展”。有关所有详细信息,请参阅此PostgreSQL 文档。
PostgreSQL 提供了多种操作符来处理 JSONB。从文档:
操作员
描述
->
获取 JSON 数组元素(从零开始索引,负整数从末尾开始计数)
->
按键获取JSON对象字段
->>
获取 JSON 数组元素作为文本
->>
获取 JSON 对象字段作为文本
#>
获取指定路径的 JSON 对象
#>>
获取指定路径的 JSON 对象作为文本
@>
左侧 JSON 值是否在顶层包含正确的 JSON 路径/值条目?
左侧 JSON 路径/值条目是否包含在右侧 JSON 值的顶层?
string 是否作为 JSON 值中的顶级键存在?
?|
这些数组_strings_中的任何一个是否作为顶级键存在?
?&
所有这些数组 strings 是否都作为顶级键存在?
||
将两个 jsonb 值连接成一个新的 jsonb 值
从左操作数中删除键/值对或 string 元素。键/值对根据其键值进行匹配。
从左操作数中删除多个键/值对或 string 元素。键/值对根据其键值进行匹配。
删除具有指定索引的数组元素(负整数从末尾开始计数)。如果顶级容器不是数组,则会引发错误。
#-
删除指定路径的字段或元素(对于JSON数组,负整数从末尾开始计数)
@?
JSON 路径是否返回指定 JSON 值的任何项目?
@@
返回指定 JSON 值的 JSON 路径谓词检查结果。仅考虑结果的第一项。如果结果不是布尔值,则返回 null。
PostgreSQL 还提供了多种创建函数和处理函数来处理 JSONB 数据。
JSONB 提供了广泛的选项来索引您的 JSON 数据。在高层次上,我们将深入研究 3 种不同类型的索引 - GIN、BTREE 和 HASH。并非所有索引类型都支持所有运算符类,因此需要根据您计划使用的运算符和查询类型来设计索引。
GIN 代表“广义倒排索引”。从文档:
“GIN 是为处理要索引的项目是复合值的情况而设计的,并且要由索引处理的查询需要搜索出现在复合项目中的元素值。例如,项目可以是文档,而查询可以是搜索包含特定单词的文档。”
GIN 支持两种操作符类:
jsonb_ops (默认) - ?, ?|, ?&, @>, @@, @? [索引 JSONB 元素中的每个键和值]
jsonb_pathops - @>,@@,@? [仅索引 JSONB 元素中的值]
这些运算符可用于检查 JSONB 中是否存在顶级键。让我们在数据 JSONB 列上创建一个 GIN 索引。例如,查找所有提供盲文的书籍。 JSON 看起来像这样:
从解释输出中可以看出,我们创建的 GIN 索引正在用于搜索。如果我们想找到盲文或精装书怎么办?
GIN 索引仅支持“顶级”键上的“存在”运算符。如果键不在顶层,则不会使用索引。它将导致顺序扫描:
检查嵌套文档中是否存在的方法是使用“表达式索引”。让我们在 data->tags 上创建一个索引:
注意:这里的替代方法是使用 @> 运算符:
但是,这仅在值是对象时才有效。因此,如果您不确定该值是对象还是原始值,则可能会导致错误的结果。
“路径”运算符可用于 JSONB 数据的多级查询。让我们使用它类似于 ?上面的运算符:
路径运算符支持查询嵌套对象或顶级对象:
查询也可以是多级的:
GIN 还支持“pathops”选项来减小 GIN 索引的大小。当您使用 pathops 选项时,唯一的运算符支持是“@>”,因此您需要小心查询。从文档:
“jsonb_ops 和 jsonb_path_ops GIN 索引的技术区别在于前者为数据中的每个键和值创建独立的索引项,而后者只为数据中的每个值创建索引项”
您可以按如下方式创建 GIN pathops 索引:
在我的 100 万本书的小型数据集上,您可以看到 pathops GIN 索引更小 - 您应该使用您的数据集进行测试以了解节省的情况:
让我们使用 pathops 索引重新运行之前的查询:
<
code>demou003d# select * from books where data @&gt; '{"标签":{"nk455671":{"ik937456":"iv506075"}}}'::jsonb;
编号 |作者 |伊斯本 |评级 |数据
](#)
--------------+-----------------+------------+---- ----+--------------------------------------------- -------------------------------------------------- -------------------------------------------------- -----
1000005 | XEI7xShT8bPu6H7 | 2kD5XJDZUF | 0 | {“标签”:{“nk455671”:{“ik937456”:“iv506075”}},“盲文”:真,“关键字”:\ [“abc”,“kef”,“keh”],“精装书” :假,“出版商”:“zSfZIAjGGs”,“
批评”:4}
(1 行)
demou003d#解释 select * from books where data @&gt; '{"标签":{"nk455671":{"ik937456":"iv506075"}}}'::jsonb;
](#查询计划)
查询计划
书籍上的位图堆扫描(成本u003d12.75..1005.25 行u003d1000 宽度u003d158)
重新检查 Cond: (data @&gt; '{"tags": {"nk455671": {"ik937456": "iv506075"}}}'::jsonb)
-&amp;gt; dataginpathops 上的位图索引扫描(成本u003d0.00..12.50 行u003d1000 宽度u003d0)
索引条件:(数据@&gt; '{"tags": {"nk455671": {"ik937456": "iv506075"}}}'::jsonb)
(4 行)
但是,如上所述,“pathops”选项并不支持默认操作符类支持的所有场景。使用“pathops” GIN 索引,所有这些查询都无法利用 GIN 索引。总而言之,您的索引较小,但它支持的用例更有限。
B树索引是关系数据库中最常见的索引类型。但是,如果您使用 B 树索引对整个 JSONB 列进行索引,则唯一有用的运算符是“u003d”、<、<u003d、>、>u003d。本质上,这只能用于整个对象的比较,其用例非常有限。
好吧,这不起作用,因为 '->' 运算符返回 JSONB 类型。所以我们需要使用这样的东西:
如果您使用的是 PostgreSQL 11 之前的版本,它会变得更加丑陋。您需要先查询为文本,然后将其转换为整数:
对于表达式索引,索引需要与查询表达式完全匹配。所以,我们的索引看起来像这样:
从上面我们可以看到 BTREE 索引正在按预期使用。
如果您只对“u003d”运算符感兴趣,那么哈希索引就会变得有趣。例如,考虑我们正在寻找一本书上的特定标签的情况。要索引的元素可以是顶级元素或深度嵌套。
例如。标签->发布者 u003d XlekfkLOtL
哈希索引的大小也往往小于 B-tree 或 GIN 索引。当然,这最终取决于您的数据集。
PostgreSQL 支持使用 trigram 索引进行字符串匹配。 Trigram 索引通过将文本分解为 trigram 来工作。三元组基本上是分解成 3 个字母序列的单词。更多信息可以在文档中找到。 GIN 索引支持“gin_trgm_ops”类,可用于索引 JSONB 中的数据。您可以选择使用表达式索引在特定列上构建三元组索引。
正如您在上面的查询中看到的,我们可以搜索出现在任何药水上的任意字符串。与 B 树索引不同,我们不限于左锚定表达式。
JSONB 对索引数组有很好的内置支持。让我们考虑一个使用 GIN 索引对字符串数组进行索引的示例,当我们的 JSONB 数据包含“关键字”元素并且我们希望查找具有特定关键字的行时:
右侧数组中项目的顺序无关紧要。例如,以下查询将返回与前一个相同的结果:
包含运算符右侧数组中的所有元素都需要存在 - 基本上就像“AND”运算符。如果你想要“OR”行为,你可以在 WHERE 子句中构造它:
有关包含数组的包含运算符的行为的更多详细信息,请参见文档。
SQL 标准在 SQL - SQL/JSON Standard-2016 中添加了对 JSON 的支持。在 PostgreSQL 12/13 版本中,PostgreSQL 拥有 SQL/JSON 标准的最佳实现之一。有关更多详细信息,请参阅PostgreSQL 12 公告。
SQL/JSON 的核心特性之一是支持 JSONPath 语言查询 JSONB 数据。 JSONPath 允许您指定一个表达式(使用类似于 Javascript 中的属性访问表示法的语法)来查询您的 JSONB 数据。这使其简单直观,但查询 JSONB 数据也非常强大。将 JSONPath 视为 XML 的 XPath 的逻辑等价物。
。钥匙
返回具有指定键的对象成员。
[*]
返回所有数组元素的通配符数组元素访问器。
.*
通配符成员访问器,返回位于当前对象顶层的所有成员的值。
.**
递归通配符成员访问器,它处理当前对象的所有级别的 JSON 层次结构并返回所有成员值,而不管它们的嵌套级别如何。
有关运算符的完整列表,请参阅JSONPath 文档。 JSONPath 还支持多种过滤器表达式。
PostgreSQL 12 提供了几个函数来使用 JSONPath 来查询你的 JSONB 数据。从文档:
jsonb_path_exists - 检查 JSONB 路径是否返回指定 JSON 值的任何项目。
jsonb_path_match - 返回指定 JSONB 值的 JSONB 路径谓词检查结果。仅考虑结果的第一项。如果结果不是布尔值,则返回 null。
jsonb\path\query - 获取指定 JSONB 值的 JSONB 路径返回的所有 JSON 项。此函数还有几个其他变体可以处理对象数组。
让我们从一个简单的查询开始——按出版商查找书籍:
您可以将此表达式重写为 JSONPath 过滤器:
您还可以使用非常复杂的查询表达式。例如,让我们选择印刷风格 u003d 精装且价格 u003d 100 的书籍:
但是,此时对 JSONPath 的索引支持非常有限 - 这使得在 where 子句中使用 JSONPath 很危险。 JSONPath 对索引的支持将在后续版本中得到改进。
JSONPath 的另一个很好的用例是从匹配的行中投影部分 JSONB。请考虑以下示例 JSONB:
仅选择发布者字段:
选择打印字段(这是一个对象数组):
选择数组中的第一个元素打印:
选择数组中的最后一个元素打印:
从阵列中仅选择精装印刷品:
我们还可以链接过滤器:
总之,PostgreSQL 提供了一个强大且多功能的平台来存储和处理 JSON 数据。您需要注意几个问题,但我们乐观地认为它会在未来的版本中得到修复。
PostgreSQL 图形用户界面 (GUI) 工具帮助这些开源数据库用户管理、操作和可视化他们的数据。在这篇文章中,我们将讨论用于管理 PostgreSQL 部署的前 5 个 GUI 工具。了解更多
在现代应用程序中,客户端会打开很多连接。不鼓励开发人员在进行其他操作时保持数据库连接。 “尽可能晚地打开连接,尽快关闭”。了解更多
PostgreSQL社区为您提供最前沿的新闻资讯和知识内容
更多推荐
PostgreSQL 计数查询效率,物化视图 [重复]
问题:PostgreSQL 计数查询效率,物化视图 [重复] 可能重复: PostgreSQL 计数查询优化 使用 PostgreSQL 9.2,我们试图弄清楚是否有一种方法可以跟踪查询的结果数量,并以有效的方式返回该数字。这个查询应该每秒执行几次(可能几十到几百甚至几千次)。我们现在的查询看起来像这样,但我们想知道这是否效率低下: -- Get # of rows that do not hav
多对多中的唯一性
问题:多对多中的唯一性 我无法弄清楚谷歌的哪些术语,所以帮助标记这个问题或只是以相关问题的方式向我指出会有所帮助。 我相信我有一个典型的多对多关系: CREATE TABLE groups ( id integer PRIMARY KEY); CREATE TABLE elements ( id integer PRIMARY KEY); CREATE TABLE groups_elements