我知道您正试图从JSON数组的内容生成一个表结构。
您需要分两步进行:
首先,将数组中的每个元素转换成一个记录;为此,可以生成一个内嵌的数字表并使用
JSON_EXTRACT()
来提取相关的JSON对象。
然后,从每个对象中提取每个属性的值,生成新的列;
->
操作员可用于此操作。
查询:
SELECT
id,
rec->'$.type' type,
rec->'$.score' score,
rec->'$.title' title,
rec->'$.keyword' keyword
FROM (
SELECT t.id, JSON_EXTRACT(t.val, CONCAT('$[', x.idx, ']')) AS rec
FROM
mytable t
INNER JOIN (
SELECT 0 AS idx UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
) AS x ON JSON_EXTRACT(t.val, CONCAT('$[', x.idx, ']')) IS NOT NULL
) z
这将为每个JSON数组处理多达10个对象(如果您期望更多,可以添加扩展
UNION ALL
查询的一部分)。
在
| id | type | score | title | keyword |
| --- | -------------------- | ----- | ------------------------- | ------------------------- |
| 1 | "automated_backfill" | 4 | "Walgreens Sales Ad" | "Walgreens Sales Ad" |
| 1 | "automated_backfill" | 4 | "Nicoderm Coupons" | "Nicoderm Coupons" |
| 1 | "automated_backfill" | 3 | "Iphone Sales" | "Iphone Sales" |
| 1 | "automated_backfill" | 1 | "Best Top Load Washers" | "Best Top Load Washers" |
| 1 | "automated_backfill" | 1 | "Top 10 Best Cell Phones" | "Top 10 Best Cell Phones" |
| 1 | "automated_backfill" | 0 | "Tv Deals" | "Tv Deals" |
注意:Mariadb不提供箭头运算符。你可以使用
JSONNA提取()
相反,像:
SELECT
id,
JSON_EXTRACT(rec, '$.type') type,
JSON_EXTRACT(rec, '$.score') score,
JSON_EXTRACT(rec, '$.title') title,
JSON_EXTRACT(rec, '$.keyword') keyword
FROM
...