<\/span><\/h2>\n\n\n\nLet us say you have the following table users(id, details)<\/em> where id<\/em> is an integer and primary key while details<\/em> is a JSON data type column. The details column stores all details of users such as their OS name, web browser, resolution, etc. as a JSON string.<\/p>\n\n\n\ncreate table users(\n id int auto_increment primary key,\n details json<\/strong>\n );<\/pre>\n\n\n\nWe will insert the following JSON data in our table.<\/p>\n\n\n\n
insert into users(details)\n values(\n '{ \"page\": \"\/\" , \n \"name\": \"Safari\", \n \"os\": \"Mac\", \n \"spend\": [100, 50]\n \"resolution\": { \"x\": 1920, \"y\": 1080 } }'\n),\n(\n '{ \"page\": \"\/products\", \n \"name\": \"Chrome\", \n \"os\": \"Windows\", \n \"spend\": [150, 250]\n \"resolution\": { \"x\": 1680, \"y\": 1050 } }'\n),\n(\n '{ \"page\": \"\/shoes\", \n \"name\": \"Firefox\", \n \"os\": \"Windows\", \n \"spend\": [200,300]\n \"resolution\": { \"x\": 1280, \"y\": 800 } }'\n);<\/pre>\n\n\n\n<\/span>How to Retrieve data from JSON column in MySQL<\/span><\/h2>\n\n\n\n MySQL offers path operators as well as json_extract() function for this purpose. Lastly, you can search it just as you would search a string for a substring. We will look at each of these solutions one by one.<\/p>\n\n\n\n
<\/span>1. Using Path Operators<\/span><\/h2>\n\n\n\nSince MySQL 5.7.8, it provides two operators ( -> and ->> ) to extract data from JSON columns. ->> will get the string value while -> will fetch value without quotes.<\/p>\n\n\n\n
Here is the SQL query to extract browser name from details<\/em> column<\/p>\n\n\n\nmysql> select id, \n details->>'$.name' as browser_str,\n details->'$.name' as browser_name\n from users;\n+----+--------------+--------------+\n| id | browser_str | browser_name |\n+----+--------------+--------------+\n| 1 | \"Safari\" | Safari |\n| 2 | \"Chrome\" | Chrome |\n| 3 | \"Firefox\" | Firefox |\n+----+--------------+--------------+<\/pre>\n\n\n\nAs you can see ->> returns output as quoted strings, while -> returns values as they are.<\/p>\n\n\n\n
You can also use these operators in WHERE clause as shown below.<\/p>\n\n\n\n
mysql> select id,
details->>'$.name' as browser_str,
details->'$.name' as browser_name
from users
where details->>'$.name'=\"Chrome\";
+----+--------------+--------------+
| id | browser_str | browser_name |
+----+--------------+--------------+
| 2 | \"Chrome\" | Chrome |
+----+--------------+--------------+<\/pre>\n\n\n\nIf you want to retrieve resolution.x<\/em> value, that is, from an object, then you can do so using the dot (.) operator, as shown below.<\/p>\n\n\n\nmysql> select details->'$.resolution.x'<\/code> as width\n from users;\n+----+-----------+\n| id | width |\n+----+-----------+\n| 1 | 1920 |\n| 2 | 1680 |\n| 3 | 1280 |\n+----+-----------+<\/pre>\n\n\n\nSimilarly, if you want to extract an array element from a JSON, then you can access them using square brackets ‘[ ]’ and index of array element. Here is the SQL query to extract first element of array spend<\/em>.<\/p>\n\n\n\nmysql> select details->>'$.spend[0]'<\/code> as spend\n from users;\n+----+-----------+\n| id | spend |\n+----+-----------+\n| 1 | 100 |\n| 2 | 150 |\n| 3 | 200 |\n+----+-----------+<\/pre>\n\n\n\n