How to parse JSON in MYSQL trigger

Question

I want to parse JSON format in MYSQL Trigger

 {"multicast_id":67784038XXXXXX,"success":1,"failure":0,"canonical_ids":0,"results":
[{"message_id":"0:13XXXX280085815%XXXXXXXXX"}]}

Actually i am getting this as a response from one of my application and storing in one table (tbl1) , so what i am trying to do is create one trigger at that table(tbl1) and parse above response and insert respective entries in another table(tbl2)

Need to parse above json format in MYSQL Trigger so that i will get all the parameters separately. How can it be done?

Answer 1

You can install common_schema. Then you should be able to do something like this:

select common_schema.extract_json_value(d.table1,'/multicast_id') as multicase_id,
common_schema.extract_json_value(d.table1,'/success') as success,
common_schema.extract_json_value(d.table1,'/failure') as failure,
common_schema.extract_json_value(d.table1,'/canonical_ids') as canonical_ids,
common_schema.extract_json_value(d.table1,'/results') as results,
common_schema.extract_json_value(d.table1,'/message_id') as message_id
from database d;

Answer 2

Add the code in your application's database model. PHP has a nice built-in function for parsing JSON, whereas this is no job for MySql.

Anyways, storing raw JSON in a table is Bad because it defeats the very purpose of a relational database. Unless you consider the JSON string as a raw string, and do not care of its meaning at database level. Which is not the case apparently.

So do not even bother with storing the JSON string. Parse it from PHP and store the data directly into your tbl2. Rebuilding the JSON string from the table with PHP is straightforward and virtually instant (compared to the time it will take to retreive the data from the database).