php - 使用 MySQL 在 JSON 数组中搜索

Question

我想在 JSON 数组中搜索某个值，但只收到错误消息。

例如，我想在 JSON 数组中搜索某个国家/地区，如果数组中包含国家/地区 ID，则仅选择用户的 user_id 。

我尝试使用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>";
    }
?>

我的数据库表“用户”

|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"]}

我的错误消息

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

Answer 1

Json_Contains()当您传递字符串时，期望第二个参数是有效的 JSON 文档。

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

使用'[“us”]'而不是“us”。 View on DB Fiddle :

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

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

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

但是，如果我理解正确的话，您正在尝试查找那些 JSON 文档，其中与 country 键对应的数组包含 'us'。 JSON_CONTAINS() 不能直接用于实现此目的。您将需要使用 JSON_EXTRACT() 的组合和 JSON_SEARCH() 。请注意，您只是在查找匹配项，因此第二个参数可以是 Json_Search() 函数中的 one:

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

现在，如果 json_doc、search_str 或路径参数中的任何一个为 NULL，Json_Search 将返回 NULL；文档中不存在路径；或未找到 search_str。因此，您需要在 WHERE 子句中使用 IS NOT NULL。请改用以下查询:

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