Nested curly brackets in Psycopg2 SQL Composition query-CodePudding

I'm trying to create a query using Pycopg2's SQL String Composition which in I need to use a curly brackets inside my query to update a key value in a jsonb column. Something like this:

update myschema.users set data = jsonb_set(data, '{someId}', '100')

This is how I'm trying to write this query using Sql Composition string in Python:

statement = SQL(
    "UPDATE {schema}.{table} set data = jsonb_set(data, '{{key}}', '{value}') {where};"
).format(
    schema=Identifier(schema_var),
    table=Identifier(table_var),
    key=SQL(id_key),
    value=SQL(id_value),
    where=SQL(where),
)

But by running this, a new key called key will be added in the jsonb value. and if I try to run it with just one pair of curly brackets like this:

statement = SQL(
    "UPDATE {schema}.{table} set data = jsonb_set(data, '{key}' ...." # The rest is the same

I get this error:

Array value must start with "{" or dimension information

How can I fix this?

CodePudding user response：

To solve this issue I needed to use three nested curly brackets like this:

statement = SQL(
    "UPDATE {schema}.{table} set data = jsonb_set(data, '{{{key}}}' ...." # The rest is the same

This way, the someId key will actually gets updated in the database.

CodePudding user response：

You are over thinking this.

Load data into table:

json_import
                              Table "public.json_import"
  Column   |  Type   | Collation | Nullable |                 Default                 
----------- --------- ----------- ---------- -----------------------------------------
 id        | integer |           | not null | nextval('json_import_id_seq'::regclass)
 jsonb_fld | jsonb   |           |          | 


insert into json_import values (1, '{"test": "dog"}'::jsonb);

 select * from json_import;                                  
 id |    jsonb_fld    
---- -----------------
  1 | {"test": "dog"}

import psycopg2
from psycopg2 import sql

con = psycopg2.connect("dbname=test user=postgres host=localhost port=5432")
cur = con.cursor()

sql_str = sql.SQL('update {table} set jsonb_fld = jsonb_set(jsonb_fld,
%(key)s, %(val)s) where id = 1').format(table=sql.Identifier('json_import'))

cur.execute(sql_str, {'key': '{test}', 'val': '"cat"'})

con.commit()

select * from json_import;  
 id |    jsonb_fld    
---- -----------------
  1 | {"test": "cat"}

The values for the jsonb_set() should be passed in as parameters not as part of the composition process.