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

devtools/2024/9/23 0:21:28/

在 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}
]');

json_array_elements__54">方法一：LATERAL 和 json_array_elements 函数

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

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


SELECT(elem->>'commenceDate')::timestamp AS commenceDate
FROMdfv_visiting_plan plan,LATERAL (SELECTelem,ROW_NUMBER() OVER () AS rnFROMjson_array_elements(plan.schedule) AS elem) AS subquery
WHEREplan.id = '72e823b2b6224c7985752806efb513e2'AND subquery.rn = 2;

3.使用CTE/或者LATERAL

第一中使用 CTE

WITH expanded AS (SELECTplan.id,(elem->>'commenceDate')::timestamp AS commenceDate,ROW_NUMBER() OVER (PARTITION BY plan.id ORDER BY plan.id) AS rnFROMdfv_visiting_plan plan,LATERAL json_array_elements(plan.schedule) AS elem
)SELECTplan.id,expanded.commenceDate
FROMdfv_visiting_plan plan
JOINexpanded ON plan.id = expanded.id
WHEREexpanded.rn = 1AND expanded.commenceDate >= '2023-01-28';

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

1. 公共表表达式 (CTE)

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

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

b. SELECT ...
SELECTplan.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 子句指定排序顺序。
json_array_elementsplanschedule_AS_elem_142">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. 主查询

SELECTplan.id,expanded.commenceDate
FROMdfv_visiting_plan plan
JOINexpanded ON plan.id = expanded.id
WHEREexpanded.rn = 1AND 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 (SELECTpa.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 rnFROMdfv_visiting_plan planLEFT JOIN dfv_patient_case pcase ON pcase.ID = plan.idLEFT JOIN dfv_patient pa ON pa.ID = pcase.patient_id,LATERAL jsonb_array_elements(plan.schedule) AS schedule_elem
)
SELECTtenant_code,COUNT(*) FILTER (WHERE 1 = 1) AS 应,COUNT(*) FILTER (WHERE status IN (2, 3)) AS 随,commence_date
FROMexpanded_schedule
WHERErn = 1AND commence_date::timestamp >= '2023-01-28'::timestamp
GROUP BYtenant_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 排序，如果不指定排序顺序，默认是升序。

json_array_elements__246">3. LATERAL 和 json_array_elements 函数

a. LATERAL

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

LATERAL (SELECTelem,ROW_NUMBER() OVER (PARTITION BY plan.id ORDER BY plan.id) AS rnFROMjson_array_elements(plan.schedule) AS elem
)
  • LATERAL 子查询：在 FROM 子句中使用 LATERAL，使子查询能够访问外部查询中的列。在这种情况下，子查询可以引用 plan.schedule 列。
json_array_elements__263">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 数组：

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

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

WITH expanded AS (SELECTplan.id,(elem->>'commenceDate')::timestamp AS commenceDate,ROW_NUMBER() OVER (PARTITION BY plan.id ORDER BY plan.id) AS rnFROMdfv_visiting_plan plan,LATERAL json_array_elements(plan.schedule) AS elem
)
SELECTplan.id,expanded.commenceDate
FROMdfv_visiting_plan plan
JOINexpanded ON plan.id = expanded.id
WHEREexpanded.rn = 1AND 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 字段。


http://www.ppmy.cn/devtools/43870.html

相关文章

基于Keras的手写数字识别(附源码)

基于Keras的手写数字识别(附源码)

目录 引言 为什么要创建虚拟环境，好处在哪里？ 源码 我修改的部分 调用本地数据 修改第二层卷积层 引言 本文是博主为了记录一个好的开源代码而写，下面是代码出处！强烈建议收藏！【深度学习实战—1】&#xff1a…
阅读更多...
Python | Leetcode Python题解之第116题填充每个节点的下一个右侧节点指针

Python | Leetcode Python题解之第116题填充每个节点的下一个右侧节点指针

题目： 题解： class Solution:def connect(self, root: Node) -> Node:if not root:return root# 从根节点开始leftmost rootwhile leftmost.left:# 遍历这一层节点组织成的链表，为下一层的节点更新 next 指针head leftmostwhile head:#…
阅读更多...
【QT】包含中文的QString转换为std::string乱码问题

【QT】包含中文的QString转换为std::string乱码问题

问题描述 在开发过程中需要用到QStringList file_names QFileDialog::getOpenFileNames()读取文件，并通过QString::toStdString()方法转换为std::string类型进行后续的使用，但是我发现当文件名包含中文的时候，QString没问题，但是…
阅读更多...
请叙述Vue 中使用了哪些设计模式

请叙述Vue 中使用了哪些设计模式

在Vue中，使用了多种设计模式来构建其框架和组件系统，这些设计模式使得Vue具有高效、灵活和易于维护的特性。以下是Vue中常见的设计模式及其简要描述： MVVM（Model-View-ViewModel）模式： Vue.js是一个MVVM框架…
阅读更多...
TCP—三次握手和四次挥手

TCP—三次握手和四次挥手

目录 一、三次握手和四次挥手的目的 二、TCP可靠的方面 三、什么是三次握手 四、第三次握手的目的 五、什么是四次挥手 六、超时时间的目的 七、SYN包、ACK包、FIN包 八、解决丢包和乱序 九、参考资料 一、三次握手和四次挥手的目的 TCP三次握手的目的主要是为了确保两…
阅读更多...
汇编原理（三）编程

汇编原理（三）编程

源程序： 汇编指令：有对应的机器码与其对应 伪指令：无对应的机器码，是由编译器来执行的指令，编译器根据伪指令来进行相关的编译工作。 ex1:XXX segment、XXX ends这两个是一对成对使用的伪指令，且必须会被用…
阅读更多...
TCP 与 UDP

TCP 与 UDP

0. tcp 与 udp 的 异同特性 TCPUDPname传输控制协议用户数据报协议面向连接？ 需要 传输数据前建立连接传输完毕后断开连接不需要可靠的传输数据？ 可靠 有确认机制（三次握手） 有确认、窗口、重传、拥塞控制的机制保证数据可靠传输…
阅读更多...
Web前端三大主流框架

Web前端三大主流框架

Web前端三大主流框架，就像是建房子用的三种超级工具箱，让开发者能更快更好地搭建漂亮的网页。这三大框架分别是Angular、React和Vue，我们一个个来了解： 1. Angular： Angular像是一个全能型的建筑套装，里…
阅读更多...
最新文章

Copyright @ 2022~2023