SQL to return rows when certain value exists in Json in a VarChar Column

Question

I am trying to return only the rows when tags column contain "environment" value

Table: Data

Column: tags VARCHAR ( 40 )

{"environment":"prod", "version":"1:"}, NULL, {"environment":"test"}

My existing query extracts "prod" and "test" properly but still returns rows when "tags" column is empty as NULL

SELECT *, CASE WHEN JSON_VALID(tags) AND tags IS NOT NULL THEN JSON_EXTRACT(tags, '$.environment') ELSE null END FROM Data

So I want my query to return only 2 rows, but it returns 3 rows one being NULL

Answer 1

It appears that you are using mysql/mariadb, if so, try this:

SELECT *
FROM mytable
WHERE JSON_EXTRACT(tags, '$.environment') IS NOT NULL

Demo here