mysql - 如何从 MySQL 中提取 JSON 数组

Question

我计划将一个 JSON 数组插入到我的数据库表中以用于动态目的。我在使用键/值对提取 JSON 对象时没有任何问题，但对于 JSON 数组则相反。请参阅以下示例:

[{"LCM": {"id": 333, "barcode": "ABC"}, "DCover": {"id": 444, "barcode": "CDE"}, "date_associated": "2017-11-27 23:59:59"}, {"LCM": {"id": 555, "barcode": "EFG"}, "DCover": {"id": 666, "barcode": "GHI"}, "date_associated": "2017-11-27 23:59:59"}, {"LCM": {"id": 777, "barcode": "IJK"}, "DCover": {"id": 888, "barcode": "KLM"}, "date_associated": "2017-11-27 23:59:59"}]

理想情况下，根据上面的每一个，如果提取 - 它看起来像这样:

{"LCM": {"id": 777, "barcode": "IJK"}, "DCover": {"id": 888, "barcode": "KLM"}, "date_associated": "2017-11-27 23:59:59"}

现在的问题是，我怎样才能这样提取它们:

1. {"LCM": {"id": 333, "barcode": "ABC"}, "DCover": {"id": 444, "barcode": "CDE"}, "date_associated": "2017-11-27 23:59:59"}
2. {"LCM": {"id": 555, "barcode": "EFG"}, "DCover": {"id": 666, "barcode": "GHI"}, "date_associated": "2017-11-27 23:59:59"}
3. {"LCM": {"id": 777, "barcode": "IJK"}, "DCover": {"id": 888, "barcode": "KLM"}, "date_associated": "2017-11-27 23:59:59"}

通过它们中的哪一个，我将能够根据需要进行解析，即:“$.LCM”或“$.DCover.barcode”

JSON_EXTRACT(...) 似乎只能通过定义特定 JSON 对象的键来使用。但这不适用于仅值 JSON 数组(据我所知)

希望我能在这里得到小费。谢谢。

Answer 1

如果您使用的是 MySQL >= 8.0.4，则可以使用 JSON_TABLE。

示例查询

with tbl(val) as (
  select CAST('[{"LCM": {"id": 333, "barcode": "ABC"}, "DCover": {"id": 444, "barcode": "CDE"}, "date_associated": "2017-11-27 23:59:59"}, {"LCM": {"id": 555, "barcode": "EFG"}, "DCover": {"id": 666, "barcode": "GHI"}, "date_associated": "2017-11-27 23:59:59"}, {"LCM": {"id": 777, "barcode": "IJK"}, "DCover": {"id": 888, "barcode": "KLM"}, "date_associated": "2017-11-27 23:59:59"}]'
  as json
 )
)
select rowid, content from tbl, JSON_TABLE(val, '$[*]' columns (rowid for ordinality, content json PATH '$')) as jt;

结果

+-------+---------------------------------------------------------------------------------------------------------------------------+
| rowid | content                                                                                                                   |
+-------+---------------------------------------------------------------------------------------------------------------------------+
|     1 | {"LCM": {"id": 333, "barcode": "ABC"}, "DCover": {"id": 444, "barcode": "CDE"}, "date_associated": "2017-11-27 23:59:59"} |
|     2 | {"LCM": {"id": 555, "barcode": "EFG"}, "DCover": {"id": 666, "barcode": "GHI"}, "date_associated": "2017-11-27 23:59:59"} |
|     3 | {"LCM": {"id": 777, "barcode": "IJK"}, "DCover": {"id": 888, "barcode": "KLM"}, "date_associated": "2017-11-27 23:59:59"} |
+-------+---------------------------------------------------------------------------------------------------------------------------+