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

2024-06-02 12:40:05

在 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 ORDER BY plan.id) AS rn
    FROM
        dfv_visiting_plan plan,
        LATERAL json_array_elements(plan.schedule) AS elem
)

SELECT
    plan.id,
    expanded.commenceDate
FROM
    dfv_visiting_plan plan
JOIN
    expanded ON plan.id = expanded.id
WHERE
    expanded.rn = 1
    AND expanded.commenceDate >= '2023-01-28';

当然，以下是对该 SQL 查询中的各个部分及其涉及的语法的详细讲解：

1. 公共表表达式 (CTE)

WITH expanded AS (
    SELECT
        plan.id,
        (elem->>'commenceDate')::timestamp AS commenceDate,
        ROW_NUMBER() OVER (PARTITION BY plan.id ORDER BY plan.id) AS rn
    FROM
        dfv_visiting_plan plan,
        LATERAL json_array_elements(plan.schedule) AS elem
)
a. WITH expanded AS (...)

WITH 语句定义了一个公共表表达式 (CTE)，其名称为 expanded。CTE 是一个临时结果集，供随后查询中的主查询使用。

b. SELECT ... 
SELECT
    plan.id,
    (elem->>'commenceDate')::timestamp AS commenceDate,
    ROW_NUMBER() OVER (PARTITION BY plan.id ORDER BY plan.id) AS rn
  • plan.id: 从表 dfv_visiting_plan 中选择 id 列。
  • (elem->>'commenceDate')::timestamp AS commenceDate: 使用 ->> 运算符从 JSON 对象中提取 commenceDate 字段，将其转换为文本，然后将其转换为 timestamp 类型。
  • ROW_NUMBER() OVER (PARTITION BY plan.id ORDER BY plan.id) AS rn: 使用窗口函数 ROW_NUMBER() 为每组 plan.id 分配一个唯一的行号，按 plan.id 排序。PARTITION BY 子句将数据按 plan.id 分区，ORDER BY 子句指定排序顺序。
c. FROM dfv_visiting_plan plan, LATERAL json_array_elements(plan.schedule) AS elem
  • dfv_visiting_plan plan: 查询表 dfv_visiting_plan，别名为 plan。
  • LATERAL json_array_elements(plan.schedule) AS elem: LATERAL 允许子查询中的每一行都能引用外部查询中的列。json_array_elements(plan.schedule) 将 JSON 数组展开为多个 JSON 对象，每个对象作为一行，别名为 elem。

2. 主查询

SELECT
    plan.id,
    expanded.commenceDate
FROM
    dfv_visiting_plan plan
JOIN
    expanded ON plan.id = expanded.id
WHERE
    expanded.rn = 1
    AND expanded.commenceDate >= '2023-01-28';
a. SELECT plan.id, expanded.commenceDate

选择 dfv_visiting_plan 表中的 id 列和 CTE expanded 中的 commenceDate 列。

b. FROM dfv_visiting_plan plan JOIN expanded ON plan.id = expanded.id

从 dfv_visiting_plan 表和 CTE expanded 中进行连接：

  • JOIN: 使用内部连接将两个结果集结合起来。
  • ON plan.id = expanded.id: 指定连接条件，基于 id 列匹配。
c. WHERE expanded.rn = 1 AND expanded.commenceDate >= '2023-01-28'
  • expanded.rn = 1: 过滤条件，选择行号为 1 的记录，即 JSON 数组中的第一个对象。
  • expanded.commenceDate >= '2023-01-28': 过滤条件，选择 commenceDate 字段大于或等于 2023-01-28 的记录。

整个查询流程总结

  1. CTE 定义 (expanded): 将 dfv_visiting_plan 表中的 schedule 列展开为 JSON 数组中的各个对象，并提取每个对象的 commenceDate 字段。使用窗口函数 ROW_NUMBER() 为每个 plan.id 分配行号。
  2. 主查询: 从 dfv_visiting_plan 表和 CTE expanded 中选择数据，进行内部连接，并应用过滤条件来筛选所需的记录。

具体

WITH expanded_schedule AS (
    SELECT
        pa.tenant_code,
        plan.status,
        schedule_elem->>'commenceDate' AS commence_date,
        ROW_NUMBER() OVER (PARTITION BY pa.tenant_code ORDER BY (schedule_elem->>'commenceDate')::timestamp) AS rn
    FROM
        dfv_visiting_plan plan
        LEFT JOIN dfv_patient_case pcase ON pcase.ID = plan.id
        LEFT JOIN dfv_patient pa ON pa.ID = pcase.patient_id,
        LATERAL jsonb_array_elements(plan.schedule) AS schedule_elem
)
SELECT
    tenant_code,
    COUNT(*) FILTER (WHERE 1 = 1) AS 应,
    COUNT(*) FILTER (WHERE status IN (2, 3)) AS 随,
    commence_date
