arrayssqlitejson-remove

How to remove a string element from an array everywhere in a SQLite table?

Considering a SQLite table where one column (named domain) is an array of strings with no particular index/order.
Example: ["google", "apple", "twitter"]

How can I update the all table to delete all google items?
In my example, result should be ["apple", "twitter"].

I've started with:

UPDATE events
SET domain = json_remove(domain, "google")

But (1) it doesn't work and (2) it seems that I must use index with json_remove.

See this DBFiddle.

EDIT

I tried with:

UPDATE events
SET domain = REPLACE(domain, '"google", ', '')
WHERE domain LIKE '%"google"%';

...but quote escaping is hard and it doesn't work if google is at the end of the array (= there is no ,)

Solution

  • The manipulation of the json array as a string is tricky, but it is feasible:

    UPDATE events
SET domain = '[' || TRIM(REPLACE(',' || TRIM(json(domain), '[]') || ',', ',' || '"google"' || ',', ','), ',') || ']'
WHERE domain LIKE '%"google"%';

    See the demo.