CODEWITHSUNDEEP
phpmysqlarraysjson
Mike Deluca
Mike Deluca

Reputation: 1210

MySQL Filter JSON_CONTAINS Any value from Array

I have a JSON field in a MySQL database that contains values like [1,3,4,7]. I would like to be able to easily supply another array from a PHP variable and determine if there is any overlap. I know this example does not work, but this is what I am trying to do:

$DaysVar = $_GET['Days']; --Example is [1,5,8]

$sql = mysqli_query($db, "
    SELECT ScheduleID, 
           Days --Example is [1,3,4,7]
    FROM Schedule
    WHERE JSON_CONTAINS(Days, '$DaysVar')
");

How can I get this query to return a result since there is a 1 in each array?

Upvotes: 19

Views: 32045

Answers (3)

Renish Gotecha
Renish Gotecha

Reputation: 2522

Table :

table details

Sample Data:

enter image description here

In my case, I achieved it with below query if you want data in different row then remove GROUP_CONCAT

Query :

 SELECT *, GROUP_CONCAT(wis.wi) FROM profile_card_stacks,
 JSON_TABLE(work_industry, "$[*]" COLUMNS(wi INT PATH '$')) as wis
 where wis.wi in(10,14);

Upvotes: 0

trincot
trincot

Reputation: 350272

On MySQL 5.7.8+ you can perform a JSON_CONTAINS for each separate value:

SELECT * 
FROM   Schedule 
WHERE  (   JSON_CONTAINS(Days, '1')
        OR JSON_CONTAINS(Days, '2')
        OR JSON_CONTAINS(Days, '6')
       )

When the values to be searched for are stored in a PHP variable -- as in your question -- then you could build the above SQL like this:

$DaysVar = $_GET['Days'];
$condition = implode(" OR ", array_map(function($day) {
    return "JSON_CONTAINS(Days, '".intval($day)."')";
}, $DaysVar));
$sql = mysqli_query($db, "
    SELECT ScheduleID, 
           Days
    FROM   Schedule
    WHERE  ($condition)
");

To avoid SQL injection, you should better prepare your statement creating a string like with substr(str_repeat(" OR JSON_CONTAINS(Days, ?)", count($DaysVar)), 3) and then call mysqli_prepare on it, then bind, ...etc.

MySQL 8.*

Since MySQL 8 you can use JSON_OVERLAPS:

SELECT * 
FROM   Schedule
WHERE  JSON_OVERLAPS(Days, '$DaysVar')

Again, you would better prepare the statement (with a ? placeholder), then bind $DaysVar to it, and finally execute it.

Upvotes: 27

Jan Misker
Jan Misker

Reputation: 2149

Probably you found a solution, but for those looking for an answer in the future:

There is now JSON_OVERLAPS(), which in your example you can just swap out for JSON_CONTAINS().

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

Upvotes: 2

Related Questions