Query JSON column with array of objects MySQL

Question

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.

Thanks @LukaszSzozda, but Schwern's answer includes my proposal. — wchiquito
– wchiquito, Commented Sep 2, 2018 at 7:09
@wchiquito At point where I wrote that answer Schwern's answer was deleted. You could check it using stackoverflow.com/posts/52130389/revisions. Anyway good job and keep going :) — Lukasz Szozda
– Lukasz Szozda, Commented Sep 2, 2018 at 10:25

Schwern · Accepted Answer · 2018-09-01 17:34:35Z

3

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");

edited Sep 1, 2018 at 17:34

answered Sep 1, 2018 at 17:01

Schwern

167k28 gold badges225 silver badges370 bronze badges

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

2 Comments

Lukasz Szozda Over a year ago

How is [1,2,2] equal to 1? where usages->'$[*].day' = 1. This query returns 0 rows. Counterexample demo

Schwern Over a year ago

@LukaszSzozda Whoops. Had to install MySQL to remember how their JSON works.

Collectives™ on Stack Overflow

Query JSON column with array of objects MySQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related