Mysql Update JSON Int Array Column

Question

I got a table with below configurations

Table: jsontesttable Column : ArrayCol of type Medium Text Sample value: [123,45,67,85,78]

I use the below query to select List of IDS except one of the selected IDs

/*query to select list of IDs from the column and remove unwanted columns from the selection */

SELECT hm.Id FROM  jsontesttable tbl,
JSON_TABLE(ArrayCol, '$[*]' columns (Id int path '$')) AS hm
WHERE hm.Id NOT IN (67,85)

I used below query to get the JSON array back but its treating as string at present

/*Convert the IDs back to a JSON int array like [123,45,78] */

SELECT JSON_ARRAY(GROUP_CONCAT(hm.Id SEPARATOR ',')) AS IDs FROM   jsontesttable tbl,
JSON_TABLE(ArrayCol, '$[*]' columns (Id int path '$')) AS hm
WHERE hm.Id NOT IN (67,85)

But its generating rows like this only Not really needed to have " in the beggining and end

//["123,45,78,88,9,3,53,6"] //["83,97"]

/*Update the ArrayCol , column as the new array without the removed values! 
Perform this updation only if ArrayCol contains one of these IDs in thisc ase it is 67 & 85 
*/

Can i write direct UPDATE query to modify the column without the IDs that needs to be removed like

[123,45,67,85,78]  => [123,45,78]   //2 IDs changed
[67,222,14] => [222,14] //Just 1 ID is changed 
[83,85,97] => [83,97]   //Just 1 ID is changed
[21,12,17,19]  =>  [21,12,17,19]  //No change

Akina · Accepted Answer · 2022-03-21 12:17:52Z

1

WITH
cte AS ( SELECT ArrayCol, ROW_NUMBER() OVER () rn
         FROM jsontesttable )
SELECT rn, JSON_ARRAYAGG(hm.Id) AS IDs 
FROM   cte
CROSS JOIN JSON_TABLE(ArrayCol, '$[*]' columns (Id int path '$')) AS hm
WHERE hm.Id NOT IN (67,85)
GROUP BY rn

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=ce067ef12d9ed0d0ef1866e0a32d2a40

answered Mar 21, 2022 at 12:17

Akina

43.2k6 gold badges17 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Mysql Update JSON Int Array Column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related