如何针对JSONB数据构建ActiveRecords查询及入门资源推荐

你要把现有json列转成jsonb，直接执行下面的SQL即可，不会丢失原有数据：

ALTER TABLE 你的目标表名 
ALTER COLUMN payload TYPE jsonb 
USING payload::jsonb;

转完之后jsonb会把数据解析成二进制结构存储，支持索引、丰富的操作符，查询效率比纯文本存储的json类型高很多。

jsonb 哈希结构指定项查询方法 #

日常查询记住几个核心操作符就能覆盖绝大多数场景，以下示例都基于这个示例payload结构：

{
  "user_id": 10086,
  "order": {"status": "paid", "pay_amount": 199, "create_time": "2024-01-01"},
  "tags": ["active", "coupon_used"]
}

• 取值操作：-> 取到的结果还是jsonb类型，->>取到的结果是文本类型
  取顶层user_id的文本值：

  SELECT payload->>'user_id' AS user_id FROM 你的目标表名;
  

  取嵌套结构里的订单状态：

  SELECT payload->'order'->>'status' AS order_status FROM 你的目标表名;
  

• 条件筛选：
  查所有已支付订单的记录：

  SELECT * FROM 你的目标表名 
  WHERE payload->'order'->>'status' = 'paid';
  

  查payload中包含refund_info键的记录：

  SELECT * FROM 你的目标表名 WHERE payload ? 'refund_info';
  

  查用户id为10086、订单状态为已支付的记录，可以直接用包含操作符@>，这种写法支持走GIN索引，大数据量下性能很好：

  SELECT * FROM 你的目标表名 
  WHERE payload @> '{"user_id": 10086, "order": {"status": "paid"}}'::jsonb;
  

  查使用了优惠券的用户（tags数组包含coupon_used元素）：

  SELECT * FROM 你的目标表名 
  WHERE payload->'tags' @> '["coupon_used"]'::jsonb;
  

学习参考方向 #

不用找零散的二手教程，按下面的路径学最快：

• 先看PostgreSQL官方手册里的JSON类型章节，所有操作符、函数的定义、参数要求、返回值类型都写得最准确，不会有误导
• 先练熟上面列的4个核心操作符：->、->>、@>、?，日常80%的查询需求都能靠这几个操作符解决
• 熟悉基础查询后，再了解jsonb的GIN索引、表达式索引创建方法，针对高频查询路径建索引，能解决绝大多数性能问题
• 最后了解jsonb的更新操作：比如||合并json对象、jsonb_set更新指定路径的值、-删除指定键，不用把整个字段读出来在应用层修改再存回数据库，能减少很多不必要的代码

内容的提问来源于stack exchange，提问作者user3724786