MySQL: Query by JSON value | Sajad Torkamani

On this page

Assuming a state table like this:

id	state_key	state_value
1	has_accepted_cookies	{ “value”: true, “cookieTypes”: [“Foo”, “Bar” }
2	has_used_chat	{ “value”: true, “date”: “2023-02-01 }
3	has_used_chat	{ “value”: true, “date”: “2023-05-04 }

You can query the rows by specific JSON fields like so:

SELECT *
FROM state
where state_key = 'has_used_chat'
  and state_value -> '$.value' = true;

OR:

SELECT *
FROM state
where state_key = 'has_used_chat'
  and state_value -> '$.date' = '2023-02-01';

Tagged: MySQL