Update JSONB using psycopg2

Question

The following SQL statement, towards a multi-schema PostgreSQL (v.12) DB, is a perfectly valid one and it achieves the intended result.

I need to update a deep JSONB value as shown below:

UPDATE schema."Some_table_name" SET jsonb_col = jsonb_set(jsonb_col, '{path,to,key}', '"string value"') WHERE id = 1;

When I try to do the above from python using psycopg2, I don't get any errors, but no update either.

def update_method(schema, path, value):
   q = f"""UPDATE {schema}."Some_table_name" SET jsonb_col = jsonb_set(jsonb_col, '{path}', '{value}') WHERE id = 1"""
   cur = conn.cursor()
   cur.execute(q)
   conn.commit()
   cur.close()

update_method('schema_name', '{path,to,key}', '"string value"')

I tried to specifically cast the path inside my q statement, as: '{path}'::text[]. Still no error, nor update.

Any idea how I can update a deep JSONB using psycopg2 and a method like the one above?

EDIT - after reading the comments and trying the code of @Abelisto, I've realized what would work (and I feel a bit silly). The following code updates correctly any deep JSONB value:

def update_method(connection, schema, path, value):
   q = f"""UPDATE {schema}."Some_table_name" SET jsonb_col = jsonb_set(jsonb_col, '{path}', '{value}') WHERE id = 1"""
   cur = connection.cursor()
   cur.execute(q)
   connection.commit()
   cur.close()

update_method(conn, 'schema_name', '{path,to,key}', '"string value"')

user_593144 · Answer

It is not the solution actually but it is hard to post the python code in the comments.

This question definitely need some debugging. IMO this code snippet should be enough to find the source of the issue:

#!/usr/bin/python3

import psycopg2

schema_name = 'pg_temp'
table_name = 'foo'
init_value = '{"a": "x", "b": {"c": "y"}}'

conn = psycopg2.connect('')

c = conn.cursor();
c.execute(f"""create table {schema_name}.{table_name}(id int, col jsonb)""")
c.execute(f"""insert into {schema_name}.{table_name} values(1, '{init_value}')""")
c.execute(f"""select * from {schema_name}.{table_name} where id = 1""")
print(c.fetchall())

def update_method(schema, path, value):
    qs = f"""select * from {schema}.{table_name} where id = 1"""
    q = f"""UPDATE {schema}.{table_name} SET col = jsonb_set(col, '{path}', '{value}') WHERE id = 1"""
    cur = conn.cursor()
    cur.execute(qs)
    print(cur.fetchall())
    print(q)
    cur.execute(q)
    cur.execute(qs)
    print(cur.fetchall())
    conn.commit()
    cur.close()

update_method(schema_name, '{b,c}', '"string value"')

Output:

[(1, {'a': 'x', 'b': {'c': 'y'}})]
[(1, {'a': 'x', 'b': {'c': 'y'}})]
UPDATE pg_temp.foo SET col = jsonb_set(col, '{b,c}', '"string value"') WHERE id = 1
[(1, {'a': 'x', 'b': {'c': 'string value'}})]

As you can see the code working for this simple test case.

Update JSONB using psycopg2

1 Answers