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

Nested curly brackets in Psycopg2 SQL Composition query – Postgresql

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?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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.


  2. 0

    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.

    UPDATE

    Using same sql_str and assigning the values to variables.

    key_val = '{test}'
fld_val = '"cat"'

cur.execute(sql_str, {'key': key_val, 'val': fld_val})

con.commit()

cur.execute("select * from json_import")
cur.fetchone()

(1, {'test': 'cat'})
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search