How to Query a JSON Column

MySQL supports json columns in a regular table, as any other data type. Then you can query a json column as any other kind of column. However, as a json value can store an unstructured data, there are several functions that allow to convert a json column into relational data, or extract an element from a json value, or even create a json value from regular relational data.

Let’s create a table with a json column, populated with relational data using the to_json function.

create table emp_json_tbl ( emp_json_col json );

select * from emp_json_tbl ;

emp_json_col 
-------------
"{"emp_id":1,"last_name":"bisso","dep_id":100,"salary":1000,"department":null}"
"{"emp_id":2,"last_name":"lopez","dep_id":100,"salary":900,"department":null}"

select json_extract(emp_json_col,'$.last_name') last_name_json from emp_json_tbl;
last_name_json
bisso
lopez

IN THIS PAGE