json_remove is removing the wrong value from array on second run

user_1778465 0
Viewed 69

I am trying to remove a value from a json array, however I am having issues.

First I insert the array into into the table like so:

INSERT into demo (hint) VALUES ('["Hello","World"]');

Next when I run this query, the World value gets removed which is what is supposed to happen. However if I run it a second time, then the Hello value gets removed which is not supposed to happen. What am I doing wrong, or what is a better way to remove items from a json array?

UPDATE demo SET hint = json_remove(
    hint,
    (SELECT json_each.fullkey FROM demo, json_each(demo.hint) WHERE json_each.value = 'World')
);

select * from demo;
sql-update sqlite sql json database
1 Answers

The subquery that you use as the path argument of json_remove() returns null the 2nd time that you execute the update statement because there is no "World" in the json array.
In this case json_remove() also returns null.

If your version of SQLite is 3.33.0+ you can use the UPDATE...FROM syntax:

UPDATE demo AS d
SET hint = json_remove(d.hint, j.fullkey)
FROM json_each(d.hint) AS j
WHERE j.value = 'Hello';
user_104988280
Related