How to insert nested json data in mysql table?

Question

I am trying to update my mysql table and insert json data to my mysql table's json-datatype column using JSON_INSERT. Here is the structure of my column.

{
"Data": 
     [{
      "Devce": "ios", 
      "Status": 1
      }]
}

This is the query I am using to insert more data to this field.

UPDATE table SET `Value` = JSON_INSERT
(`Value`,'$.Data','{\"Device\":\"ios\",\"Status\":1}') WHERE Meta = 'REQUEST_APP'

This is supposed to update the field to this:

{
    "Data": 
         [{
          "Devce": "ios", 
          "Status": 1
          },
    {
          "Devce": "ios", 
          "Status": 1
          }
]
    }

But instead it the result is:

0 rows affected. (Query took 0.0241 seconds.)

Any help regarding this would be appreciated.

If you help me make it work on phpmyadmin I will make it work myself. The php code may make the question unclear. — Haris Khan
– Haris Khan, Commented Aug 25, 2017 at 11:00
@Carlos it worked for me. Please answer it so I can mark it as correct. — Haris Khan
– Haris Khan, Commented Aug 25, 2017 at 11:08

Carlos Gonzalez · Accepted Answer · 2017-08-25 11:12:24Z

2

JSON_APPEND serves your purpose better JSON_APPEND docs

answered Aug 25, 2017 at 11:12

Carlos Gonzalez

88813 silver badges23 bronze badges

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

Collectives™ on Stack Overflow

How to insert nested json data in mysql table?

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