PostgreSQL LATERAL 的工作原理

本文主要是介绍PostgreSQL LATERAL 的工作原理，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

LATERAL 的工作原理

1. 外部查询生成一行结果：LATERAL 子查询会对每一行外部查询结果进行评估。
2. LATERAL 子查询执行：对于每一行，LATERAL 子查询会使用该行的列值来执行自己的查询。
3. 结果合并：子查询的结果与外部查询的行合并，形成最终的结果集。

以下是一个简单的例子来说明 LATERAL 的工作原理：

示例表和数据

假设我们有一个名为 employees 的表：

CREATE TABLE employees (id SERIAL PRIMARY KEY,name TEXT,tasks JSONB
);INSERT INTO employees (name, tasks) VALUES
('Alice', '[{"task": "task1"}, {"task": "task2"}]'),
('Bob', '[{"task": "task3"}, {"task": "task4"}]');

使用 LATERAL 的查询示例

我们想要展开每个员工的任务，并显示员工的名字和每个任务的名字。

SELECTe.name,t.task
FROMemployees e,LATERAL jsonb_array_elements(e.tasks) AS t(task);

查询解释

1. 外部查询生成每一行结果：

   • 第一行：('Alice', '[{"task": "task1"}, {"task": "task2"}]')
   • 第二行：('Bob', '[{"task": "task3"}, {"task": "task4"}]')

2. LATERAL 子查询执行：

   • 对于第一行，jsonb_array_elements('[{"task": "task1"}, {"task": "task2"}]') 展开成两行：
     • {"task": "task1"}
     • {"task": "task2"}
   • 对于第二行，jsonb_array_elements('[{"task": "task3"}, {"task": "task4"}]') 展开成两行：
     • {"task": "task3"}
     • {"task": "task4"}

3. 结果合并：

   • 第一行展开结果合并：
     • ('Alice', 'task1')
     • ('Alice', 'task2')
   • 第二行展开结果合并：
     • ('Bob', 'task3')
     • ('Bob', 'task4')

结果集

 name  | task
-------|-------Alice | task1Alice | task2Bob   | task3Bob   | task4

使用 LATERAL 和 WHERE 子句

假设我们只想筛选出特定的任务，可以在 LATERAL 子查询之后使用 WHERE 子句。

例如，筛选出任务为 task1 和 task3 的记录：

SELECTe.name,t.task
FROMemployees e,LATERAL jsonb_array_elements(e.tasks) AS t(task)
WHEREt.task->>'task' IN ('task1', 'task3');

结果集

 name  | task
-------|-------Alice | task1Bob   | task3

总结

• LATERAL 的作用：LATERAL 允许子查询访问外部查询的列，且会对外部查询的每一行进行评估。
• 子查询与外部查询的关系：子查询可以使用外部查询中的列值来生成结果，并将其合并到最终的结果集中。
• 临时结果集：LATERAL 子查询的结果会与外部查询的每一行合并，从而形成最终的结果集。

通过使用 LATERAL，我们能够在子查询中引用外部查询的列值，并根据这些列值进行进一步的数据处理和筛选。

是的，你可以使用相同的方法来处理具有多个字段的 JSON 数组。假设每个 JSON 对象都有两个字段 `task` 和 `priority`，你可以像这样修改查询来处理它：```sql
SELECTe.name,t.task,t.priority
FROMemployees e,LATERAL (SELECT (elem->>'task') AS task,(elem->>'priority') AS priorityFROM jsonb_array_elements(e.tasks) AS elem) AS t
WHEREt.task IN ('task1', 'task3');

在这个查询中，jsonb_array_elements(e.tasks) 将每个 JSON 对象展开成一行，并在子查询中使用 ->> 运算符来提取每个对象的 task 和 priority 字段。然后，通过别名 t 引用这些字段。

这样，你就可以处理具有多个字段的 JSON 数组了。

在这个查询中，`LATERAL jsonb_array_elements(e.tasks) AS t(task)` 这一部分是一个 LATERAL JOIN 语句，用于将 JSON 数组 `e.tasks` 中的每个元素展开成一行。让我们来解释一下其中的各个部分：- `LATERAL`: 这是一个关键字，它告诉数据库引擎在执行查询时按顺序处理 FROM 子句中的表达式，确保对每一行计算一次。在这里，它确保对 `jsonb_array_elements(e.tasks)` 返回的每个元素都进行处理。- `jsonb_array_elements(e.tasks)`: 这是一个 JSON 函数，它将 JSON 数组 `e.tasks` 中的每个元素作为一个行返回。例如，如果 `e.tasks` 是 `["task1", "task2", "task3"]`，那么这个函数将返回三行，每行一个任务。- `AS t(task)`: 这部分给返回的行定义了一个别名 `t`，并指定了一个列别名 `task`。在查询的后续部分，你可以使用 `t.task` 来引用 `jsonb_array_elements(e.tasks)` 返回的每一行中的 `task` 列。所以，整个 LATERAL JOIN 的作用是将 JSON 数组 `e.tasks` 中的每个元素拆分成一行，并将每个元素赋值给名为 `t` 的临时表，该表只有一个名为 `task` 的列。**可以选择第几个对象**
```sql
SELECTe.name,t.task,t.priority
FROMemployees e,LATERAL (SELECT (elem->>'task') AS task,(elem->>'priority') AS priority,ROW_NUMBER() OVER () AS rnFROM jsonb_array_elements(e.tasks) AS elem) AS tleft join 
WHERE
t.rn = 1;

这篇关于PostgreSQL LATERAL 的工作原理的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---