英文:
How to remove a string element from an array everywhere in a SQLite table?
问题
UPDATE events
SET domain = json_remove(domain, "google")
编辑：
尝试使用以下代码：
UPDATE events
SET domain = REPLACE(domain, '"google", ', '')
WHERE domain LIKE '%"google"%';
但是引号的转义很困难，如果 "google" 在数组末尾的话是无法工作的（即没有逗号）。
英文:
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 ,)
答案1
得分: 0
UPDATE events
SET domain = '[ ' || TRIM(REPLACE(',', ',' || TRIM(json(domain), '[]') || ',', ',' || '"google"' || ','), ',') || ']'
WHERE domain LIKE '%"google"%';
See the demo.
英文:
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.<br/>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论