How to update only 1 attribute of json type column in postgres table using python api code

Question

I have a postgresql(v.9.5) table called products defined using sqlalchemy core as:

products = Table("products", metadata,
                 Column("id", Integer, primary_key=True),
                 Column("name", String, nullable=False, unique=True),
                 Column("description", String),
                 Column("list_price", Float),
                 Column("xdata", JSON))

Assume the date in the table is added as follows:

id |    name    |        description        | list_price |             xdata              
----+------------+---------------------------+------------+--------------------------------
 24 | Product323 | description of product332 |       6000 | [{"category": 1, "uom": "kg"}]

Using API edit code as follows:

def edit_product(product_id):
    if 'id' in session:
        exist_data = {}
        mkeys = []
        s = select([products]).where(products.c.id == product_id)
        rs = g.conn.execute(s)
        if rs.rowcount == 1:
            data = request.get_json(force=True)
            for r in rs:
                exist_data = dict(r)
            try:
                print exist_data, 'exist_data'
                stmt = products.update().values(data).\
                       where(products.c.id == product_id)
                rs1 = g.conn.execute(stmt)
                return jsonify({'id': "Product details modified"}), 204
            except Exception, e:
                print e
                return jsonify(
                    {'message': "Couldn't modify details / Duplicate"}), 400

    return jsonify({'message': "UNAUTHORIZED"}), 401

Assuming that I would like to modify only the "category" value in xdata column of the table, without disturbing the "uom" attribute and its value, which is the best way to achieve it? I have tried the 'for loop' to get the attributes of the existing values, then checking with the passed attribute value changes to update. I am sure there is a better way than this. Please revert with the changes required to simplify this

Iiuc, use `jsonb_set()` in an UPDATE statement. Your question is a bit light on actual details, like what's `data` like. — Ilja Everilä, May 24 '18 at 10:01
data is the values passed eg in curlcurl -b cookies.txt -X PUT http://localhost:8081/api/v1.0/products/24 -d '{"list_price":70000.00, "xdata":[{"category":5}]}' -H 'Content-type:application/json': How would I use the jsonb_set in sqlalchemy core only ? — user956424, May 24 '18 at 11:30
Related: https://stackoverflow.com/questions/35897834/sqlalchemyflaskpostgres-how-to-update-only-a-specific-attribute-of-a-json-f — Ilja Everilä, May 24 '18 at 12:03

Ilja Everilä · Answer 1 · 2018-05-24T12:19:59.280

Postgresql offers the function jsonb_set() for replacing a part of a jsonb with a new value. Your column is using the json type, but a simple cast will take care of that.

from sqlalchemy.dialects.postgresql import JSON, JSONB, array
import json

def edit_product(product_id):
    ...

    # If xdata is present, replace with a jsonb_set() function expression
    if 'xdata' in data:
        # Hard coded path that expects a certain structure
        data['xdata'] = func.jsonb_set(
            products.c.xdata.cast(JSONB),
            array(['0', 'category']),
            # A bit ugly, yes, but the 3rd argument to jsonb_set() has type
            # jsonb, and so the passed literal must be convertible to that
            json.dumps(data['xdata'][0]['category'])).cast(JSON)

You could also device a generic helper that creates nested calls to jsonb_set(), given some structure:

import json

from sqlalchemy.dialects.postgresql import array

def to_jsonb_set(target, value, create_missing=True, path=()):
    expr = target

    if isinstance(value, dict):
        for k, v in value.items():
            expr = to_jsonb_set(expr, v, create_missing, (*path, k))

    elif isinstance(value, list):
        for i, v in enumerate(value):
            expr = to_jsonb_set(expr, v, create_missing, (*path, i))

    else:
        expr = func.jsonb_set(
            expr,
            array([str(p) for p in path]),
            json.dumps(value),
            create_missing)

    return expr

but that's probably overdoing it.

How to update only 1 attribute of json type column in postgres table using python api code

1 Answers1