[FIXED] How to append JSON fields in sqlalchemy statements

Issue

How to update JSON field in sqlalchemy appending another json?

    stmt = pg_insert(Users).values(
        userid=user.id,
        pricesjson=[{
            "product1": user.product1,
            "product2": user.product2,
            "product3": user.product3
        }],
        tsins=datetime.now()
    )
    stmtUpsert = stmt.on_conflict_do_update(index_elements=[Users.userid],
                                            set_={'pricesjson': cast({"product1": user.product1,
                                                                      "product2": user.product2,
                                                                      "product3": user.product3
                                                                     } +
                                                                     cast(stmt.excluded.pricesjson, JSONB),
                                                                     JSON)
                                                , 'tsvar': datetime.now()})

In that way i don't receive errors but overwrite json field without append. Thank you ;)

Solution

Solved: After altered the field on table from json to jsonb, that's the working code:

stmtUpsert = stmt.on_conflict_do_update(index_elements=[Users.userid],
                                                  set_={'pricesjson': cast([{"product1": user.product1,
                                                                       "product2": user.product2,
                                                                       "product3": user.product3
                                                                      ], JSONB) + Users.pricesjson
                                                , 'tsvar': datetime.now()})

That's the relative sample query:

insert into users (userid, pricesjson) values('1',  '{"product1": "test1", product2": "test2"}') 
on conflict (userid) 
do update set pricesjson =cast('[{"productX": "testX"}]' as jsonb) || securitiesprices.pricesjson

Answered By - Damiano Dotto

Answer Checked By - Marilyn (PHPFixing Volunteer)