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
                )

I am trying to edit an item (several items) in items json list, based on said item's properties. 'items' structure:

[
{
    "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 tried:

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

This result in item's list becoming a single dict with just the edited object:

{
    "am_pm": "AM",
    "brand_": "Teq",
    "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"
}

I am at a loss here. The json ddocumentation shows no example of effectively using json_set/json_replace. I'm trying to think of a way to loop through each element of the array and update it if conditions are met (with json_each) however I fail to write a correct statement.

How can an item from a list of items be updated?

Without going into great detail, I think what you need to do is to use two CTEs (common table expressions, i.e., WITH blocks):

• The first one should use json_each together with json_set, more or less like you did, to produce a table of patched entries for your json array.
• The second should use the json_group_array function to aggregate the results of the first into a single json array,
• and then you can use that single value in your UPDATE statement.

Disclaimer: There may well be dragons lurking here. I have never used json_group_array() myself, so I cannot vouch for my proposed usage. Good luck.

Ty for replying. I cannot wrap my head around it: even if I would write multiple CTEs, the first one, or the one with json_set (CTE1) would destroy the original list of dicts, and replace it with a single dict; I would then, in CTE2, have to concatenate it with the original array, then remove the original dict edited... ? I am utterly lost here, and my complexity budget is shot.

I hoped, and still do, that there is a simpler, more straight forward way of updating nested value in a json list. Right now I'm trying to determine the key for the element in list to be updated, and then to .. update the table with the new values (here is where I'm lost). This is my updated code:

select json_each.key, json_each.value, json_set(items, '$['||key||'].uuid_code',

'updated_uuid_code') from orders, json_each(orders.items, '$')

where orders.id = x and json_extract(value, '$.item_id') = y

This returns a table with 'key', 'value', and 'json_set(items, '$['

key

'].uuid_code' columns, the last column containing the desired result, the list of dicts with only the correct dict updated. How do I actually update the table?

I like your notion of a complexity budget. I agree, this is a bit complicated. I think you might be better off not trying to hand off so much work to json arrays, but instead improve your database schema by introducing a table order_items or something like that, with the order id and one item from each order per row.

But anyhow, after a bit of fiddling, I came up with a proof of concept for your original question, I think. I simplified the database schema to just contain the essentials. I hope it makes sense. Here is the sql code:

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  items TEXT
);

INSERT INTO orders(id,items)
VALUES
  (1,json_array(
    json_object('b','BBB','s','SSS','o','some stuff'),
    json_object('b','CCC', 's','TTT', 'o','some other stuff'))),
  (2,json_array(
    json_object('b','DDD', 's','UUU', 'o','and so on')));

SELECT * FROM orders;

WITH
items AS (
  SELECT orders.id,
    json_set(items.value,'$.b',
    CASE json_extract(items.value,'$.s')
      WHEN 'SSS' THEN 'replacement'
      ELSE json_extract(items.VALUE,'$.b')
    END) value
  FROM orders, json_each(items, '$') items
  WHERE orders.id=1),
updated AS (SELECT id, json_group_array(json(value)) items FROM items)
UPDATE orders
  SET items = updated.items
  FROM updated
WHERE orders.id = updated.id;

SELECT * FROM orders;

and here is the output when I run it. Note two SELECTS, showing the orders table before and after the substitution:

┌────┬──────────────────────────────────────────────────────────────┐
│ id │                            items                             │
├────┼──────────────────────────────────────────────────────────────┤
│ 1  │ [{"b":"BBB","s":"SSS","o":"some stuff"},{"b":"CCC","s":"TTT" │
│    │ ,"o":"some other stuff"}]                                    │
├────┼──────────────────────────────────────────────────────────────┤
│ 2  │ [{"b":"DDD","s":"UUU","o":"and so on"}]                      │
└────┴──────────────────────────────────────────────────────────────┘
┌────┬──────────────────────────────────────────────────────────────┐
│ id │                            items                             │
├────┼──────────────────────────────────────────────────────────────┤
│ 1  │ [{"b":"replacement","s":"SSS","o":"some stuff"},{"b":"CCC"," │
│    │ s":"TTT","o":"some other stuff"}]                            │
├────┼──────────────────────────────────────────────────────────────┤
│ 2  │ [{"b":"DDD","s":"UUU","o":"and so on"}]                      │
└────┴──────────────────────────────────────────────────────────────┘

Thank you. The whole point of using JSON was to avoid a db schema with foreign key, multiple inserts, joins, and the enticing promise of having a nosql db with no overheads in your pocket.

Using the json_group_array looks complex, something I'm trying to avoid (otherwise I would have used Postgres/Mongo/..).

I managed to write a viable statement (with a CTE), but that will change a single item (i.e, if I filter for item.supplier and I want tp change item.brand and there are more than one item with the same supplier, it will only change the first item. My solution looks less elegant than the one posted as a response, below (the one with 'fullkey'):

with cte_one as (
	select json_each.key, json_each.value, json_set(items, '$['||key||'].uuid_code', 'updated_uuid_code', '$['||key||'].delivery_date', 'updated delivery_date') as updated_json_set_result 
		from orders, json_each(orders.items, '$')
	where orders.id = 2 and json_extract(value, '$.item_id') = '1144fbbc-14f4-4682-b040-98abfa122a25'
	) 
	update orders set items = (select updated_json_set_result from cte_one)
		where orders.id = 2

It's the same result like the solution posted below (the one with 'fullkey'). I feel I'm missing something, and there is a more straightforward way of handling this json update.

Well, super convenience often comes at the cost of underlying complexity, that's just a fact of life. Only you, knowing the application, can properly weigh the pros and cons of different approaches.

Perhaps an option is to just repeat the update statement you came up with, until nothing changes anymore? If this is an operation you do relatively rarely, and it only involves a few items in each order, it may be good enough. Other than that, I don't think you can get a much simpler solution than the one I offered, without a change in database schema.

If you do choose to change the schema, though, don't forget the possibility of creating a VIEW containing a more convenient presentation of your data.

Another reason for going the json column way was the fact I could then write different views, showing different aspects/calculations for each order, or group of items, per brand, client, delivery date and so on. Json option implementation in Sqlite appears to be geared towards read, not necessarily towards write/update. Array items do not seem to exist per se, only as part of the text, and changing several items at once doesn't seem to be possible outside recreating the whole array with json_group_array, including both unchanged, and updated items. Whether this is reasonable, or unreasonable complexity for a relatively straightforward concept (update an array), is debatable. ... unless I'm missiing something, which I suspect I do. There really must be a way of editing several items at once in the array, then returning a single row with all the changes, as opposed to returning several rows, one per change, each subsequent row not including the previous change.

Thank you for your solution. I was not aware of 'fullkey' concept. This solution works for the first item in order dictionary: if there are multiple items from the same supplier, only the first will be updated.

CORRECTION: This will mess up the dict array, adding more items to it (which I cannot fully explain why). In order to work as intended, it should specify the orders.id within the json_set brackets:

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