JSONB转换为数组数据类型

Question

我目前有一张名为sessions的表格，其中有一个名为actions的列(JSONB)。

我能够使用array_to_jsonb(array value)将来自我的前端的数组正确地存储到我的数据库中。

我的数组结构（array value）如下：

var arrayStructure = [
    {name: 'email client', check: false},
    {name: 'send contract', check: false}
]

与我在SO上看到的大多数关于修改JSONB或访问某些键的问题不同，我只对将整个JSONB数据转换回前端数组感兴趣。

我的临时解决方案是通过数组映射并在Javascript中使用JSON.parse()来重构数组中的每个对象。我无法在整个数组上使用JSON.parse()，因为它会抛出错误。

我正在寻找一个查询，以将数组格式带回存储的JSONB数据类型。我已经看过了JSONB_SET、LATERAL和JSONB_ARRAY_ELEMENTS_TEXT。但没有一种方法可以有效地将其转换成正确的数组。

开始为：名为sessions的表中的actions列中的JSONB

应返回结果：一个查询，带回所有行，但是将actions列(JSONB)转换回前端数组：

select session_id, session_name, someFunction or lateral here(actions) from sessions

会话表截图

我尝试过类似这样的查询：

SELECT 
   session_id, 
   actions::jsonb -> 'name' as name
FROM sessions;

我尝试获取name，结果却返回null。我已经试过访问更深层次的内容，但是也没用。

以下是正确查询结果的一部分：

select session_id, jsonb_array_elements_text(actions)
from sessions
group by session_id;

以下是结果（只关注session_id为264的结果）：

查询结果

现在我已经将对象放在它们自己的行中：

 {"name": "some task", "check": "false} 

当我想要的动作列是：

[ {name: "some task", check: false}, {name: "other task", check: true} ]

我需要进一步解析JSON并按session_id分组。 我只是在努力构建一个能够实现此功能的子查询。

创建设置步骤：

create table fakeSessions (
session_id serial primary key, 
name varchar(20), 
list jsonb
)

insert into fakeSessions(name, list)
VALUES(
'running',
'["{\"name\":\"inquired\",\"check\":false}", "{\"name\":\"sent online guide\",\"check\":false}", "{\"name\":\"booked!\",\"check\":false}"]'
)

insert into fakeSessions(name, list)
VALUES(
'snowboarding',
'["{\"name\":\"rental\",\"check\":false}", "{\"name\":\"booked ski passes\",\"check\":false}", "{\"name\":\"survey\",\"check\":false}"]'
)

我创建的最接近的查询：

with exports as (
    select jsonb_array_elements_text(actions)::jsonb as doc from sessions
)
select array_agg(doc) from
exports, sessions
group by session_id;

获取文本值，然后对返回的行应用聚合函数。只是无法将选择的array_agg（doc）按预期工作。最有可能是因为我需要在那个位置使用不同的函数。

- Ryan Brockhoff

---

请提供一些DDL/DML语句以重现问题。同时，展示您期望的输出和格式。 - Kaushik Nayak

请展示表格和数据的样子。您是否有一个包含整个jsonb数组的单行？您是否每个数组元素都有一行？您是否有名称和检查的单独列？ - S-Man

我真的还是不太明白。请添加一些示例数据（以文本形式，以便我们可以复制到我们的工具中）和您期望的输出。我的理解是：您有一些带有数组的行，并且您想要合并这些数组。因此，第1行具有[{name:a, check:t}, {name:b, check:f}]，第2行具有[{name:c, check:t}]，您想要的是[{name:a...}，{name:b,...}，{name:c,...}]？ - S-Man

@S-Man，非常抱歉我没有表述清楚:/ 我添加了查询来构建一个带有我正在处理的数据的示例表。是的，你的期望输出是正确的！ - Ryan Brockhoff

感谢您提供的样本数据。现在我们需要这些数据的预期输出 :) 我的回答有帮助吗？如果没有，请添加此输出 :) - S-Man

Answer 1

这能帮助你吗？

演示: db<>fiddle

SELECT
    jsonb_agg(elem) 
FROM
    sessions, jsonb_array_elements(actions) as elem

1. jsonb_array_elements()将jsonb数组扩展为每个jsonb元素的一行。
2. jsonb_agg()将这些jsonb元素聚合成一个大数组。

- S-Man

---

你太棒了！我能够通过这个构建出正确的格式。感谢你提供的精彩反馈并与我一起解决问题。让我的一天变得美好 :) - Ryan Brockhoff

我刚刚获得了在这个问题/答案上投票的能力。所以我很兴奋地给出我的第一个赞！ :) - Ryan Brockhoff

Answer 2

我能够通过基于你的查询进行构建得出答案！非常感谢。得到预期结果的查询如下：

with exports as (
    select session_id, jsonb_array_elements_text(actions)::jsonb as doc from sessions
)
select session_id, jsonb_agg(doc) from exports
group by session_id;

我在获取元素后没有使用jsonb_agg()函数。获得精确格式的区别仅仅是使用了jsonb_array_elements_text::jsonb

- Ryan Brockhoff

---

Hm json_array_element_text 将 json 元素转换为纯文本，然后再将其转换回 json。这似乎不太合理。Postgres 的 json 引擎性能非常高。如果在中间将对象转换为其他类型，则可能会损失很多性能。 - S-Man