MySQL. search data in serialized column

Question

I have table workers and table worksheets.

This two tables are linked by column "worker_id"

In worksheets we have some data about workers as birthday, sex and other. This data is saved in one column as serialized array

something like:

{"photo":"image.png","sex":"male","birthday":"14.09.2018","phone":"8(777)777-7778"}

My task is to get workers over 20 years old and under 30 years old for example

How can I do it? Also in birthday key may be null values

I will be glad of any help

Maybe take a look at the built-in JSON functions – Strawberry Nov 18 '19 at 12:26 — Strawberry, Nov 18 '19 at 12:26

score 2 · Answer 1 · answered Nov 18 '19 at 12:26

2

You can use json_extract to get data from json in MySQL

set @json = '{"age": 25,"photo":"image.png","sex":"male","birthday":"14.09.2018","phone":"8(777)777-7778"}';
select json_extract(@json, '$.sex'); //returns "male"

I suppose you are looking to a query like this:

select *
  from workers w
 inner join worksheets ws on w.id = ws.worker_id
 where json_extract(ws.json_field, '$.age') > 20

Take a look at MySQL JSON functions MYSQL Json

answered Nov 18 '19 at 12:26

Marcelo The Mage Coder

1,944
2
11
29

seems that my mysql server is old for "json_extract", because I got error "json_extract is not exist". May be there any other soultions? – Baurzhan Nov 18 '19 at 12:37
@Baurzhan which version of mysql server are you using? – Marcelo The Mage Coder Nov 18 '19 at 12:44
version is 5.0.12 – Baurzhan Nov 18 '19 at 12:47
1

MySQL native JSON functions was implemented on version 5.7.8 as the docs says, I think in your case you'll could try to implement your own function or update it to a new version – Marcelo The Mage Coder Nov 18 '19 at 12:55
@Baurzhan can't you update your MySQL version? – Fantasmic Nov 18 '19 at 12:56
I can't update MySQL version because this is not my database – Baurzhan Nov 18 '19 at 13:01

MySQL. search data in serialized column

1 Answers1