MySQL Filter JSON_CONTAINS Any value from Array

Question

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?

So you want only duplicated values in result? or counter of how many values are duplicated? — Shaharyar
– Shaharyar, Commented Apr 6, 2017 at 6:22
@JorgeCampos JSON_CONTAINS only available in 5.7.8 or later — Shaharyar
– Shaharyar, Commented Apr 6, 2017 at 6:25

trincot · Accepted Answer · 2022-04-25 14:15:22Z

27

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.

edited Apr 25, 2022 at 14:15

answered Apr 6, 2017 at 6:30

trincot

357k38 gold badges282 silver badges338 bronze badges

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

4 Comments

Mike Deluca Over a year ago

This won't help since the values will be dynamic.

iMarh Over a year ago

Yeah. I have the same problem with dynamic count of JSON array elements. And I need mysql-only solution. How you fixed this?

Tofandel Over a year ago

Those queries are vulnerable to SQL injection! You need to sanitize day to an int or use prepared statements

trane77 Feb 12 at 18:22

🫶 JSON_OVERLAPS saved my afternoon!

Jan Misker · Accepted Answer · 2021-07-22 12:40:45Z

2

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

answered Jul 22, 2021 at 12:40

Jan Misker

2,14917 silver badges26 bronze badges

Comments

Renish Gotecha · Accepted Answer · 2023-01-27 15:50:29Z

0

Table :

Sample Data:

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

answered Jan 27, 2023 at 15:50

Renish Gotecha

2,56026 silver badges24 bronze badges

Collectives™ on Stack Overflow

MySQL Filter JSON_CONTAINS Any value from Array

3 Answers 3

MySQL 8.*

4 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

MySQL 8.*

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related