求助：PostgreSQL嵌套JSON数组按日期过滤的查询实现

我在PostgreSQL数据库中存储了类型为json的字段数据，结构如下：

{"users": [
    {
      "id": 1,
      "data": [
        {
          "pos": "BA",
          "endDate": "2022-07-31",
          "startDate": "2022-01-01"
        },
        {
          "pos": "BA2",
          "endDate": "2022-09-30",
          "startDate": "2022-08-01"
        },
        {
          "pos": "BA3",
          "endDate": "2023-03-31",
          "startDate": "2022-10-01"
        },
        {
          "pos": "BA4",
          "endDate": "2023-06-08",
          "startDate": "2023-04-01"
        }
      ]
    },
    {
      "id": 2,
      "data": [
        {
          "pos": "BA",
          "endDate": "2022-07-31",
          "startDate": "2022-01-01"
        },
        {
          "pos": "BA2",
          "endDate": "2022-09-30",
          "startDate": "2022-08-01"
        },
        {
          "pos": "BA3",
          "endDate": "2023-03-31",
          "startDate": "2022-10-01"
        },
        {
          "pos": "BA4",
          "endDate": "2023-06-08",
          "startDate": "2023-04-01"
        }
      ]
    },
    {
      "id": 3,
      "data": [
        {
          "pos": "BA",
          "endDate": "2022-07-31",
          "startDate": "2022-01-01"
        },
        {
          "pos": "BA2",
          "endDate": "2022-09-30",
          "startDate": "2022-08-01"
        },
        {
          "pos": "BA3",
          "endDate": "2023-03-31",
          "startDate": "2022-10-01"
        },
        {
          "pos": "BA4",
          "endDate": "2023-06-08",
          "startDate": "2023-04-01"
        }
      ]
    }
  ]
}

需要编写SQL查询语句，根据指定的startDate和endDate过滤嵌套的data数组中的条目。例如当指定startDate为2022-01-01、endDate为2022-12-31时，需返回如下符合条件的JSON结果：

{"users": [
    {
      "id": 1,
      "data": [
        {
          "pos": "BA",
          "endDate": "2022-07-31",
          "startDate": "2022-01-01"
        },
        {
          "pos": "BA2",
          "endDate": "2022-09-30",
          "startDate": "2022-08-01"
        }
      ]
    },
    {
      "id": 2,
      "data": [
        {
          "pos": "BA",
          "endDate": "2022-07-31",
          "startDate": "2022-01-01"
        },
        {
          "pos": "BA2",
          "endDate": "2022-09-30",
          "startDate": "2022-08-01"
        }
      ]
    },
    {
      "id": 3,
      "data": [
        {
          "pos": "BA",
          "endDate": "2022-07-31",
          "startDate": "2022-01-01"
        },
        {
          "pos": "BA2",
          "endDate": "2022-09-30",
          "startDate": "2022-08-01"
        }
      ]
    }
  ]
}

实现查询语句 #

假设你的表名为your_table，JSON字段名为json_data，可以使用以下SQL语句实现需求：

SELECT json_build_object(
    'users', json_agg(
        json_build_object(
            'id', user_obj->>'id',
            'data', filtered_data
        )
    )
) AS filtered_json
FROM your_table,
     json_array_elements(json_data->'users') AS user_obj,
     LATERAL (
         SELECT json_agg(data_item) AS filtered_data
         FROM json_array_elements(user_obj->'data') AS data_item
         WHERE (data_item->>'startDate')::DATE >= '2022-01-01'
           AND (data_item->>'endDate')::DATE <= '2022-12-31'
     ) AS filtered_data_subquery;

关键点说明 #

1. 展开数组：使用json_array_elements函数分别展开users数组和每个用户的data数组，将嵌套结构转为行数据处理。
2. 日期过滤：将JSON中的日期字符串转为DATE类型，与指定的起止日期进行比较，筛选符合条件的data条目。
3. 重新聚合：通过json_agg将过滤后的data条目重新聚合成数组，再将每个用户的信息聚合成users数组，最后用json_build_object构建目标JSON结构。

如果你的JSON字段类型是jsonb（推荐用于频繁操作JSON的场景），只需将上述语句中的json_*函数替换为jsonb_*函数即可（如jsonb_array_elements、jsonb_agg、jsonb_build_object）。

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