在一个查询中更新多行，但我们期望的输入来自多个数据的 json 对象

    update users as u set -- postgres FTW
    email = u2.email,
    first_name = u2.first_name,
    last_name = u2.last_name
    from (values
   (1, '[email protected] /cdn-cgi/l/email-protection', 'Hollis', 'O\'Connell'),
   (2, '[email protected] /cdn-cgi/l/email-protection', 'Robert', 'Duncan')
    ) as u2(id, email, first_name, last_name)
    where u2.id = u.id;

上面的查询用于更新一个查询中的多行，它的工作效率也很高，但我有下面的 JSON：

   Person:{[id:1,email:"[[email protected] /cdn-cgi/l/email-protection]",first_name:"John",last_name:"Doe"],[id:2,email:"[[email protected] /cdn-cgi/l/email-protection]",first_name:"Robert",last_name:"Duncan"],[id:3,email:"[[email protected] /cdn-cgi/l/email-protection]",first_name:"Ram",last_name:"Das"],[id:4,email:"[[email protected] /cdn-cgi/l/email-protection]",first_name:"Albert",last_name:"Pinto"],[id:5,email:"[[email protected] /cdn-cgi/l/email-protection]",first_name:"Robert",last_name:"Peter"],[id:6,email:"[[email protected] /cdn-cgi/l/email-protection]",first_name:"Christian",last_name:"Lint"],[id:7,email:"[[email protected] /cdn-cgi/l/email-protection]",first_name:"Mike",last_name:"Hussey"],[id:8,email:"[[email protected] /cdn-cgi/l/email-protection]",first_name:"Ralph",last_name:"Hunter"]};

这样的 JSON 有 1000 条数据，我想使用 JPA 将其插入数据库中。目前我已经通过迭代插入它，这使得我的代码变慢，是否有任何其他可以实现的替代方案。

任何帮助将不胜感激。

这是我的Java代码：

     public Boolean multiEditPerson(List<PersonList> personList) {

        for (PersonList list : personList) {
            Person personMstr = em.find(Person.class, list.getId());
            personMstr.setFirstName(list.getFirstName());
            personMstr.setLastName(list.getLastName());
            personMstr.setEmail(Arrays.toString(list.getEmail()));
            em.persist(personMstr);
        }
        return Boolean.TRUE;
}

您可以根据json文档进行批量插入。您应该重新格式化文档，因为问题中显示的格式很奇怪且不切实际。

完整的工作示例：

create table example(id int primary key, email text, last_name text, first_name text);

with jsondata(jdata) as (
    values
    (
    '[
        {"id": 1, "email": "[[email protected] /cdn-cgi/l/email-protection]", "first_name": "John", "last_name": "Doe"},
        {"id": 2, "email": "[[email protected] /cdn-cgi/l/email-protection]", "first_name": "Robert", "last_name": "Duncan"},
        {"id": 3, "email": "[[email protected] /cdn-cgi/l/email-protection]", "first_name": "Ram", "last_name": "Das"},
        {"id": 4, "email": "[[email protected] /cdn-cgi/l/email-protection]", "first_name": "Albert", "last_name": "Pinto"},
        {"id": 5, "email": "[[email protected] /cdn-cgi/l/email-protection]", "first_name": "Robert", "last_name": "Peter"},
        {"id": 6, "email": "[[email protected] /cdn-cgi/l/email-protection]", "first_name": "Christian", "last_name": "Lint"},
        {"id": 7, "email": "[[email protected] /cdn-cgi/l/email-protection]", "first_name": "Mike", "last_name": "Hussey"},
        {"id": 8, "email": "[[email protected] /cdn-cgi/l/email-protection]", "first_name": "Ralph", "last_name": "Hunter"}
    ]'::jsonb)
)

insert into example 
select (elem->>'id')::int, elem->>'email', elem->>'last_name', elem->>'first_name'
from jsondata,
jsonb_array_elements(jdata) as elem;

结果：

select *
from example

 id |     email     | last_name | first_name 
----+---------------+-----------+------------
  1 | [[email protected] /cdn-cgi/l/email-protection] | Doe       | John
  2 | [[email protected] /cdn-cgi/l/email-protection] | Duncan    | Robert
  3 | [[email protected] /cdn-cgi/l/email-protection] | Das       | Ram
  4 | [[email protected] /cdn-cgi/l/email-protection] | Pinto     | Albert
  5 | [[email protected] /cdn-cgi/l/email-protection] | Peter     | Robert
  6 | [[email protected] /cdn-cgi/l/email-protection] | Lint      | Christian
  7 | [[email protected] /cdn-cgi/l/email-protection] | Hussey    | Mike
  8 | [[email protected] /cdn-cgi/l/email-protection] | Hunter    | Ralph
(8 rows)    

如果您想更新表（而不是插入表）：

with jsondata(jdata) as (
    -- values as above
)

update example set
    email = elem->>'email', 
    last_name = elem->>'last_name', 
    first_name = elem->>'first_name'
from jsondata,
jsonb_array_elements(jdata) as elem
where id = (elem->>'id')::int;