How do i add a name: value pair to an existing mysql stored json object

Question

how do i add the new attribute to a json object i have stored in a mysql table column? now i have json_encode data stored like:

"1":{"lastname":"blah","firstname":"R.A.","function":"Manager","email":"test@hotmail.com","barcode":"33432799181"}

and i like to add a new data pair, visted:0 which i want to update as soon as a person has been visited.

"1":{"lastname":"blah","firstname":"R.A.","function":"Manager","email":"test@hotmail.com","barcode":"33432799181", "visited":"0"}

How do i push an element to the existing json data?

$jsondataTmp = json_decode($core,true);
$custom = array('visited'=>'0');
$jsondataTmp[] = $custom;

but it adds an array and not within each

  [1] => Array
        (
            [lastname] => blah
            [firstname] => R.A.
            [function] => Manager
            [email] => test@hotmail.com
            [barcode] => 33432799181
        )

    [2] => Array
        (
           [lastname] => blah
            [firstname] => R.A.
            [function] => Manager
            [email] => test@hotmail.com
            [barcode] => 33432799181
        )

    [3] => Array
        (
            [visited] => 0
        )

and not

[2] => Array
            (
               [lastname] => blah
                [firstname] => R.A.
                [function] => Manager
                [email] => test@hotmail.com
                [barcode] => 33432799181
[visited] => 0
            )

I don't think MySQL offers built-in handling for JSON. You'd have to use your next layer up to retrieve, deserialize, update, reserialize, and store the value. (If you need to update it at the DB level, store it in a more normal DB form, or use a document database like MongoDB.) — T.J. Crowder, Feb 02 '14 at 14:54
Tx, i am going for the retrieve decode, update encode and store, but i need some more tips see my last edit — alex, Feb 02 '14 at 15:07

score 2 · Accepted Answer · answered Feb 02 '14 at 15:05

2

MySQL doesnot have native support for JSON - MySQL still is pure RDBMS system.

One easy hack for the time being can be using a REPLACE function.

UPDATE table_name
SET column_name = REPLACE ( column_name, "}", ',"visited":"0"}';

answered Feb 02 '14 at 15:05

georgecj11

1,600
15
22

i see what you are doing, but i get a error message. Should i escape the single quote? UPDATE table_name SET column_name = REPLACE ( column_name, '"}', ',"visited":"0"}'; Still have to readup on RDBMS, tx for the tip – alex Feb 02 '14 at 15:41
btw do you know how i could update that specific json element to 1? – alex Feb 02 '14 at 16:34
MySQL is not a NoSQL like Mongo/Couch etc. So if you need to do that you need to do some hardwork like Read existing data from DB and json_decode( as you are using php). Then replace the required key to proper value and then json_encode and save it back. If this operation is frequent, you can have a look at MongoDB which is better for these kind of schema. – georgecj11 Feb 02 '14 at 16:42
tx again for the info, at the moment i am stuck with mysql :( (will readup on RDBMS and MongoDB. I just need to update the visited key and set it to 1 where barcode equals the barcode i read (i have cheated by just adding +1 for each json barcode, so if the barcode of the parent is 0123, the 3 json barcode would be 01231,01232,01233). – alex Feb 02 '14 at 17:07

How do i add a name: value pair to an existing mysql stored json object

1 Answers1