Update column with the same value apart from an object removed in column in Sqlite

Question

I want to remove an object from a json column in Sqlite and I can't make it work. The json column contains a nested object, has the following type:

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

I want to update the column "ArrayColumn" with the same values but remove the object that has field1 equal to "0" and field2 equal to "1" . Every row contains the "pair" array, but not all the "pair" arrays in ArrayColumn contain this value ({"field1":"0", "field2":"1"})

I have the following structure:

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"}] } 

After updating the rows, the values would be:

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"}] } 

I tried with JSON_TREE but can't make it work.

I was thinking that the first step would be to select all the rows that contain that value, I retreived them using these 2 ways:

1. With LIKE operator searching for the stringified form:

select Id, json_extract(json(par), '$.pair') as pair from Table pair like '%{"field1":"0","field2":"1"}%'

2. Using json_tree

select Id, value from Table, json_tree(Table.ArrayColumn, '$.pair' ) where json_extract(value, '$.field1' ) = '0' AND json_extract(value, '$.field2' ) = '1'

I tried using json_remove with this small example but no luck: SELECT json_remove('[{"field1":"1","field2":"0"},{"field1":"A","field2":"B"}]', '${"field1":"1","field2":"0"}' )

I tried using json_remove but had no luck. Thank you

Answer 1

For this sample data the simplest way to do this is to treat the json column as a string and use string functions to remove the value that you want:

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

See the demo.