Question 231158 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Update json field in postgres table

I’m trying to add even more key : value data to existing json data. I get data from postgres with a query to the field and get a dictionary. After that, I simply add an additional key-value pair to the dictionary as follows:

conn=Connection()
a=conn.get_user_data(user_id)[0][0]
a['new']='data'

I get an updated dictionary with all the necessary fields:

a={'index': True, 'old':'data' , 'new':'data'}

After I try to populate the column with new data like this:

a = str(a)
a = a.replace("'",'"')
query = '''UPDATE table SET data = ('{}') '''.format(a)
conn.cursor.execute(query)
conn.connection.commit()

I am getting the following error:

psycopg2.errors.InvalidTextRepresentation: invalid input syntax for
type json LINE 1: UPDATE table SET data = (‘{"inde… ^ DETAIL: Token
"True" is invalid. CONTEXT: JSON data, line 1: {"index": True…

Is there any other way to update json field in postgresql or how can i fix my way given that ‘index’ contains BOOL True in db?

Tags:

2

Answers


  1. 0

    Do not manipulate the json as a string in your Python script.

    Also, use the parameter binding capabilities to avoid SQL injection.

    import json

a={'index': True, 'old':'data' , 'new':'data'}
query = '''UPDATE table SET data = %s '''
conn.cursor.execute(query, (json.dumps(a), ))
conn.commit()

    Do you need a where clause in your SQL? You know that this will update all rows in the table, right?

    Login or Signup to reply.
  2. 0

    Load your data as normalised tables, I suggest using this library https://pypi.org/project/dlt/

    You can even use this library to do an upsert on your structured data afterwards, here’s an example where we use an id in the json to update the sql table generated at destination

    
data = [{'id': 1, 'name': 'John'}]

# open connection
pipe = dlt.pipeline(destination='duckdb',
                    dataset_name='raw_data')

# Upsert/merge: Update old records, insert new
# Capture the outcome in load info
load_info = pipe.run(data,
                      write_disposition="merge",
                      primary_key="id",
                      table_name="users")
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search