MySQL view query without Json from a table containing some Json objects

Question

Ive been trying to make a new MySQL view without Json from an existing MySQL table which contain some Json objects. Table 1 is the current table. Table 2 is the new MySQL view derived from Table 1. The content of table 2 will always reflect table 1 content. The difference is just the structure.

Below are simplified tables for our example context where actually there are more than 20 columns.

Table 1 (3 columns)

user_id | name |          params
---------------------------------------------
   1    | john |{"age":"26","gender":"male"}
   2    | amy  |{"age":"28","gender":"female"}

Table 2 (4 columns)

user_id | name | age | gender
-----------------------------
   1    | john | 26  | male
   2    | amy  | 28  | female

The data flow is like this: At anytime, user key in data from 3rd party custom form and got saved into Table 1. The data however need to be pulled from the Table 2 format, also at anytime.

How can we achieve this using only SQL queries? Thank you in advance. This will sure helps many developers out there coping with Json as new standard. Cheers!

Answer 1

You can do this with a rather ugly combination of string functions, assuming your table2 above:

SELECT user_id,name,
substring_index(substring(params,locate('age":"',params)+6),'"',1) as age 
from table_2

You can follow the same pattern for gender.

This is a bit fragile based on how consistently you JSON is formatted.

Forgot to mention the docs can be viewed at: MySQL docs