Dealing with data stored as JSON objects in a MySQL

Question

I'm using opencart 2.1 and one of the field on the customer table is an text array. The data field (named custom, type text) look like this: {"2":"2015","1":"2000-11-19"} Basically, the 1st part is a birthday (not allow null), and the second part is today year (allow null). It was saved to the database using serialize($arr)

I need a query to get the data based on the "birthday" part. Basically, it's something like this:

SELECT * FROM CUSTOMER WHERE MONTH(custom.1) = MONTH(NOW()) AND DAY(custom.2) = DAY(NOW())

I don't know how to refer to part 1 or part 2 of the custom field. If I can get the query to work, I can bring it back to php and use $arr = unserialize($arr) to fetch it into an array and use it.

Also, later on, I would need to update the part 2 of the custom field (aka, the "this year" part. It can be null, or last year, and need to be update to this year). How could I do that and still keep the part 1 (aka, birthday) the same?

Does anybody know how to do this?

Thanks.

Did you try using json_decode? You'll have to pull that result as the String it is. — StackSlave
– StackSlave, Commented Nov 21, 2015 at 1:40
Unless you can use Mysql 5.7, and change the type of the column, and that's okay with the cart software, then you'll have to use string comparison against the whole string, not just a part of it. Or, use LIKE to look for unique parts -- not very reliable. It is not, generally, good practice to store data in this fashion because, as you have experienced, it is difficult to include in queries. I know you didn't set it up that way, but you're learning the lesson from someone else's mistake. — Chris Baker
– Chris Baker, Commented Nov 21, 2015 at 1:53
I'm not very familiar with json_decode, and can't use Mysql 5.7. can you give me more detail on how to use json_decode in this example. — meowmeowcastle
– meowmeowcastle, Commented Nov 21, 2015 at 2:43

ins0 · Accepted Answer · 2015-11-21 01:56:24Z

1

Solution 1:

You could update to MySQL 5.7 where JSON column field types are now supported.

So your query would look like this:

SELECT * FROM CUSTOMER WHERE MONTH(jsn_extract(custom,"$.1")) = MONTH(NOW()) AND DAY(jsn_extract(custom,"$.2")) = DAY(NOW())

Solution 2:

Query the data normaly and filter your needed result with PHP functions - like the JSON Decode + DateTime classes.

http://php.net/manual/en/class.datetime.php

http://php.net/manual/en/function.json-decode.php

Solution 3:

You could use the MySQL functions like SUBSTRING_INDEX to extract your values directly from the JSON. But this leads to strange errors when the data structure is changing and is not very fail safe.

edited Nov 21, 2015 at 1:56

answered Nov 21, 2015 at 1:44

ins0

3,9281 gold badge22 silver badges28 bronze badges

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

2 Comments

meowmeowcastle Over a year ago

I'm not very familiar with json_decode, and can't use Mysql 5.7. can you give me more detail on solution how to use json_decode in this case? Thanks!

meowmeowcastle Over a year ago

So I was able to do it with the substring_index, and it's quite messy. But if there is no other way for the moment, I might have to go with it.

SELECT * FROM 'rwy_customer' WHERE SUBSTRING_INDEX(SUBSTRING_INDEX(custom_field,'-',-2),'-',1) = MONTH(NOW()) AND SUBSTRING_INDEX(SUBSTRING_INDEX(custom_field,'-',-1),'"',1) = DAY(NOW())

Collectives™ on Stack Overflow

Dealing with data stored as JSON objects in a MySQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related