JSONB字段搜索走索引

JSONB介绍

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

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

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

用法与示例

1. 基本语法：

   jsonb_column @> jsonb_value
   

   其中 jsonb_column 是一个 JSONB 列，jsonb_value 是一个 JSONB 值。如果 jsonb_column 包含 jsonb_value，则结果为 TRUE，否则为 FALSE。

2. 示例：

   假设有一个 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"]。所以这个查询将返回所有这两行数据。

3. 用法细节：

   • 键值对匹配：@> 操作符可以用于匹配键值对，即在左侧 JSONB 对象中查找是否存在右侧 JSONB 对象中的所有键值对。
   • 部分匹配：不需要完全匹配整个 JSON 对象，只要左侧 JSONB 对象中包含右侧 JSONB 对象中的所有键值对就可以。

4. 应用场景：

   • 筛选和查询：常用于筛选和查询包含特定数据的记录，特别是当 JSONB 列用于存储具有复杂结构的数据时。
   • 数据验证：可以用于验证数据是否符合某些结构或条件。