Sql 将 jsonb 数组中的对象取消嵌套到单独的行中

发布于09月11日

我在Postgres 14.9有一张桌子:

Name (Txt)	Detail (JSONB)	state (JSONB)
apple	[{ "code": "156", "color": "red" }, { "code": "156", "color": "blue" }]	[{ "ap": "good", "op2": "bad" }]
orange	[{ "code": "156", "color": "red" }, { "code": "235", "color": "blue" }]	[{ "op": "bad", "op2": "best" }]
lemon	[{ "code": "156", "color": "red" }, { "code": "156", "color": "blue" }]	[{ "cp": "best", "op2": "good" }]

我想要这个SELECT SQL输出:

Name (Txt)	Detail (JSONB)	state (JSONB)
apple	{ "code": "156", "color": "red"}	{ "ap": "good", "op2": "bad" }
apple	{ "code": "156", "color": "blue"}	{ "ap": "good", "op2": "bad" }
orange	{ "code": "156", "color": "red" }	{ "op": "bad", "op2": "best" }
lemon	{ "code": "156", "color": "red" }	{ "cp": "best", "op2": "good" }
lemon	{ "code": "156", "color": "blue"}	{ "cp": "best", "op2": "good" }

我的try 是:

SELECT
    "Name (Txt)"
 ,  jsonb_build_object('code', elem->>'code', 'color', elem->>'color')  AS "Detail (JSONB)"
 ,  state::JSONB "
FROM your_table,
     jsonb_array_elements("Detail (JSONB)") AS elem,
     state::JSONB
WHERE elem->>'code' = '156';

SELECT tab.name, JSONB_BUILD_OBJECT('code', cte.code, 'color', cte.color) AS "Detail (JSONB)", tab.state AS "State (JSONB)" FROM tab CROSS JOIN JSONB_TO_RECORDSET(tab.Detail) cte( code VARCHAR(30), color VARCHAR(30) )

name	Detail (JSONB)	State (JSONB)
apple	{"code":"156","color":"red"}	[{"ap":"good","op2":"bad"}]
apple	{"code":"156","color":"blue"}	[{"ap":"good","op2":"bad"}]
orange	{"code":"156","color":"yellow"}	[{"op":"bad","op2":"best"}]
lemon	{"code":"156","color":"red"}	[{"cp":"best","op2":"good"}]
lemon	{"code":"156","color":"blue"}	[{"cp":"best","op2":"good"}]

name

Detail (JSONB)

State (JSONB)

apple

{"code":"156","color":"red"}

[{"ap":"good","op2":"bad"}]

apple

{"code":"156","color":"blue"}

[{"ap":"good","op2":"bad"}]

orange

{"code":"156","color":"yellow"}

[{"op":"bad","op2":"best"}]

lemon

{"code":"156","color":"red"}

[{"cp":"best","op2":"good"}]

lemon

{"code":"156","color":"blue"}

[{"cp":"best","op2":"good"}]

Sql 将 jsonb 数组中的对象取消嵌套到单独的行中

推荐答案

Sql相关问答推荐

在postgresql中使用来自另一个字段的日期名称作为JSONB查询中的关键字

帮助修复查询以识别SQL DW中数据中的递归关系

对于表A中的每一行，更新表B中与金额有关的行

如何根据给定条件PostgreSQL迭代减少组中的行数

如何在postgres函数中插入后返回布尔值？

每组显示一行(表1中的分组值)，表2中的不同列表用逗号分隔

BigQuery-当子查询不返回任何结果时，所有结果为零

如何在SQL Server中拆分包含字符和数字的列？

优化Postgres搜索未知长度的子串

在UNION查询中查找MIN

如何在连接中使用三个不同的列，从而在PostgreSQL中只获得两个列？

Snowflake 中的分层数据

不同计数的 Postgres PIVOT 表

SQL Server - 判断 ids 层次 struct 中的整数 (id)

JSON对象查询SQL服务器

如何创建一个递归计数器来查找一个元素有多少父级和子级？

Snowflake 中的对象是如何比较的？

PostgreSQL：通过数组的元素从另一个表中 Select 数据，然后按顺序显示

MIN MAX 值与条件绑定

在 PostgreSQL 中，如何将数组中的每个元素用作另一个表中的键？