Getting Values From Json Data Inside Array in Mysql

Question

We are saving information in a json Column which contain json data in an array.

Data structure:

[
    {
        "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
    }
]

What we are trying:

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

that returns:

We are looking to get each type as row, which i think only possible with stored procedure, return whole column and then run loop on each row and return data...

Or can you think of a better solution?

Either Update Architecture:

or should we change our database and save information in separate table instead to json column ?

And then we can get easily join to get data with adding a foreign key.

Thanks you.

Answer 1

I understand that you are trying to generate a table structure from the content of your JSON array.

You would need to proceed in two steps :

• first, turn each element in the array into a record ; for this, you can generate an inline table of of numbers and use JSON_EXTRACT() to pull up the relevant JSON object.

• then, extract the values of each attribute from each object, generating new columns ; the -> operator can be used for this.

Query :

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

This will handle up to 10 objects per JSON array (if you expect more than that, you can add expand the UNION ALL part of the query).

In this DB Fiddle with your test data, this yields :

| 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"                |

NB : the arrow operator is not available in MariaDB. You can use JSON_EXTRACT() instead, like :

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