当前位置：首页 > news >正文

JSONB字段搜索走索引

news 来源：原创 2024/9/20 10:42:20

JSONB介绍

PostgreSQL 数据库提供了一种特殊的数据类型叫做 JSONB，它是对 JSON 对象的原生支持，允许在数据库中存储和查询 JSON 数据。JSONB 是 JSON 类型的二进制版本，其相对于 JSON 类型来说，具有更高的效率和更好的性能。

以下是 JSONB 字段的一些特性和使用方式的详细介绍：

数据存储：
- JSONB 数据在存储时会被转换成一个紧凑的内部二进制格式，这样并不会消耗大量的空间，而且查询速度更快。
- 由于 JSONB 比较紧凑，因此在保存密集关键字和值的 JSON 数据时，与传统的表格数据存储相比，可以节省大量的空间。
模式匹配与搜索：
- 你可以使用 SQL 查询对 JSONB 数据进行模式匹配搜索，这使得数据查询更加灵活。
- 可以通过 PostgreSQL 的文本搜索功能整合 JSONB 字段，使得复杂的数据查询更加方便。
索引支持：
- PostgreSQL 支持对 JSONB 字段中的键和值进行索引。这意味着在查询中对 JSONB 字段进行操作时，索引可以显著提升查询性能。
- 用户可以为 JSONB 字段创建多种索引，例如键索引、路径索引等，从而在查询时提供更快的数据访问速度。
函数和操作符：
- PostgreSQL 提供了一系列的专门用于 JSONB 的函数和操作符，如：
  - jsonb_typeof：返回 JSONB 值的类型。
  - jsonb_to_record：将 JSONB 对象转换为记录类型，以便访问其各个字段。
  - jsonb_set：用于更新或插入 JSONB 字段的键。
  - ->>：用于获取 JSONB 对象中键的值，它相当于文本模式的 ‘->’ 和 ‘>’。
复杂的结构处理：
- JSONB 支持复杂的层次结构，可以通过指针（#> 和 ->）操作符以及 JSONB 函数来处理这些结构。
- 这不仅适用于 JSONB 对象，还适用于数组等复杂结构。
动态SQL：
- JSONB 可以非常容易地与动态SQL结合使用，为数据提供灵活性的同时，也保持了数据库设计的灵活性。
关系操作：
- 若在 JSONB 数据中需要进行自引用或与关系表中的其他条目相关联的复杂场景，JSONB 可以成为一种方便的解决方案。

🎆【核心内容】JSONB字段搜索走索引

JSONB字段精确搜索还是可以走GIN索引的，但是对于模糊搜索不太好支持；

🧨创建索引

首先如果你需要精确搜索，首先建议创建一个索引

CREATE INDEX idx_resource_extension ON tb_test USING GIN (test_extension);

说明：

idx_resource_extension:索引名称
tb_test：表名
test_extension：JSONB字段名称

🧨编写搜索sql

对于字符串类型的子字段：

SELECT *
FROM tb_test
WHERE(test_extension @> '{"coins":["USDC"]}' or test_extension @> '{"coins":"USDC"}')

这里查询使用了test_extension @> ‘{“coins”:[“USDC”]}’ or test_extension @> ‘{“coins”:“USDC”}’，至于为什么会使用or连接2个查询条件，我想对于json类型字符串就是列表类型或单个字符串；

对于数字类型：

SELECT *
FROM tb_test
WHERE(test_extension @> '{"styleNumb":[9991]}' or resource_extension @> '{"styleNumb":9991}');

这里与上面字符串类型处理一样，但是对于是使用字符串类型还是数字类型，可在代码中进行提前判断，从而选择合适的sql，如果有问题可以进入我的博客给我留言哦~

其他补充说明

@>的含义与用法

在 PostgreSQL 中，@> 是 JSONB 类型的操作符，用于判断一个 JSONB 对象是否包含另一个 JSONB 对象。它是一个用于 JSONB 数据类型的包含操作符，用于检测左侧 JSONB 值是否包含右侧 JSONB 值。

用法与示例

基本语法：
```
jsonb_column @> jsonb_value
```
其中 jsonb_column 是一个 JSONB 列，jsonb_value 是一个 JSONB 值。如果 jsonb_column 包含 jsonb_value，则结果为 TRUE，否则为 FALSE。
示例：

假设有一个 tb_common_resource 表，其中有一个 JSONB 类型的列 resource_extension，列中的数据如下：
```
[{"id": 1,"name": "Alice","tags": ["employee", "manager"]},{"id": 2,"name": "Bob","tags": ["employee"]}
]
```
如果我们想查询包含特定 JSON 对象的行，例如包含 "tags": ["employee"] 的行，可以使用 @> 操作符：
```
SELECT * FROM tb_common_resource
WHERE resource_extension @> '{"tags": ["employee"]}';
```
这个查询会返回所有 resource_extension 列中包含 "tags": ["employee"] 的行。在这个例子中，resource_extension 列中的 JSONB 数组包含两个对象，第一个对象包含 "tags": ["employee", "manager"]，第二个对象包含 "tags": ["employee"]。所以这个查询将返回所有这两行数据。
用法细节：
- 键值对匹配：@> 操作符可以用于匹配键值对，即在左侧 JSONB 对象中查找是否存在右侧 JSONB 对象中的所有键值对。
- 部分匹配：不需要完全匹配整个 JSON 对象，只要左侧 JSONB 对象中包含右侧 JSONB 对象中的所有键值对就可以。
应用场景：
- 筛选和查询：常用于筛选和查询包含特定数据的记录，特别是当 JSONB 列用于存储具有复杂结构的数据时。
- 数据验证：可以用于验证数据是否符合某些结构或条件。