sql - 遍历一个嵌套的json对象，将键和值插入到两个相关的表中

Question

我将以下 json 结构传递给我的程序：

{questA: [[a1, a2], [a3, a4]], questB: [[b1, b2], [b2, b4]...]}

我想检查所有“任务”键（questA，questB ...）并将每个键名称插入一个表，并将它的值设置为多行中的另一个表，因此每个集合（a1，a2）都有它自己的行加上它的父任务键的外键字段。

quest
-------
id
key

questValues
-------------
id
val
val
quest_id
foreign key (quest_id) references quest(id)

我试过类似的东西：

FOR key, val IN SELECT * FROM jasonb_each_text(myJson) LOOP
...
END LOOP;

但它会遍历所有内容，因此 val 数组现在只是纯文本。我考虑过使用其中一个 json 文字函数链接选择，但我不确定语法。

标签： sqlpostgresqlplpgsql

Answer 1

您确实可以通过链接输出各种 JSON 函数来做到这一点：

with input (parameter) as (
  values ('{"questA": [["a1", "a2"], ["a3", "a4"]], "questB": [["b1", "b2"], ["b2", "b4"]]}'::jsonb)
), elements as (
  select j.quest, k.answer
  from input i 
    cross join lateral jsonb_each(i.parameter) as j(quest,vals)
    cross join lateral jsonb_array_elements(j.vals) as k(answer)
), new_quests as (
  insert into quest ("key")
  select distinct quest
  from elements
  returning *
)  
insert into quest_values (val1, val2, quest_id)
select e.answer ->> 0 as val1,
       e.answer ->> 1 as val2,
       nq.id as quest_id
from new_quests nq
  join elements e on e.quest = nq.key;

第一步（“元素”）将 JSON 值转换为可用作 INSERT 语句源的行。它返回这个：

quest  | answer      
-------+-------------
questA | ["a1", "a2"]
questA | ["a3", "a4"]
questB | ["b1", "b2"]
questB | ["b2", "b4"]

下一步将quest列的唯一值插入到quest表中并返回生成的 ID。

最后的语句将生成的 ID 与第一步中的行连接起来，并将两个数组元素提取为两个值。它使用该查询作为插入quest_values表的源。

在一个过程中，您显然不需要生成示例数据的部分，所以它看起来像这样：

with elements as (
  select j.quest, k.answer
  from jsonb_each(the_parameter) as j(quest,vals)
    cross join lateral jsonb_array_elements(j.vals) as k(answer)
), new_quests as (
  insert into quest ("key")
  select distinct quest
  from elements
  returning *
)  
insert into quest_values (val1, val2, quest_id)
select e.answer ->> 0 as val1,
       e.answer ->> 1 as val2,
       nq.id as quest_id
from new_quests nq
  join elements e on e.quest = nq.key;

the_parameter传递给您的过程的 JSONB 参数 在哪里。

在线示例：https ://rextester.com/NBJIK44025