json_set returns desired result (in SQLITE Browser) but does not update the table

Question

This is my table:

CREATE TABLE orders(
                id integer primary key,
                order_uid text unique,
                created_at text,
                updated_at text,
                created_by text,
                updated_by text,
                client text,
                phone text,
                device text,
                items json,
                comments text
                )

'items' is a list of dictionaries - valid json. This is what 'items' looks like:

[
{
    "am_pm": "AM",
    "brand_": "EEE",
    "quantity": 8,
    "code": "1-936331-67-5",
    "delivery_date": "2020-04-19",
    "supplier": "XXX",
    "part_uid": "645039eb-82f4-4eed-b5f9-115b09679c66",
    "name": "WWWWWW",
    "price": 657,
    "status": "Not delivered"
},
{
    "am_pm": "AM",
    "brand_": "DDDDDDD",
    ...
},
...
]

This is what I'm running (in 'execute sql' tab in sqlitebrowser V. 3.11.2, SQLite version 3.31.1), and it looks like it returns the desired results, however not reflected in the actual table, it doesn't update it:

    select json_set(value, "$.am_pm", "Tequilla") from orders, json_each(orders.items, '$')
where orders.id=2 and json_extract(value, '$.part_uid') = '35f81391-392b-4d5d-94b4-a5639bba8591'

I also ran

    update orders
set items = (select json_set(orders.items, '$.am_pm', "Tequilla") from orders, json_each(orders.items, '$'))
where orders.id=2

With the result being - it deleted the list of dicts and replaced it with a single dict, with the 'am_pm' field updated.

What is the correct sql statement, so I can update a single (or several) object/s in 'items'?

score 1 · Accepted Answer · answered Jul 05 '22 at 19:20

After much fiddling and posting on Sqlite forums as well, the optimal solution seems to be:

update orders
set items = (select json_set(items, fullkey||'.brand_', 'Teq')
from orders, json_each(items, '$')
where json_extract(value, '$.supplier') = 'XXX' and orders.id = 1)
where orders.id = 1

This will only update a single item in the json array, even if multiple items meet the criteria. It would be helpful if someone more experienced in Sqlite could come up with a solution of updating multiple elements in a json array at once.

json_set returns desired result (in SQLITE Browser) but does not update the table

1 Answers1