Using on_conflict_do_update to merge JSON data

[mcs...@gmail.com]

Using:

SQLAlchemy 1.2.1

PostgreSQL 10.6

I have a table with a pk and jsonb data and would like to do an upsert that merges the json data

Example SQL:

INSERT INTO json_data (id, data)
  VALUES (%(id)s, %(data)s)
  ON CONFLICT ON CONSTRAINT json_data_pk
  DO
  UPDATE SET data = json_data.data::jsonb || %(data)s;

Example python:

from sqlalchemy import Column, String, Table
from sqlalchemy.dialects.postgresql import insert, JSONB

table = Table(
    'json_data',
    SqlDatabase.get_metadata(),
    Column('id', String, primary_key=True),
    Column('data', JSONB, nullable=False),
    )

insert_stmt = insert(table).values(
    id="test",
    data={'key','value'}
    )
update_stmt = insert_stmt.on_conflict_do_update(
    constraint=table.primary_key,
    set_=dict(data=???)
    )

How do I reference the existing jsonb data and merge it with the new data in the on_conflict_do_update call?

Thanks,

Steve

[Mike Bayer]

note the cast to JSONB is probably not needed because that column is
already defined as JSONB. To reference a column, you just use it:
table.c.data.concat("my data")

see below

from sqlalchemy import Column, String, Table, MetaData

from sqlalchemy.dialects.postgresql import insert, JSONB

from sqlalchemy.dialects import postgresql

m = MetaData()

table = Table(
"json_data",

m,

Column("id", String, primary_key=True),
Column("data", JSONB, nullable=False),
)

insert_stmt = insert(table).values(id="test", data={"key", "value"})

update_stmt = insert_stmt.on_conflict_do_update(
constraint=table.primary_key,
set_=dict(data=table.c.data.concat("my data")),
)

# requested:

"""INSERT INTO json_data (id, data)
VALUES (%(id)s, %(data)s)
ON CONFLICT ON CONSTRAINT json_data_pk
DO
UPDATE SET data = json_data.data::jsonb || %(data)s;"""

# prints:

"""
INSERT INTO json_data (id, data) VALUES (%(id)s, %(data)s)

ON CONFLICT (id) DO UPDATE SET data = (json_data.data || %(data_1)s)
"""
print(update_stmt.compile(dialect=postgresql.dialect()))


# prints:

"""
INSERT INTO json_data (id, data) VALUES (%(id)s, %(data)s)
ON CONFLICT ON CONSTRAINT json_data_pk DO UPDATE SET data =

(json_data.data || %(data_1)s)
"""
update_stmt = insert_stmt.on_conflict_do_update(
constraint="json_data_pk", set_=dict(data=table.c.data.concat("my data"))
)
print(update_stmt.compile(dialect=postgresql.dialect()))







>
> Thanks,
> Steve
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
> ---
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.