CODEWITHSUNDEEP
phpmysqljson
nico261
nico261

Reputation: 55

Search inside a JSON array using MySQL

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.

Upvotes: 0

Views: 1904

Answers (1)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28864

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

Upvotes: 2

Related Questions