mysql - json_table不正确的参数

我有一个类似以下结构的表：

| id |         data         |
|  1 | {"products":[1,2,3]} |
|  2 | {"products":[2,5,7]} |
|  3 | {"products":[2,4,1]} |
|  4 | {"products":[7,8]}   |
|  5 | {"products":[2,8]}   |

我想将此表加入查询，并使用产品JSON中的ID来加入3.表。 3.表称为产品。

我设法编写了查询，但始终收到以下消息：

Error Code: 1210. Incorrect arguments to JSON_TABLE

不幸的是，我无法更改结构，并且必须从JSON字段中获取这些ID。

到目前为止，我有以下查询：

select pd.`id` pd.parameter_condition->>'$.products' as `product_id`
from `shop`.`ordering` o
inner join `shop`.`ordered_product` op on (op.`order_id` = o.`id`)
-- [... a lot more joins]
inner join `shop`.`price_dependency` pd on (pd.`attribute_value_id` = av.`id`)
where o.type_id = 4

这将返回以下结果：

| id |    product_id`   |
|  2 |["25"]            |
|  3 |["10", "12"]      |
|  5 |["10", "15", "22"]|

但是我最终想要的是这样的：

| id | product_id` |
|  2 |      25     |
|  3 |      10     |
|  3 |      12     |
|  5 |      10     |
|  5 |      15     |
|  5 |      22     |

最佳答案

您可以尝试使用JSON_EXTRACT获取JSON数组值。

根据需要继续为JSON数组的索引创建结果集，以达到JSON数组的最大长度。

然后使用T CROSS JOIN索引表，使用JSON_EXTRACT函数仅显示值，该索引存在于数组中。

模式（MySQL v8.0）

CREATE TABLE T(
  id int,
  product_id varchar(50)
);
insert into T values (2,'["25"]');
insert into T values (3,'["10", "12"]');
insert into T values (5,'["10", "15", "22"]');

查询＃1

SELECT
  id,
  JSON_EXTRACT(product_id, concat('$[',idx,']')) product_id
FROM T
 CROSS JOIN (
  SELECT 0 AS idx UNION
  SELECT 1 AS idx UNION
  SELECT 2 AS idx
  ) AS indexes
where  JSON_EXTRACT(product_id, concat('$[',idx,']')) is not null
order by id;

| id  | product_id |
| --- | ---------- |
| 2   | "25"       |
| 3   | "10"       |
| 3   | "12"       |
| 5   | "10"       |
| 5   | "15"       |
| 5   | "22"       |

View on DB Fiddle

注意

T表示您当前的结果集。

关于mysql - json_table不正确的参数，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/51926322/