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

我在 Postgres 14.9 中有一个表：

我想要这个选择sql输出：

我的尝试：

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';

jsonb_array_elements() https://www.postgresql.org/docs/current/functions-json.html#id-1.5.8.22.5.11.2.2.1.1.2.1 does exactly您所要求的，只需一步：

SELECT name, jsonb_array_elements(detail) AS detail, state
FROM   tbl;

fiddle https://dbfiddle.uk/X0rN5Gao

See:

• 查询 JSON 类型内的数组元素 https://stackoverflow.com/questions/22736742/query-for-array-elements-inside-json-type/22737710#22737710

如果可以有空数组或null in detail，这是保留所有输入行的变体：

SELECT t.name, e.detail, t.state
FROM   tbl t
LEFT   JOIN LATERAL jsonb_array_elements(t.detail) AS e(detail) ON true;

See:

• PostgreSQL 中的 LATERAL JOIN 和子查询有什么区别？ https://stackoverflow.com/questions/28550679/what-is-the-difference-between-a-lateral-join-and-a-subquery-in-postgresql/28557803#28557803

当然，detail必须是 jsonbarray，否则您会收到错误消息。你可能会测试jsonb_typeof() first:

See:

• 使用 jsonb_set() 有条件更新 https://stackoverflow.com/questions/65817635/conditional-update-with-jsonb-set/65832572#65832572