Mox Mox - 1 year ago 47
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 Source


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.