【PostgreSQL】 JSON数组提取根据索引提取对象和字段

原创

已于 2024-05-29 17:32:53 修改 · 1.8k 阅读

11 ·

CC 4.0 BY-SA版权

文章标签：

#postgresql #json #数据库

于 2024-05-29 17:20:58 首次发布

在 PostgreSQL 中处理 JSON 数组：按索引提取对象和字段

在现代应用程序中，JSON 数据格式因其灵活性和可读性广泛应用。PostgreSQL 作为一个强大的关系型数据库管理系统，提供了强大的 JSON 数据类型和函数支持，使得在数据库中存储和操作 JSON 数据变得方便高效。在这篇博客中，我们将探讨如何在 PostgreSQL 中从 JSON 数组中按索引提取对象，提取对象中的字段，并使用 LATERAL 子查询和 ROW_NUMBER() 进行进一步处理。

场景描述

假设我们有一张名为 dfv_visiting_plan 的表，其中的 JSON 字段 schedule 存储了多个包含随访任务的 JSON 对象。我们希望能够从这个 JSON 数组中提取第一个对象的 commenceDate 字段，并根据这个字段进行筛选和统计。

示例数据

首先，让我们创建一个示例表并插入一些数据：

CREATE TABLE dfv_visiting_plan (
    id serial PRIMARY KEY,
    schedule jsonb
);

INSERT INTO dfv_visiting_plan (schedule) VALUES
('[
    {
        "orderNo": 0,
        "name": "出院三个月后第一次随访任务",
        "planDate": "2024-04-24T08:00:00.000Z",
        "commenceDate": "2024-04-17T08:00:00.000Z",
        "deadline": "2024-05-01T08:00:00.000Z",
        "visitWay": 1,
        "offset": 7,
        "offsetUnit": "day",
        "duration": 3,
        "durationUnit": "month",
        "forms": ["stroke-recovery-beijin"],
        "status": 0
    },
    {
        "orderNo": 1,
        "name": "出院三个月后第二次随访任务",
        "planDate": "2024-05-24T08:00:00.000Z",
        "commenceDate": "2024-05-17T08:00:00.000Z",
        "deadline": "2024-06-01T08:00:00.000Z",
        "visitWay": 1,
        "offset": 7,
        "offsetUnit": "day",
        "duration": 3,
        "durationUnit": "month",
        "forms": ["stroke-recovery-beijin"],
        "status": 0
    }
]');

方法一：LATERAL 和 json_array_elements 函数

是使用 json_array_elements 将 JSON 数组展开为多行，然后仅选择第一个对象。我们可以借助 ROW_NUMBER() 窗口函数实现这一点。

1.首先找到单条数据的json字段
2.使用json_array_elements 函数将该字段展开


SELECT
    (elem->>'commenceDate')::timestamp AS commenceDate
FROM
    dfv_visiting_plan plan,
    LATERAL (
        SELECT
            elem,
            ROW_NUMBER() OVER () AS rn
        FROM
            json_array_elements(plan.schedule) AS elem
    ) AS subquery
WHERE
    plan.id = '72e823b2b6224c7985752806efb513e2'
    AND subquery.rn = 2;

3.使用CTE/或者LATERAL

第一中使用 CTE

WITH expanded AS (
    SELECT
        plan.id,
        (elem->>'commenceDate')::timestamp AS commenceDate,
        ROW_NUMBER() OVER (PARTITION BY plan.id