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

Question

I have a table with a column for storing a JSON value for each row. Currently, these are not standardized. I'd like to end up with each of these JSON values being standardized in number and title of attributes. Is there a query I can use to determine whether or not each JSON value contains a specified attribute?

As an example, here are what some of the JSON values look like:

{"name":"Item 1","cost":"4.99"}
{"name":"Item 2"}
{"name":"Item 3","cost":""}
{"name":"Item 4"}

How do I:

• Determine which rows have a "cost" attribute (items 1 & 3)
• Determine which rows do not have a "cost" attribute (items 2 & 4)
• Determine which rows have a value set for the "cost" attribute (item 1)

Thank you! This is my first time asking (at least recently) so any help is greatly appreciated!

Answer 1

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

Answer 2

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.

Answer 3

Actually you can do it by comparing the JSON_EXTRACT result to itself:

WHERE JSON_EXTRACT(colname, '$."cost"') = JSON_EXTRACT(colname, '$."cost"')

This is going to be somehow "false" / null, if there is no corresponding key inside your JSON column.

This way you are able to find all entries that have the key you search for. If you want all the entries that dont have the key you somehow can't use the NOT operator. You have to do it like this:

SELECT * FROM tablename WHERE id NOT IN (SELECT id FROM tablename WHERE WHERE JSON_EXTRACT(colname, '$."cost"') = JSON_EXTRACT(colname, '$."cost"'))

I know it looks like voodoo, but it seems to work.

Answer 4

If you are using a MySQL without JSON functions (pre 5.7), or want to check for any tag in lists of, or unpredictably nested JSON.

Here is an example of how to do it with LIKE:

SELECT case when colname like '%cost%' then 1 else 0 end as has_cost,
       case when colname like '%cost":""%' then 1 else 0 end as has_cost_with_no_value
FROM tablename