返回json名称值的MySQL查询

Question

我正在使用一个表，其中的信息以JSON格式存储在一个表中。JSON值字段如下所示：

select * from k2_extra_fields where id =2，published = 1；

id | value        
2,[{"name":"Apples","value":1,"target":null,"alias":"","required":0,"showNull":1},{"name":"Pears","value":2,"target":null,"alias":"","required":0,"showNull":1},{"name":"Mangos","value":3,"target":null,"alias":"","required":0,"showNull":1},{"name":"Guava","value":4,"target":null,"alias":"Fruit","required":0,"showNull":1},{"name":"Pineapple","value":5,"target":null,"alias":"Fruit","required":0,"showNull":1}]

或简单的逐行视图中的值(减去ID)：

[
  {"name":"Apples","value":1,"target":null,"alias":"","required":0,"showNull":1},
  {"name":"Pears","value":2,"target":null,"alias":"","required":0,"showNull":1},
  {"name":"Mangos","value":3,"target":null,"alias":"","required":0,"showNull":1},
  {"name":"Guava","value":4,"target":null,"alias":"Fruit","required":0,"showNull":1},
  {"name":"Pineapple","value":5,"target":null,"alias":"Fruit","required":0,"showNull":1}
]

这里的查询返回3.3= Mangos的值。如何获取“3”值并将其与存储的名称/值进行匹配，以便最终得到输出Mangos？

Answer 1

这应该是可能的内置mysql的功能，但非常困难和‘不聪明’的想法做到。如果你真的需要在mysql中计算这个问题，你将需要在你的mysql中添加新的功能。查找UDF：http://dev.mysql.com/doc/refman/5.1/en/udf-compiling.html