Search inside a JSON array using MySQL

Question

I wanna search for a certain value inside a JSON array but I only get an error message.

For example I wanna search for a certain country inside the JSON array and select only the user_id of the user if the country id contained in the array.

I tried using JSON_CONTAINS:

<?php 
    $query = "SELECT user_id FROM user 
              WHERE JSON_CONTAINS(user_info, 'us', '$.country')";
    $row = mysqli_query($conn, $query) or die("Error: ".mysqli_error($conn));;
    while($results = mysqli_fetch_array($row )){
        echo $results['user_id']."<br>";
    }
?>

My Database Table "user"

|user_id|username|user_info
|      1|     xxx|{"language":["en","fr"],"country":["us"]}
|      2|     xxx|{"language":["de"],"country":["au"]}
|      3|     xxx|{"language":["ja"],"country":["us","jp"]}

My error message

Error: Invalid JSON text in argument 2 to function json_contains: "Invalid value." at position 0.

searching with PHP is no different from searching in mysql. you have to refer to mysql documentation related to json_contains — Your Common Sense, Sep 22 '19 at 14:53
`$.country` < that appears to be a typo and isn't shown where it is defined (as). — Funk Forty Niner, Sep 22 '19 at 14:54
`['user_id ']` < the space in there counts as a character and you should also be getting an undefined index warning about it. — Funk Forty Niner, Sep 22 '19 at 14:56
It isn't recommended to store data in there like that, using comma separated values. There are ways to normalize a database for this. — Funk Forty Niner, Sep 22 '19 at 14:57
[How to get the error message in MySQLi?](https://stackoverflow.com/a/22662582/1839439) — Dharman, Sep 22 '19 at 17:56

Madhur Bhaiya · Answer 1 · 2019-09-22T15:33:46.837

Json_Contains() expects the Second argument to be a valid JSON Document, while you are passing a string.

An error occurs if target or candidate is not a valid JSON document

Use '["us"]' instead of "us". View on DB Fiddle:

SET @jsn := '{"language":["en","fr"],"country":["us"]}';

SELECT JSON_CONTAINS(@jsn, '["us"]', '$.country');

| JSON_CONTAINS(@jsn, '["us"]', '$.country') |
| ------------------------------------------ |
| 1                                          |

But then, if I understand correctly, you are trying to find those JSON doc wherein the array corresponding to country key contains 'us'. JSON_CONTAINS() cannot be used to achieve this directly. You will need to use a combination of JSON_EXTRACT() and JSON_SEARCH(). Note that you are only looking for a match, so second argument can be one in Json_Search() function:

SET @jsn := '{"language":["en","fr"],"country":["us"]}';

SELECT 
  JSON_SEARCH(JSON_EXTRACT(@jsn, '$.country'), 'one', 'us');

| JSON_SEARCH(JSON_EXTRACT(@jsn, '$.country'), 'one', 'us') |
| --------------------------------------------------------- |
| "$[0]"                                                    |

View on DB Fiddle

Now, Json_Search returns NULL if any of the json_doc, search_str, or path arguments are NULL; no path exists within the document; or search_str is not found. So, you will need to use IS NOT NULL in your WHERE clause. Use the following query instead:

SELECT user_id 
FROM user 
WHERE JSON_SEARCH(JSON_EXTRACT(user_info, '$.country'), 'one', 'us') IS NOT NULL

You missed something. See a comment I left under their question, something about an undefined index ;-) — Funk Forty Niner, Sep 22 '19 at 15:05
@FunkFortyNiner there might be more errors; I am not evaluating the PHP code, but suggesting a fix for the MySQL query — Madhur Bhaiya, Sep 22 '19 at 15:06

Search inside a JSON array using MySQL

1 Answers1