如何在JSON中构建类表结构？将SQL数据转为易访问JSON结构

我来帮你搞定这个需求——把奖品规则数据转成能像数据库表一样便捷查询的JSON结构，同时适配你给出的promotions表设计。

一、选对JSON结构：用数组存储规则记录 #

你的原始数据是多行的规则条目，用数组来存储这些规则是最合适的选择。每个数组元素对应一条规则记录（包含stake、tickets_no、prize三个字段），这样既完美映射了原表的行结构，又能让PostgreSQL的JSONB工具轻松把数组转换成关系型行，方便后续的筛选和聚合操作。

二、创建表并插入结构化数据 #

按照你的promotions表设计，我们可以把规则数组放进details字段的rules键下，具体SQL如下：

create table promotions (id integer, details jsonb);
insert into promotions values (1, '{
  "name": "promo1",
  "rules": [
    {"stake": 400, "tickets_no": 5, "prize": 10},
    {"stake": 1000, "tickets_no": 10, "prize": 25},
    {"stake": 2000, "tickets_no": 50, "prize": 70}
  ]
}');

三、编写类SQL的查询语句 #

要模拟你原来的SQL逻辑（筛选stake ≤ 1200且tickets_no ≤27的规则，取最大prize），我们可以用jsonb_array_elements函数把JSON数组拆分成关系型行，再用熟悉的SQL条件和聚合函数处理：

select max((rule->'prize')::integer) as max_prize
from promotions,
     jsonb_array_elements(details->'rules') as rule
where id = 1 -- 指定目标促销活动
  and (rule->'stake')::integer <= 1200
  and (rule->'tickets_no')::integer <= 27;

这个查询的逻辑和你原来的SQL完全一致，会返回25作为结果。简单解释下关键步骤：

• jsonb_array_elements(details->'rules')：把JSON数组拆分成一条条独立的规则行
• (rule->'stake')::integer：提取JSON字段并转换成数值类型，方便做数值比较
• 最后用max()聚合函数获取符合条件的最高奖品值

四、可选优化：添加索引提升查询效率 #

如果你的promotions表数据量较大，或者需要频繁查询规则字段，可以给details字段建立GIN索引，大幅加快JSONB的查询速度：

create index idx_promotions_details on promotions using gin (details);

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