Matthew J Gravelyn Matthew J Gravelyn - 2 months ago 8
JSON Question

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

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

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; I don't think there's a way to distinguish this from a nonexistent value.