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

also always a good read https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad — nbk, Jun 14 '23 at 07:17

score 0 · Accepted Answer · answered Jun 14 '23 at 08:47

0

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

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

answered Jun 14 '23 at 08:47

SelVazi

1 Answers1