mysql - 在MySQL中从数组内的Json数据获取值

我们将信息保存在json列中，该列包含数组中的json数据。

数据结构:

[
    {
        "type":"automated_backfill",
        "title":"Walgreens Sales Ad",
        "keyword":"Walgreens Sales Ad",
        "score":4
    },
    {
        "type":"automated_backfill",
        "title":"Nicoderm Coupons",
        "keyword":"Nicoderm Coupons",
        "score":4
    },
    {
        "type":"automated_backfill",
        "title":"Iphone Sales",
        "keyword":"Iphone Sales",
        "score":3
    },
    {
        "type":"automated_backfill",
        "title":"Best Top Load Washers",
        "keyword":"Best Top Load Washers",
        "score":1
    },
    {
        "type":"automated_backfill",
        "title":"Top 10 Best Cell Phones",
        "keyword":"Top 10 Best Cell Phones",
        "score":1
    },
    {
        "type":"automated_backfill",
        "title":"Tv Deals",
        "keyword":"Tv Deals",
        "score":0
    }
]

我们正在尝试的是:

SELECT id, ad_meta->'$**.type' FROM window_requests

返回:



我们正在寻求将每种类型都作为行，我认为这仅适用于存储过程，返回整列，然后在每一行上运行循环并返回数据...

还是您能想到更好的解决方案？

两种更新架构:

还是我们应该更改数据库并将信息保存在单独的表中而不是json列中？

然后，通过添加外键，我们可以轻松地加入以获取数据。

谢谢。

最佳答案

我了解您正在尝试根据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部分)。

在带有测试数据的 this DB Fiddle 中，得出:

| 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中不可用。您可以改用JSON_EXTRACT()，例如:

SELECT
    id,
    JSON_EXTRACT(rec, '$.type') type,
    JSON_EXTRACT(rec, '$.score') score,
    JSON_EXTRACT(rec, '$.title') title,
    JSON_EXTRACT(rec, '$.keyword') keyword
FROM
    ...