FROM
    expanded_schedule
WHERE
    rn = 1
    AND commence_date::timestamp >= '2023-01-28'::timestamp
GROUP BY
    tenant_code, commence_date;

好的，让我们更详细地讲解这三个知识点：

1. 使用 ->> 运算符提取 JSON 字段并转换类型

在 PostgreSQL 中，JSON 数据类型支持各种操作符和函数来访问和操作 JSON 数据。其中，->> 运算符用于从 JSON 对象中提取字段值并将其转换为文本。

(elem->>'commenceDate')::timestamp AS commenceDate
a. ->> 运算符
  • elem->>'commenceDate'：从 JSON 对象 elem 中提取 commenceDate 字段的值并将其转换为文本。这是因为 ->> 运算符返回的是 text 类型，而 -> 运算符返回的是 jsonb 类型。
b. 类型转换
  • ::timestamp：将文本类型的 commenceDate 转换为 timestamp 类型。这样可以在 SQL 查询中对日期和时间进行比较和操作。

2. 窗口函数 ROW_NUMBER()

窗口函数 ROW_NUMBER() 为结果集中的每一行分配唯一的行号。窗口函数在 OVER 子句的控制下执行，能够根据特定的排序和分区规则对行进行编号。

ROW_NUMBER() OVER (PARTITION BY plan.id ORDER BY plan.id) AS rn
a. ROW_NUMBER()
  • ROW_NUMBER()：窗口函数，为结果集中的每一行生成一个唯一的行号，起始值为1。
b. OVER 子句
  • OVER (PARTITION BY plan.id ORDER BY plan.id)：定义了窗口函数的分区和排序规则。
    • PARTITION BY plan.id：将结果集按 plan.id 分区。每个分区内的行号从1开始重新编号。
    • ORDER BY plan.id：指定分区内行的排序顺序。按照 plan.id 排序，如果不指定排序顺序，默认是升序。

3. LATERAL 和 json_array_elements 函数

a. LATERAL

LATERAL 允许子查询中的每一行引用外部查询中的列。它使得子查询能够依赖于外部查询的上下文。

LATERAL (
    SELECT
        elem,
        ROW_NUMBER() OVER (PARTITION BY plan.id ORDER BY plan.id) AS rn
    FROM
        json_array_elements(plan.schedule) AS elem
)
  • LATERAL 子查询：在 FROM 子句中使用 LATERAL，使子查询能够访问外部查询中的列。在这种情况下，子查询可以引用 plan.schedule 列。
b. json_array_elements 函数

json_array_elements 是一个 PostgreSQL 函数，用于将 JSON 数组展开为多个 JSON 值，每个值作为一行输出。

  • json_array_elements(plan.schedule) AS elem：将 plan.schedule 中的 JSON 数组展开为多个 JSON 对象，每个对象作为一行，别名为 elem。

具体示例讲解

假设 dfv_visiting_plan 表中有一行数据，schedule 列包含以下 JSON 数组：

[
    {"commenceDate": "2024-03-03T06:34:08.811Z"},
    {"commenceDate": "2024-04-14T06:34:08.811Z"}
]

通过以下查询，我们可以提取数组中的第一个 commenceDate 字段并将其转换为 timestamp 类型：

WITH expanded AS (
    SELECT
        plan.id,
        (elem->>'commenceDate')::timestamp AS commenceDate,
        ROW_NUMBER() OVER (PARTITION BY plan.id ORDER BY plan.id) AS rn
    FROM
        dfv_visiting_plan plan,
        LATERAL json_array_elements(plan.schedule) AS elem
)
SELECT
    plan.id,
    expanded.commenceDate
FROM
    dfv_visiting_plan plan
JOIN
    expanded ON plan.id = expanded.id
WHERE
    expanded.rn = 1
    AND expanded.commenceDate >= '2023-01-28';

查询流程

  1. CTE 定义 (expanded)：

    • 使用 LATERAL 和 json_array_elements 展开 plan.schedule 中的 JSON 数组，每个元素成为一行。
    • 使用 ->> 运算符从 JSON 对象中提取 commenceDate 字段，转换为文本，再转换为 timestamp。
    • 使用 ROW_NUMBER() 窗口函数按 plan.id 分区和排序，为每组分配一个行号。

  2. 主查询：

    • 从 dfv_visiting_plan 表和 expanded CTE 中选择数据。
    • 使用内部连接 JOIN，基于 id 进行匹配。
    • 在 WHERE 子句中过滤出 rn = 1（第一个 JSON 对象）且 commenceDate >= '2023-01-28' 的记录。

通过上述步骤，最终我们可以提取并筛选出 JSON 数组中指定的 commenceDate 字段。

上一篇：LeetCode 两数相加


下一篇：（持续更新）ai 工程师(nlp)面试问题+答案