如何在MYSQL触发器中解析JSON [英] How to parse JSON in MYSQL trigger

问题描述

我想在MYSQL触发器中解析JSON格式

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"}]}

实际上，我是从我的一个应用程序中获得响应并将其存储在一个表( tbl1 )中的，所以我想做的就是在该表中创建一个触发器( tbl1 )并解析以上响应，然后将相应的条目插入另一个表( tbl2 )

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)

需要在MYSQL触发器中解析以上json格式，以便我将分别获取所有参数.怎么办?

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

推荐答案

您可以安装 common_schema .然后，您应该可以执行以下操作:

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;

这篇关于如何在MYSQL触发器中解析JSON的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！