如何用JSONB更快地查询PostgreSQL中的JSON数据

在我作为产品经理的日常工作中，我经常要做一些数据探索以更好地了解我们的客户。

在我们的数据中，存储在一个postgreSQL数据库中，有许多字段被格式化为JSON，我经常要通过这些字段来划分我们的用户。

为此，我使用了JSONB，我想与你分享我在postgres中查询JSON时使用的小技巧和捷径。但首先，什么是JSONB？

什么是JSONB？

JSONB是一种用于存储JSON的结构化格式。JSONB不是以纯文本形式存储数据，而是以二进制格式存储数据。

JSON和JSONB都将允许你存储有效的JSON值。JSON和JSONB的主要区别将是写入和读取效率。由于JSON以全文形式存储数据，编写不需要开销，因此比JSONB稍快，因为JSONB需要将文本转换成二进制。然而，处理JSONB要比处理JSON快得多，因为它不需要重新解析数据。

你可以在PostgreSQL文档中了解更多。

在postgreSQL中查询JSONB

由于我们的PostgreSQL数据库涉及到对JSON数据的大量处理，我们将其存储为JSONB。因此我需要使用JSONB来处理这些JSON数据。那么，这是怎么做的呢？嗯，就像JSON一样，因为JSONB的优点是你可以像操作JSON一样操作它。

让我们考虑一下你的表mytable 中的一个字段myfield. (多么有创意！)

这个字段是一个JSON字段，其值为 。

{
"key1":"valueA",
"key2":"valueB"
} 

我想只选择存在 "key1 "并且等于 "valueA "的行。

这通常是用于基于一个值的分段。

选项1：使用@>操作数

SELECT *
FROM mytable
WHERE myfield @> '{"key1":"valueA"}'

选项2：使用->> 操作数

SELECT *
FROM mytable
WHERE myfield->>'key1'='valueA'

->> 操作数将以文本形式返回键key1 的值。你可以使用操作数-> 来返回一个数组。

当访问JSON字段中的值时，以下是我常用的操作数。

操作数	右边操作数类型	说明
->	文本	以数组形式返回与右操作数相关的值。
->>	文本	将与右操作数相关的值作为文本返回。
#>	数组	以数组形式返回特定JSON路径下的值。
#>>	数组	在一个特定的JSON路径下，将值作为文本返回。
@>	JSON字段	左边的JSON是否包含右边的JSON？
<@	JSON字段	右边的JSON包含左边的JSON吗？

我想只选择存在顶层'key3'的行。

操作数?用于检查顶层键的存在。

SELECT *
FROM mytable
WHERE myfield?'key3'

当检查一个JSON字段的存在性和包含性时，以下是我常用的操作数。

操作数	右边操作数类型	说明
?	文本	这个键是我的JSON字段中的顶级键吗？
?|	文本数组	这些键中的任何一个是我的JSON字段中的顶级键吗？
?&	文本数组	所有这些键都是我的JSON字段中的顶级键吗？

我想看看key1能有的所有值。

SELECT DISTINCT myfield->'key1'

或者，如果你想让它们变成文本

SELECT DISTINCT myfield->>'key1'

今天就到这里吧

你可以在PostgreSQL官方文档中找到更多信息。

想了解更多的PostgreSQL技巧吗？请看我们的Postgres骗局表

需要为PostgreSQL建立管理面板或GUI工具吗？ 查看PostgreSQL的森林管理