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.

Tony Chiboucas · Accepted Answer · 2016-06-09 01:50:41Z

9

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:

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

... since there will only ever be one match for that query, it will always eval to 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

edited Jun 9, 2016 at 1:50

answered Jun 9, 2016 at 0:21

Tony Chiboucas

5,7231 gold badge32 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

MarkL Over a year ago

It does not return any results. Using WHERE JSON_EXTRACT(data_column, '$[1].code') = 4 works but still hardcodes the object index.

Tony Chiboucas Over a year ago

Ah... I think I see what you're asking for... one sec.

MarkL Over a year ago

Syntax error near 'JSON_EXTRACT(data_column, '$[*].code') ) WHERE row->code = 4' at line 8. How would you modify the JSON object to make it easier to query?

Tony Chiboucas Over a year ago

ah... missing quotes.

MarkL Over a year ago

Thanks. Still an error with quotes. Your other solution makes sense but for a specific use case: finding code. What if I want to search for stores where stores.code = 400 and then select the whole code object. My original question was about finding/selecting objects without knowing their index in a deeply nested JSON. Perhaps the JSON data type is not intended for that purpose?

|

DanVS · Accepted Answer · 2023-03-17 20:27:34Z

0

This will only return results that have code = 4 at the first level. So it will not find stores->code = 4

SELECT * 
FROM json_data_table 
WHERE JSON_CONTAINS(JSON_EXTRACT(data_column, '$[*].code'), '4')

Hope this helps someone.

answered Mar 17, 2023 at 20:27

DanVS

1

Comments

Edu · Accepted Answer · 2023-07-06 14:51:09Z

-1

This is what you are looking for.

SELECT data_column->>'$[*]' FROM json_data_table where data_column->>'$[*].cod' = 4.

The selected data will have [] around it when selecting from an array thus data_column->>'$[*].code' = 4 is not possible.

edited Jul 6, 2023 at 14:51

answered May 12, 2020 at 17:32

Edu

315 bronze badges

1 Comment

Artistan Over a year ago

data_column->'$[*].code' like '%4%' would match numbers with a 4 in them

Collectives™ on Stack Overflow

How to search nested JSON in MySQL

3 Answers 3

Alternate data structure if possible

8 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Alternate data structure if possible

8 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related