gpt4 book ai didi

sql - PostgreSQL - 将数据从 jsonb 数组移动到单独的表

转载 作者:行者123 更新时间:2023-11-29 11:58:19 25 4
gpt4 key购买 nike

在我的 PostgreSQL 数据库中,我有以下架构:

CREATE TABLE slides (
id integer,
question jsonb DEFAULT '{}'::jsonb
);

CREATE TABLE suggestions (
id integer,
slide_id integer,
suggestion_text text
);


INSERT INTO slides (id, question)
VALUES (1, '{"suggestions": ["Suggestion 1", "Suggestion 2"]}');

现在我想将question->>'suggestions' 移动到suggestions 表中。所以

SELECT * FROM suggestions; 应该返回:

------------------------------------------------------------------
id | suggestion_test | slide_id
------------------------------------------------------------------
1 | Suggestion 1 | 1
2 | Suggestion 2 | 1

我如何在 PostgreSQL 中执行此操作?这是您可以试验的 DBfiddle: https://www.db-fiddle.com/f/oEQf7ntttV5Wu9wyiXbEFk/0

最佳答案

我认为 suggestions.id 列应该是 serial:

CREATE TABLE suggestions (
id serial,
slide_id integer,
suggestion_text text
);

使用函数jsonb_array_elements_text().

insert into suggestions(slide_id, suggestion_text)
select
id as slide_id,
value as suggestion_text
from slides
cross join jsonb_array_elements_text(question->'suggestions');

DbFiddle.

关于sql - PostgreSQL - 将数据从 jsonb 数组移动到单独的表,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/50909213/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com