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.

GMB · Accepted Answer · 2019-02-11 10:22:52Z

11

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

edited Feb 11, 2019 at 10:22

answered Feb 10, 2019 at 14:41

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Furqan Freed Over a year ago

but its not working for MariaDB ! i think because of arrow operator "->" ?

GMB Over a year ago

@FurqanFreed : yes it looks like the -> is not supported in MariaDB, I updated my answer to provide an alternative using JSON_EXTRACT()

Collectives™ on Stack Overflow

Getting Values From Json Data Inside Array in Mysql

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related