SQL query to determine if a JSON value contains a specified attribute

I assume you're using MySQL 5.7, which adds the JSON data type. Use JSON_EXTRACT(colname, '$.cost') to access the cost property. It will be NULL is there's no such property.

1. WHERE JSON_EXTRACT(colname, '$.cost') IS NOT NULL
2. WHERE JSON_EXTRACT(colname, '$.cost') IS NULL
3. WHERE JSON_EXTRACT(colname, '$.cost') != ''

It will also be NULL if the value in the JSON is null; if you need to distinguish this case, see Can't detect null value from JSON_EXTRACT

If you need to only check, whether the json key exists, you can use JSON_CONTAINS_PATH. I expect it to be faster than JSON_EXTRACT.

mySQL 8.0 docs

Example:

I have a permission column in the DB. The structure looks like this:

{
    "users": {
        "user1" : "rw",
        "user2" : "r"
    },
    "groups": {
        "root": "rw",
        "anotherGroup" : "r"
    }
}

If I want to get all the items, that have the root group (regarding of the actual permissions), I use this:

SELECT * FROM `filesystem_folders` where JSON_CONTAINS_PATH(permissions, 'one', '$.groups.root');

I made just a very simple time comparison between the select above which took 12.90s (10000 reads). Vs the following select using JSON_EXTRACT

 SELECT * FROM `filesystem_folders` where JSON_EXTRACT(permissions, '$.groups.root') IS NOT NULL;

which took 15.13s. So, quite a small difference.