Создать SQL запрос на обновление нескольких значений JSON-поля

Question

Подскажите метод для обновления данных в PostgreSQL в jsonb поле, когда есть несколько значений без вложенности. Пример json, который хранится:

{
    "dfg": 12,
    "sdf": null,
    "sdv": null,
    "fdgn": null,
    "nhgn": null,
    "timestamp": 1621602047.921123
}

с помощью python и psycopg2-модуля составляю запрос на обновление поля.

import json
import psycopg2

def save_last_state(macAddress, payload):
    conn = psycopg2.connect(dbname=DB_DATABASE,user= DB_USERNAME,password= DB_PASSWORD)
    payload_dict = json.loads(payload)
    execute = "UPDATE mainrole_enhet SET registrer_state = jsonb_set(registrer_state::jsonb"
    for key, value in payload_dict.items():
        execute += ", '{" + key + "}', '" + f"{value}" + "'"
    execute += f' ) WHERE "macAddress"=\'{macAddress}\';'
    print(execute)
    try:
        cur = conn.cursor()
        cur.execute(execute)
        conn.commit()
        cur.close()
        conn.close()
    except psycopg2.Error as e:
        logger.error(e)

execute- переменная, которая содержит запрос ниже замечательно работает:

UPDATE mainrole_enhet SET registrer_state = jsonb_set(registrer_state::jsonb, '{dfg}', '12' ) WHERE "macAddress"='111111111111';

Но если в переменную payload я передаю больше одной пары ключ:значение и запрос выглядит вот так:

UPDATE mainrole_enhet SET registrer_state = jsonb_set(registrer_state::jsonb, '{dfg}', '12', '{sdf}', '10' ) WHERE "macAddress"='111111111111';

то получается следующее:

No function matches the given name and argument types. You might need to add explicit type casts.

Понятно, что не предусмтрены запросы с несколькими значениями, но тогда как?

Answer 1

Решение оказалось достаточно простым и элегантным. В документации я нашел операцию для конкатенации || и решил попробовать.

Код не чистый, но рабочий:

import json
import psycopg2
from datetime import datetime

def save_last_state(macAddress,topic, payload):
    conn = psycopg2.connect(dbname=DB_DATABASE,user= DB_USERNAME,password= DB_PASSWORD)
    payload_dict = json.loads(payload)
    execute = "UPDATE mainrole_enhet SET registrer_state = registrer_state "
    for key, value in payload_dict.items():
        execute += "|| '{\"" + key + "\":\"" + f"{value}" + "\"}'"
    # здесь я добавил как дополнительное значение timestamp
    execute += "|| '{\"timestamp\":\"" + str(datetime.now().timestamp()) + "\"}'"
    execute += f' WHERE "macAddress"=\'{macAddress}\';'
    print(execute)
    try:
        cur = conn.cursor()
        cur.execute(execute)
        conn.commit()
        cur.close()
        conn.close()
    except psycopg2.Error as e:
        logger.error(e)

Запрос к базе данных выглядит следующим образом:

UPDATE mainrole_enhet SET registrer_state = registrer_state || '{"dfg":"100"}'|| '{"timestamp":"1621704818.328344"}' WHERE "macAddress"='111111111111';

ну, и ожидаемый результат из базы данных:

{
    "dfg": "100",
    "sdf": null,
    "sdv": null,
    "fdgn": null,
    "nhgn": null,
    "timestamp": "1621705382.791411"
}

БЛОГ НА HUSL

Создать SQL запрос на обновление нескольких значений JSON-поля

Ответы (1 шт):