JSON_EXTRACT not working for nested json data

Question

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`

But it's showing error :

[Invalid JSON text in argument 1 to function json_extract: "Invalid value." at position 0.]

My json data in table can not be changed to other and it's correct JSON for sure, Could anyone correct above query ?

" it's correct JSON for sure"...what makes you think so? It doesn't look like correct JSON based on that sample, and jsonlint.com (a site you can use to validate your JSON strings) agrees with me. It's quite a long way from being valid JSON, in fact. — ADyson
– ADyson, Commented Jul 16, 2018 at 12:34

Bill Karwin · Accepted Answer · 2018-07-16 14:00:20Z

3

That's not JSON data. It looks like a serialized PHP object. See http://php.net/serialize

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.

answered Jul 16, 2018 at 14:00

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

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

Collectives™ on Stack Overflow

JSON_EXTRACT not working for nested json data

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