CODEWITHSUNDEEP
mysqljson
Marco
Marco

Reputation: 120

Query JSON column with array of objects MySQL

I have the following json array in my column:

[  
   {  
      "day": 1,
      "requests": 23
   },
   {  
      "day": 2,
      "requests": 5
   },
   {  
      "day": 2,
      "requests": 9
   }
]

and I want the row that has day 1. I already tried to do it with

SELECT * FROM api WHERE usages->'$[*].day' = JSON_ARRAY(1)

but it returns no results.

Upvotes: 3

Views: 169

Answers (1)

Schwern
Schwern

Reputation: 164809

select usages->'$[*].day' from api shows that it's the JSON array [1,2,2].

where usages->'$[*].day' = JSON_ARRAY(1) is trying to match [1,2,2] with [1] which isn't true.

Instead, use JSON_CONTAINS to look for values within the array.

where json_contains(usages->'$[*].day', "1");

Upvotes: 3

Related Questions