使用相同的值更新列,但不更新在Sqlite的列中删除的对象

vuktfyat  于 2022-11-24  发布在  SQLite
关注(0)|答案(1)|浏览(191)

我想从Sqlite中的json列中删除一个对象,但我无法使其工作。json列包含一个嵌套对象,具有以下类型:

{
  a: number;
  pair: { 
     field1: string;
     field2: string;
  }[]
}

我想用相同的值更新列"ArrayColumn",但删除field1等于"0"field2等于"1"的对象。每行都包含"pair"数组,但ArrayColumn中的所有"pair"数组并不都包含此值({"field1":"0", "field2":"1"})
我有以下结构:

Id| ArrayColumn
--------------------------------------------------------------------------------------------
1 | { "a":1, "pair":[{"field1":"0", "field2":"1"},{"field1":"C", "field2":"D"},{"field1":"E", "field2":"F"}] }
2 | { "a":5, "pair":[{"field1":"C", "field2":"D"},{"field1":"E", "field2":"F"}] }  
3 | { "a":8, "pair":[{"field1":"G", "field2":"G"},{"field1":"0", "field2":"1"},{"field1":"A", "field2":"A"}] } 
4 | { "a":1, "pair":[{"field1":"F", "field2":"T"},{"field1":"C", "field2":"D"},{"field1":"0", "field2":"1"}] } 
5 | { "a":1, "pair":[{"field1":"A", "field2":"B"}] }

更新行后,值将为:

Id| ArrayColumn
--------------------------------------------------------------------------------------------
1 | { "a":1, "pair":[{"field1":"C", "field2":"D"},{"field1":"E", "field2":"F"}] }
2 | { "a":5, "pair":[{"field1":"C", "field2":"D"},{"field1":"E", "field2":"F"}] }  
3 | { "a":8, "pair":[{"field1":"G", "field2":"G"},{"field1":"A", "field2":"A"}] } 
4 | { "a":1, "pair":[{"field1":"F", "field2":"T"},{"field1":"C", "field2":"D"}] } 
5 | { "a":1, "pair":[{"field1":"A", "field2":"B"}] }

我尝试使用JSON_TREE,但无法使其工作。
我认为第一步应该是选择包含该值的所有行,我使用以下两种方法检索它们:
1.使用LIKE运算符搜索字符串化表单:
select Id, json_extract(json(par), '$.pair') as pair from Table pair like '%{"field1":"0","field2":"1"}%'
1.使用json_tree
select Id, value from Table, json_tree(Table.ArrayColumn, '$.pair' ) where json_extract(value, '$.field1' ) = '0' AND json_extract(value, '$.field2' ) = '1'
我试着在这个小例子中使用json_remove,但是没有成功:SELECT json_remove('[{"field1":"1","field2":"0"},{"field1":"A","field2":"B"}]', '${"field1":"1","field2":"0"}' )
我尝试使用json_remove,但没有成功。谢谢

9lowa7mx

9lowa7mx1#

对于此示例数据,最简单的方法是将json列视为字符串,并使用字符串函数删除所需的值:

UPDATE tablename
SET ArrayColumn = REPLACE(REPLACE(REPLACE(ArrayColumn, ']', ',]'), '{"field1":"0", "field2":"1"},', ''), ',]', ']')
WHERE ArrayColumn LIKE '%{"field1":"0", "field2":"1"}%';

请参阅demo

相关问题