JSON_EXTRACT不适用于嵌套json数据 [英] JSON_EXTRACT not working for nested json data
问题描述
我想从我的表中选择json数据,以便显示我的表数据是这样的:
I want to select the data from my table which is json data so to show my table data is like this :
user_id: 1
metaname: mymetaname
meta_value: a:1:{i:0;a:10:{s:7:"street1";s:36:"shiv plaza";s:4:"city";s:5:"surat";s:5:"state";s:7:"gujarat";s:7:"zipcode";s:6:"395010";s:14:"dollet_country";s:2:"IN";s:10:"tostreet1l";s:5:"surat";s:7:"tocityl";s:5:"surat";s:8:"tostatel";s:5:"surat";s:10:"tozipcodel";s:6:"395000";s:17:"todollet_countryl";s:2:"IN";}}
我正在尝试运行此查询:
And i am trying to run this query :
SELECT user_id,JSON_EXTRACT(meta_value, '$."city"') FROM `usermetatable`
但是显示错误:
[功能json_extract的参数1中的JSON文本无效:无效 值."位于位置0.]
[Invalid JSON text in argument 1 to function json_extract: "Invalid value." at position 0.]
我在表中的json数据无法更改为其他数据,并且可以肯定是正确的JSON,有人可以更正上面的查询吗?
My json data in table can not be changed to other and it's correct JSON for sure, Could anyone correct above query ?
推荐答案
这不是JSON数据.它看起来像一个序列化的PHP对象.参见 http://php.net/serialize
That's not JSON data. It looks like a serialized PHP object. See http://php.net/serialize
没有MySQL函数可以从该序列化对象中提取字段.您应该将整个对象提取到PHP应用程序中,然后调用 unserialize()在它上面,然后访问对象成员.
There's no MySQL function for extracting a field from that serialized object. You should fetch the whole object into a PHP app, and call unserialize() on it, then access the object members.
这篇关于JSON_EXTRACT不适用于嵌套json数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!