时间戳

首页 > 解决方案 > SQL 从 JSON 中的数组中选择

sql - SQL 从 JSON 中的数组中选择

问题描述

我有一个带有以下 json 的 json 字段的表：

[
 {
     productId: '1',
     other : [
         otherId: '2'
     ]
 },
 {
     productId: '3',
     other : [
         otherId: '4'
     ]
 }
]

我正在尝试为每个数组元素选择 productId 和 otherId，如下所示：

select JSON_EXTRACT(items, $.items[].productId) from order;

但这是完全错误的，因为它只需要数组中的第一个元素

我需要写一个循环还是什么？

标签： sqljsonmariadb

解决方案

首先，您显示的数据不是有效的 JSON。它有多个错误使其无效。

这是一个使用有效 JSON 的演示：

mysql> create table orders ( items json );
mysql> insert into orders set items = '[ { "productId": "1", "other": { "otherId": "2" } }, { "productId": "3", "other" : { "otherId": "4" } } ]'

mysql> SELECT JSON_EXTRACT(items, '$[*].productId') AS productIds FROM orders;
+------------+
| productIds |
+------------+
| ["1", "3"] |
+------------+

如果您希望将一行中的每个 productId 单独作为标量值而不是 JSON 数组，则必须在 MySQL 8.0 中使用 JSON_TABLE()：

mysql> SELECT j.* FROM orders CROSS JOIN JSON_TABLE(items, '$[*]' COLUMNS(productId INT PATH '$.productId')) AS j;
+-----------+
| productId |
+-----------+
|         1 |
|         3 |
+-----------+

这是在 MySQL 8.0.23 中测试的。

您还标记了您的问题 MariaDB。我不使用 MariaDB，而且 MariaDB 有自己不兼容的 JSON 支持实现，所以我无法预测它将如何工作。

推荐阅读