针对嵌套 JSON 字段、MYSQL、PHP 的查询

Question

我有这样的结构：

{"placards":

    [

    {"barcode": "string", "destination":"string", "weight":"string"},

    {etc...}

]

}

存储在 MYSQL 数据库中的单个列上。

我正在尝试搜索特定的条形码，并返回该条形码位于该行的 json 结构内的整行。

我尝试了两种方法，两种方法都在堆栈溢出中找到，但我没有在一种方法中得到结果，下面我将发布后者的错误。

attempt1:

"SELECT * FROM table WHERE placards[*->barcode] = ".$job->events[0]->imcb;

attempt2:

    $sampleBC = $job->events[0]->imcb;

        $sql = 'SELECT * FROM(

        SELECT data_column->"[*]" as row

        from table

        where $sampleBC IN JSON_EXTRACT(data_column, ""

    )

    WHERE row->".barcode" = $sampleBC';

这些方法都不会在 $stmt->error 上给出错误，但我实际上无法通过此查询成功获取任何内容。

Answer 1

您只需使用json_contains()：select *from mytablewhere json_contains(data_column, '{ "barcode": "foo" }' , '$.placards')此短语为：搜索路径 'placards' 下数组中包含候选对象的键/值对的任何元素'{ "barcode": "foo" }'，其中 'foo' 是您搜索的条形码值。DB Fiddle 演示：set @data_column =     '{        "placards": [            {"barcode": "foo", "destination":"string", "weight":"string"},            {"barcode": "bar", "destination":"string", "weight":"string"}        ]    }';select json_contains(@data_column, '{ "barcode": "foo" }' , '$.placards') is_a_match;| is_a_match || ---------: ||          1 |select json_contains(@data_column, '{ "barcode": "baz" }' , '$.placards') is_a_match;| is_a_match || ---------: ||          0 |从 MySQL 8.0.17 开始，你甚至可以在嵌套的 json 数组上创建多值索引，这样数据库就不需要为此查询执行全表扫描：alter table mytable     add index myidx( (cast(data_column -> '$.placards' as unsigned array)) );

Answer 2

您必须使用 JSON_TABLE()：SELECT mytable.* FROM mytable,  JSON_TABLE(mytable.data_column, '$.placards[*]' COLUMNS (    barcode VARCHAR(100) PATH '$.barcode',    destination VARCHAR(100) PATH '$.destination',     weight VARCHAR(20) PATH '$.weight'  )) AS jWHERE j.barcode = ?如果将这些字段存储在普通列中而不是 JSON 中，则会简单得多。每个标语牌存储一行，并包含 barcode、description 和 weight 的单独列。SELECT m.* FROM mytable AS m JOIN placards AS p ON m.id = p.mytableid WHERE p.barcode = ?我在 Stack Overflow 问题中看到的 MySQL 中 JSON 的大多数使用都是不必要的，因为数据不需要 JSON 的灵活性。针对 JSON 文档的查询很难编写，也很难优化。 JSON 还需要更多空间来存储相同的数据。