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

Question

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 1

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.

Answer 2

If you are using PostgreSQL then use below query

select * from table_name where column_name->>'key_name' = 'value_name';