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
– Your Common Sense, Commented Sep 22, 2019 at 14:53
$.country < that appears to be a typo and isn't shown where it is defined (as). — Funk Forty Niner
– Funk Forty Niner, Commented Sep 22, 2019 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
– Funk Forty Niner, Commented Sep 22, 2019 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
– Funk Forty Niner, Commented Sep 22, 2019 at 14:57

Madhur Bhaiya · Accepted Answer · 2019-09-22 15:33:46Z

2

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

edited Sep 22, 2019 at 15:33

answered Sep 22, 2019 at 15:03

Madhur Bhaiya

28.9k10 gold badges53 silver badges61 bronze badges

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

2 Comments

Funk Forty Niner Over a year ago

You missed something. See a comment I left under their question, something about an undefined index ;-)

Madhur Bhaiya Over a year ago

@FunkFortyNiner there might be more errors; I am not evaluating the PHP code, but suggesting a fix for the MySQL query

Collectives™ on Stack Overflow

Search inside a JSON array using MySQL

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related