时间戳

首页 > 解决方案 > JSONB 转数组数据类型

sql - JSONB 转数组数据类型

问题描述

我目前有一个带有列操作（JSONB）的表会话。

我能够使用array_to_jsonb(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。但不是以一种可以恢复适当阵列的方式。

开始为：名为会话的表中操作列中的 JSONB

应导致：返回所有行的查询，但将操作列 (JSONB) 转换回前端的数组：

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

会话表的屏幕截图

我试过这样的查询：

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

并接收返回 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;

获取文本值，然后将聚合函数应用于这些返回的行。只是无法让 select array_agg(doc) 按预期工作。很可能是因为我在那个地方需要不同的功能。

标签： sqlarraysjsonpostgresqljsonb

解决方案

这有帮助吗？

演示：db<>小提琴

SELECT
    jsonb_agg(elem) 
FROM
    sessions, jsonb_array_elements(actions) as elem
  1. jsonb_array_elements()将数组扩展jsonb为一行，每个jsonb元素
  2. jsonb_agg()将这些元素聚合jsonb成一个大数组。

推荐阅读