Mox Mox - 4 months ago 7
SQL Question

How to use json column in the WHERE clause as a condition

The question is stated in the title and below is an example of the data

insert into table A values('a','b', {'key':'value'});


And I would like to be able to select this row based on the key-value pair using the WHERE clause. How can I do that?

Answer

Use JSON_VALUE:

SELECT t.*
FROM tableA t
WHERE JSON_VALUE(col3, '$.key') LIKE 'some_value'

This assumes that the column which contains the JSON value {'key':'value'} is called col3.