UPDATE multiple values in column FROM JSON object

(1.1) By maraxai (maraca) on 2024-05-30 13:32:43 edited from 1.0 [source]

I want to update multiple values in the amount column of table t:

CREATE TABLE t (f_id INTEGER, t_id INTEGER, amount INTEGER, PRIMARY KEY(t_id));
INSERT INTO t VALUES (1,1,55), (1,2,66);
SELECT * from t;
gives me:
1|1|55
1|2|66

My JSON data looks like this and I place it for better readability into a variable.

data = {
    "f_id": 1,
    "obj": {
        "arr": [
              {"t_id": 1, "amount": 444}, 
              {"t_id": 2, "amount": 443} 
         ] 
    } 
}

This UPDATE statement deletes the values but does not update them:

UPDATE t
    SET amount = (
        SELECT json_extract(a.value, '$.amount')
        FROM (
            SELECT json_extract(a.value, '$.amount', '$.t_id') as arr
            FROM json_each(
                   '{"f_id": 1,"obj":{"arr":[{"t_id": 1, "amount": 444},{"t_id": 2, "amount": 443}]}}',
                   '$.obj.arr') as a
            WHERE t_id = json_extract(arr, '$[1]')
        ) q, json_each(arr) a
    )
WHERE f_id = json_extract(
        '{"f_id": 1,"obj":{"arr":[{"t_id": 1, "amount": 444},{"t_id": 2, "amount": 443}]}}',
        '$.f_id');

I am not familiar with SQLite and I am a beginner in SQL. Any help is greatly appreciated.

(2) By Dave Mausner (dmausner) on 2024-05-30 13:08:22 in reply to 1.0 [link] [source]

Your use of the variable "data" is puzzling.  When you place the word in single quotes, as in "json_extract('data', ... )" you are not passing the JSON value into the function;  you are instead passing the value comprised of the four characters d, a, t, and a.

Does that help?

(3) By maraxai (maraca) on 2024-05-30 13:25:22 in reply to 2 [link] [source]

Sorry for the confusion. I only used 'data' to indicate that there is a valid JSON object in this place. This is not the problem. I will update the post

(4) By punkish on 2024-05-30 15:14:07 in reply to 1.1 [link] [source]

I think when you extract the array with json_extract(a.value, '$.amount', '$.t_id') as arr, it has no key called amount. It is just an array that looks like so

[444, 1]
[443, 2]

So, the value of amount in json_extract(a.value, '$.amount') is NULL, and thus UPDATE dutifully sets each row's amount to NULL

(5.1) By maraxai (maraca) on 2024-05-30 17:26:31 edited from 5.0 in reply to 4 [link] [source]

If I change this line to json_extract(a.value,'$') as arr, I get the complete object with keys and values - 
{"t_id":1,"amount":444}
{"t_id":2,"amount":443}
{"t_id":3,"amount":666}

But somehow the values get lost in 
json_each(arr) a 

Because json_extract(a.value, '$.amount') reads NULL as you explained.

Do you have an idea how to fix the UPDATE statement?

(6) By punkish on 2024-06-02 19:44:27 in reply to 5.1 [link] [source]

try the following

UPDATE t
    SET amount = (
        SELECT json_extract(a.value, '$.amount') AS amount 
        FROM json_each(
            '{"f_id": 1,"obj":{"arr":[{"t_id": 1, "amount": 444},{"t_id": 2, "amount": 443}]}}',
            '$.obj.arr') AS a
        )
WHERE f_id = json_extract(
    '{"f_id": 1,"obj":{"arr":[{"t_id": 1, "amount": 444},{"t_id": 2, "amount": 443}]}}',
    '$.f_id');

(7) By maraxai (maraca) on 2024-06-04 18:07:03 in reply to 6 [link] [source]

This works :D Thank you so much!