查询嵌套的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')

这个短语是:在path '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 = ?

在MySQL中，我在堆栈溢出问题中看到的大多数JSON用法都是不必要的，因为数据不需要JSON的灵活性。针对JSON文档的查询很难编写，也很难优化。JSON还需要更多的空间来存储相同的数据。