Insert Array of JSON-s into Postgres table I have an array containing JSON of the following form:

[
  {
    "country": "ANY",
    "category": "poetry"
  },
  {
    "country": "ES",
    "category": "science"
  }
]


What I currently do is - iterate over the JSON-s in the Array in Python and insert them into the DB table one by one like so:

sql = 'insert into table_name (country, category) values (%s, %s)'
vars = (country, category)
app_utils.run_query_pool_commit(con_pool, sql, vars)


I'm curious if there's a more elegant way of doing such kind of bulk inserts without having to make a loop and inserting the objects one by one.

Thanks.
there are many json functions built into postgresql

the function you are looking for json_to_recordset()  the command will look something like this

insert into table_name (country, category) 
   select * from json_to_recordset( '[
  {
     "country": "ANY",
     "category": "poetry"
   },
   {
      "country": "ES",
      "category": "science"
    }
   ]') as x(country text, category text);


On how elegant this is does not matter,  something has to parse the json text and turn it into a record,  postgresql may be able to do it faster than python.
Neither side looks pretty and neither side is fast because of the casting and text parsing that is going on