PostgreSQL之jsonb

PostgreSQL 的 jsonb 数据类型是 PostgreSQL 从版本 9.4 开始引入的一种用于存储 JSON 数据的二进制格式。与传统的 json 类型不同，jsonb 在存储之前会解析输入的 JSON 数据，并将其转换为一种优化过的内部表示形式。这种处理方式使得 jsonb 在查询性能上通常优于 json 类型，因为它不需要在每次访问时重新解析数据。

一、存储特性

• 紧凑性：jsonb 删除了不必要的空格、不保留对象键的顺序，并且不会保留重复的键值对，仅保存最后一个出现的键值对。这意味着 jsonb 的存储更加紧凑，同时也更易于处理。
• 效率：由于 jsonb 是以二进制格式存储的，所以在进行查询操作时不需要再次解析，这提高了读取速度。然而，在写入或更新时，因为需要做额外的转换工作，所以相对 json 类型来说可能会稍慢一些。

二、查询与索引

• 操作符和函数：PostgreSQL 提供了一系列丰富的操作符和函数来处理 jsonb 数据，例如 ->, ->>, @>, <@, ?, ?|, ?& 等等，这些可以用来提取、过滤、比较 JSON 数据中的元素。
• 索引支持：jsonb 支持创建 GIN（Generalized Inverted Indexes）索引，这对于加速包含大量文档的集合上的复杂查询非常有用。此外，还有两种不同的 GIN 操作符类 jsonb_ops 和 jsonb_path_ops 可用于创建索引，前者会对每个 key 和 value 创建索引项，而后者只为每个 value 创建索引项。

三、使用场景

jsonb 类型非常适合用于存储非结构化或半结构化的数据，尤其是在需求变化频繁的应用中。它允许开发者灵活地设计数据库模式，同时保持良好的查询性能。

四、示例代码

SELECT *
FROM table
WHERE up_year @> '["undefined"]';
UPDATE table SET up_year='["2023"]' WHERE up_year @> '["undefined"]';
UPDATE table SET brand_name = NULL WHERE brand_name @> '[null]';