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.