sql - 如何编写一个插入 SQL 语句循环遍历对象数组中的每条记录并相应地插入到记录的特定列中？

Question

首先，我想弄清楚如何在 sql 语句中甚至编写一个对象数组(如在 js 中)，但我在互联网上找不到任何东西......

我当然可以只重复所有的插入语句，但我真的只想遍历数据集并将它们注入(inject)到一组列的表中，这些列具有完全相同的插入语句但值不同!但是，如果数据集像对象数组一样太复杂，似乎没有办法做到这一点？还是我只需要编写多个数组列表来表示每一列，这真的很愚蠢……不是吗？

谢谢

数据集示例

[
  {
    name: 'abc',
    gender: 'male',
  },
  {
    name: 'bbc',
    gender: 'female',
  },
  {
    name: 'ccc',
    gender: 'male',
  },
]

并将它们放入一个包含列的表格中

nameHere
genderThere

Answer 1

您可以使用 jsonb_array_elements 从数组中提取每个 JSON，然后将其用作 INSERT 的源:

create table x(name text, gender text);

insert into x (name, gender)
select t ->> 'name', t ->> 'gender'
from jsonb_array_elements(
'[
  {
    "name": "abc",
    "gender": "male"
  },
  {
    "name": "bbc",
    "gender": "female"
  },
  {
    "name": "ccc",
    "gender": "male"
  }
]'::jsonb) t;

在线示例:http://rextester.com/GZF87679

更新(范围改变后)

要处理嵌套的 JSON 结构，需要将返回 jsonb -> 的运算符与返回“纯文本”的运算符结合起来:

insert into x (name, gender)
select t -> 'name' ->> 'first', t ->> 'gender'
from jsonb_array_elements(
'[
  {
    "name": {"first": "a", "last": "b"},
    "gender": "male"
  }
]'::jsonb) t;

可以找到有关 JSON 运算符的更多详细信息 in the manual