针对嵌套 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 mytable

where 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 j

WHERE 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 还需要更多空间来存储相同的数据。