0

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.

  • Which version of Postgres are you using? Newer versions have extensive JSON support and you may be able to get the server to do it for you. – Colin 't Hart Feb 12 at 13:15
2

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

|improve this answer|||||

Not the answer you're looking for? Browse other questions tagged or ask your own question.