MYSQL Update only one key:value inside a json array

Ask Time：2014-11-12T22:38:04 Author：jonnypixel

I need to be bale to reset just one value for one json key

{
"newsletter":"1",
"contactcategories":"40",
"newslettermessagetitle":"Hey hey its  the monkeys",
"newslettermessage":"And we got something to say...."
}

I'm trying to work out how i can format a MYSQL UPDATE query that will leave everything alone in that json but only change the 1 to 0 for the newsletter key.

So basically this ends up my result:

{
"newsletter":"0",
"contactcategories":"40",
"newslettermessagetitle":"Hey hey its  the monkeys",
"newslettermessage":"And we got something to say...."
}

I have this so far but cant wrap my head around it. Is it even possible to update only one json key and value using a mysql query?

UPDATE #__table 
SET params='(SELECT *
            FROM #__table
            WHERE params LIKE '%\"newsletter\":\"1\"%')'i WHERE id='$id'

Thanks to any one who can help :) Jonny

Author:jonnypixel，eproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article：https://stackoverflow.com/questions/26889547/mysql-update-only-one-keyvalue-inside-a-json-array

vaso123 :

If I understood your question: decode the json string into an array: \n\n$myResultArray = json_decode($jsonString, true);\n\n\nand then you can use the associative array. \n\nFor example: $myResultArray['newsletter'];\n\n$sql = \"UPDATE #__table SET newsletter = '\".$myResultArray['newsletter'].\"' WHERE id='\".$id.\"'\";\n",

2014-11-12T14:40:32

MYSQL Update only one key:value inside a json array