Update Mysql Column of JSON array via SQL

Question

I am storing a list of integers as JSON array inside the column called ConvertedIds inside a table SelectionLogs The type of column is MediumText and some of the example values of the column are [290766,319075,234525,325364,3472,34241,85643,11344556,88723,656378]

I am using following sql to generate the list of IDs from the column as rows

     SELECT hm.id FROM SelectionLogs hlog,
     JSON_TABLE(ConvertedIds, '$[*]' columns (Id int path '$')) AS hm

And then following query to extract further informations from other tables like

     SELECT hm.id,hc.firstname ,hc.lastname ,hc.email  FROM SelectionLogs hlog,
     JSON_TABLE(ConvertedIds, '$[*]' columns (Id int path '$')) AS hm        
     LEFT JOIN contacts hc ON hc.Id = hm.id

Now i have to update this column based on the presence of a given value of IDs For example if an ID exists in this column on any rows , i have to update the array after removing the ID

For example: [1,2,3,4,5,6,7] If ID : 3 exists , remove 3 and update column as [1,2,4,5,6,7]

I can use the following query to find the records from table SelectionLogs with given id present in column ConvertedIds

        SELECT DISTINCT hlog.Id FROM SelectionLogs hlog,
        JSON_TABLE(ConvertedIds, '$[*]' columns (Id int path '$')) AS hm        
        WHERE hm.id=427529

Now i have plans to iterate through each rows from my console program written in c#

             foreach row in result
              
               List<int> columnIds = read from column   ConvertedIds as list of int
               Remove the given int number from List 
               Update column ConvertedIds for given rowId  refreshed List

Can i perform the updation via SQL itself ?

For example: [1,2,3,4,5,6,7] If ID : 3 exists , remove 3 and update column as [1,2,4,5,6,7] I am trying to do this via an sql statement itself basically . I am not sure the detailed explanation causes more confusions to reader — Sebastian, Mar 09 '22 at 13:09

Akina · Answer 1 · 2022-03-09T13:26:03.740

DEMO fiddle with some explanations.

-- source data
CREATE TABLE t1 (id INT, val JSON) 
SELECT 1 id, '[1,2,3]' val UNION SELECT 2, '[3,4,5]' UNION SELECT 3, '[5,6,7]';

CREATE TABLE t2 (id INT) SELECT 1 id UNION SELECT 4;

-- UPDATE source table
UPDATE t1
JOIN ( SELECT t1.id, JSON_ARRAYAGG(jsontable.id) val
       FROM t1
       CROSS JOIN JSON_TABLE(t1.val, 
                            '$[*]' COLUMNS (id INT PATH '$')) jsontable
       LEFT JOIN t2 ON t2.id = jsontable.id
       WHERE t2.id IS NULL
       GROUP BY t1.id ) data_for_update USING (id)
SET t1.val = data_for_update.val;

Update Mysql Column of JSON array via SQL

1 Answers1