PostgreSQL JSON数据查询：日期范围且库存大于0的实现方法

嘿，我来帮你搞定这个PostgreSQL处理嵌套JSON数据的问题！首先得明确：你的数据是嵌套层级的JSON，所以第一步需要把这些嵌套结构展开成关系型的行数据，之后才能方便地进行日期筛选和库存判断。

先假设你的表结构 #

首先，我先假设你有一张存储这个JSON数据的表，比如叫product_inventory，里面有个字段data用来存你的JSON（推荐用JSONB类型，比JSON更高效且支持索引）：

CREATE TABLE product_inventory (
    id SERIAL PRIMARY KEY,
    data JSONB NOT NULL
);

然后把你提供的示例数据插进去：

INSERT INTO product_inventory (data)
VALUES ('{"2018-05": {"20": {"price": 50, "stock": 12}, "21": {"price": 60, "stock": 5}, "25": {"price": 55, "stock": 0} }}');

编写查询语句 #

接下来就是核心的查询了，我们需要用jsonb_each函数把嵌套的JSON逐层展开，然后拼接日期、转换数据类型，最后筛选条件：

SELECT
    -- 拼接年-月和日，转换成标准DATE类型
    (year_month || '-' || day)::DATE AS record_date,
    -- 提取价格并转成整数
    (daily_details ->> 'price')::INT AS product_price,
    -- 提取库存并转成整数
    (daily_details ->> 'stock')::INT AS product_stock
FROM
    product_inventory,
    -- 第一层：展开顶层的年-月键值对
    jsonb_each(data) AS month_level(year_month, daily_records),
    -- 第二层：展开每个月对应的日期键值对
    jsonb_each(daily_records) AS day_level(day, daily_details)
WHERE
    -- 筛选指定日期范围，这里示例是2018-05-20到2018-05-22
    (year_month || '-' || day)::DATE BETWEEN '2018-05-20' AND '2018-05-22'
    -- 筛选库存大于0的记录
    AND (daily_details ->> 'stock')::INT > 0;

结果说明 #

执行这个查询后，你会得到符合条件的记录：

一些补充说明 #

• 如果你的JSON字段是JSON类型而不是JSONB，只需要把jsonb_each换成json_each就行，但还是建议换成JSONB，尤其是数据量较大的时候，性能会好很多。
• 如果你需要动态传入日期范围，可以用PostgreSQL的变量或者应用层传参，比如：

  -- 定义变量
  SET @start_date = '2018-05-20';
  SET @end_date = '2018-05-22';
  
  SELECT
      (year_month || '-' || day)::DATE AS record_date,
      (daily_details ->> 'price')::INT AS product_price,
      (daily_details ->> 'stock')::INT AS product_stock
  FROM
      product_inventory,
      jsonb_each(data) AS month_level(year_month, daily_records),
      jsonb_each(daily_records) AS day_level(day, daily_details)
  WHERE
      (year_month || '-' || day)::DATE BETWEEN @start_date::DATE AND @end_date::DATE
      AND (daily_details ->> 'stock')::INT > 0;
  

• 要是你经常需要做这类查询，建议考虑把JSON数据扁平化存储成常规的关系表，这样查询效率会更高，也更易维护。

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