How to Query a JSON Column in MySQL

Starting with version 5.7.8, MySQL supports JSON columns. This gives the advantage of storing and querying unstructured data. Here's how you can query a JSON column in MySQL:

-- Getting the string value from events table
SELECT params->>'$.name'
FROM events;

-- Getting rows where the is Chrome
-- This also shows the difference of using -> vs ->>
-- Using -> will cause strings to be enclosed in quotes
SELECT browser->>'$.name', browser->'$.name'
FROM events
WHERE browser->>'$.name' = 'Chrome';

-- Give me the first index of a JSON array
SELECT properties->>'$.my_array[0]'
FROM events;

-- Going deeper to get the X resolution only
SELECT properties->'$.resolution.x'
FROM events;
database icon
From MySQL query to chart to Slack in seconds
Get to answers faster, together, with PopSQL and MySQL