Querying JSON in MySQL

Question

I have a table say t_records which has a JSON type column say col_json which can have value like below

{
  "1": {
    "value": null,
    "comment": null,
    "timestamp": null,
    "guidelineId": null,
    "pushedToSnowflakePipeline": "yes"
  },
  "2": {
    "value": null,
    "comment": null,
    "timestamp": null,
    "guidelineId": null,
    "pushedToSnowflakePipeline": "yes"
  },
  "3": {
    "value": null,
    "comment": null,
    "timestamp": null,
    "guidelineId": null,
    "pushedToSnowflakePipeline": "no"
  }
}

I want to query the rows which have isPushedToSnowflakePipeline=no.

I tried with below query but it is not returning any records.

SELECT * 
FROM t_records 
WHERE JSON_EXTRACT(col_json, '$.*.pushedToSnowflakePipeline')='no';

Where am I going wrong with the query?

Answer 1

Analysis

If this is the typical JSON that is stored, the value that will be returned from this query is an array since we iterate over multiple keys in this case ["1","2","3"].

Your current where statement:

JSON_EXTRACT(col_json, '$.*.pushedToSnowflakePipeline');

returns, this: ["yes", "yes", "no"], which it is not filtering properly when you check if this value ="no"

Solution

If you are wanting to check if any of these values = "no", then you need to first get the array of pushedToSnowflakePipeline and then filter that array to see if the values contained are "no".

Update your WHERE statement to this:

JSON_CONTAINS(JSON_EXTRACT(assay_data, '$.*.pushedToSnowflakePipeline'), '"no"', "$") = 1