CODEWITHSUNDEEP
mysqljson
Miket46
Miket46

Reputation: 1

Selecting all objects in a json array that meet criteria in mysql

I need to capture rows when data inside a json field in mysql meets certain criteria. What select would I run to get the rows returned where the json column had a Status value of 1000 in any object in the array?

I tried using the something like this:

SELECT * FROM table WHERE Task_JSON->"$[0]" = 1000;

I'm using Server version: 5.7.11 of mysql

[
{
 "Sequence":"1" ,
 "Status":"1000" 
},
{
 "Sequence":"2" ,
 "Status":"1000" 
},
{
 "Sequence":"3" ,
 "Status":"3000"
}
]

Upvotes: 0

Views: 841

Answers (3)

Abhishek
Abhishek

Reputation: 1561

JSON_EXTRACT can help you.

https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html

select * from table  where JSON_EXTRACT(Task_JSON, '$.Status') = 1000 ;

Upvotes: 0

Yuliya Sokol
Yuliya Sokol

Reputation: 1

SELECT * FROM table WHERE Task_JSON ->>'Status' = 1000;

Upvotes: 0

Sebastian Brosch
Sebastian Brosch

Reputation: 43604

You can use the following solution using JSON_CONTAINS:

SELECT *
FROM table_name
WHERE JSON_CONTAINS(Task_JSON, '{"Status":"1000"}') > 0;

Another solution using JSON_SEARCH:

SELECT * 
FROM table_name
WHERE JSON_SEARCH(Task_JSON, 'one', '1000', NULL, '$[*]."Status"') IS NOT NULL;

demo: https://www.db-fiddle.com/f/3JTQreVi63FS7HbmWQ9TEM/4

Upvotes: 1

Related Questions