update multiple rows in one query but the input we are expecting is from json object of multiple data

Question

    update users as u set -- postgres FTW
    email = u2.email,
    first_name = u2.first_name,
    last_name = u2.last_name
    from (values
   (1, 'hollis@weimann.biz', 'Hollis', 'O\'Connell'),
   (2, 'robert@duncan.info', 'Robert', 'Duncan')
    ) as u2(id, email, first_name, last_name)
    where u2.id = u.id;

Above query is used to update multiple rows in one query and it works efficiently as well but I have a JSON below:

   Person:{[id:1,email:"[xyz@abc.com]",first_name:"John",last_name:"Doe"],[id:2,email:"[xyz@abc.com]",first_name:"Robert",last_name:"Duncan"],[id:3,email:"[xyz@abc.com]",first_name:"Ram",last_name:"Das"],[id:4,email:"[xyz@abc.com]",first_name:"Albert",last_name:"Pinto"],[id:5,email:"[xyz@abc.com]",first_name:"Robert",last_name:"Peter"],[id:6,email:"[xyz@abc.com]",first_name:"Christian",last_name:"Lint"],[id:7,email:"[xyz@abc.com]",first_name:"Mike",last_name:"Hussey"],[id:8,email:"[xyz@abc.com]",first_name:"Ralph",last_name:"Hunter"]};

Such JSON has 1000 data which I want to insert in the Database using JPA. Currently I have inserted it by iterating this which makes my code slow,Is there any other alternative which can be implemented.

Any help will be appreciated.

Here is my Java Code :

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

Answer 1

Thanks for the reply to everyone !!

I have used below query to update records using JSON directly in the db.

    UPDATE person p
                SET (first_name,email,last_name)= 
                (COALESCE(ab.first_name, p.first_name)
                , COALESCE(ab.email,p.email)
                ,COALESCE(ab.last_name, p.last_name)
                     )
                FROM (
                  select * from  json_populate_recordset 
                (null::person,'[{"id":1,"first_name":"Robert","email":"robert.stark@xyz.com","last_name":"Stark"},{"id":2,"first_name":"John","email":"John.Doe@xyz.com","last_name":"Doe"}]')
                  ) ab
                WHERE p.id = ab.id;

Answer 2

You can do a bulk insert based on the json document. You should reformat the document as the format shown in the question is strange and unpractical.

Full working example:

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

with jsondata(jdata) as (
    values
    (
    '[
        {"id": 1, "email": "[xyz@abc.com]", "first_name": "John", "last_name": "Doe"},
        {"id": 2, "email": "[xyz@abc.com]", "first_name": "Robert", "last_name": "Duncan"},
        {"id": 3, "email": "[xyz@abc.com]", "first_name": "Ram", "last_name": "Das"},
        {"id": 4, "email": "[xyz@abc.com]", "first_name": "Albert", "last_name": "Pinto"},
        {"id": 5, "email": "[xyz@abc.com]", "first_name": "Robert", "last_name": "Peter"},
        {"id": 6, "email": "[xyz@abc.com]", "first_name": "Christian", "last_name": "Lint"},
        {"id": 7, "email": "[xyz@abc.com]", "first_name": "Mike", "last_name": "Hussey"},
        {"id": 8, "email": "[xyz@abc.com]", "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;

The result:

select *
from example

 id |     email     | last_name | first_name 
----+---------------+-----------+------------
  1 | [xyz@abc.com] | Doe       | John
  2 | [xyz@abc.com] | Duncan    | Robert
  3 | [xyz@abc.com] | Das       | Ram
  4 | [xyz@abc.com] | Pinto     | Albert
  5 | [xyz@abc.com] | Peter     | Robert
  6 | [xyz@abc.com] | Lint      | Christian
  7 | [xyz@abc.com] | Hussey    | Mike
  8 | [xyz@abc.com] | Hunter    | Ralph
(8 rows)    

---

If you want to update the table (instead of insert into it):

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;

Answer 3

The trick is to do a patch insertion without commit for each record. If this is a one time job, it is better to process it on the PostgreSQL side, insert the Json entries all at once to the database using unlogged table and then update you main table.

here an exmaple from documentation to change a json to rwos

select * from json_each('{"a":"foo", "b":"bar"}')

if this is not one time job, you need to create a batch insert in your java code. Do not process one person at a time but the whole list of persons.