How to search nested JSON in MySQL

Question

I am using MySQL 5.7+ with the native JSON data type. Sample data:

[
  {
    "code": 2,
    "stores": [
      {
        "code": 100,
        "quantity": 2
      },
      {
        "code": 200,
        "quantity": 3
      }
    ]
  },
  {
    "code": 4,
    "stores": [
      {
        "code": 300,
        "quantity": 4
      },
      {
        "code": 400,
        "quantity": 5
      }
    ]
  }
]

Question: how do I extract an array where code = 4?

The following (working) query has the position of the data I want to extract and the search criterion hardcoded:

SELECT JSON_EXTRACT(data_column, '$[0]') 
FROM   json_data_table
WHERE  data_column->'$[1].code' = 4

I tried using a wildcard (data_column->'$[*].code' = 4) but I get no results in return.

Answer 1

SELECT row FROM 
(
    SELECT data_column->"[*]" as row
    FROM   json_data_table
    WHERE  4 IN JSON_EXTRACT(data_column, '$[*].code')
) 
WHERE row->".code" = 4

... though this would be much easier to work with if this wasn't an unindexed array of objects at the top level. You may want to consider some adjustments to the schema.

Note:

If you have multiple rows in your data, specifying "$[i]" will pick that row, not the aggregate of it. With your dataset, "$[1].code" will always evaluate to the value of code in that single row.

Essentially, you were saying:

1. $ json collection
2. [1] second object in the collection.
3. .code attribute labeled "code".

... since there will only ever be one match for that query, it will always eval to 4...

4. WHERE 4 = 4

Alternate data structure if possible

Since the entire purpose of "code" is as a key, make it the key.

[
  "code2":{
    "stores": [
      {
        "code": 100,
        "quantity": 2
      },
      {
        "code": 200,
        "quantity": 3
      }
    ]
  },
  "code4": {
    "stores": [
      {
        "code": 300,
        "quantity": 4
      },
      {
        "code": 400,
        "quantity": 5
      }
    ]
  }
]

Then, all it would require would be:

SELECT datacolumn->"[code4]" as code4
FROM json_data_table