Iterate through a json array mysql 8

Question

I have the fiollowing table schema with a JSON field,

CREATE TABLE `test` (
      `stats` json NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

I have inserted the following,

INSERT INTO test VALUES('[{"days": 15, "percentage": 50},{"days": 5, "percentage": 100}]');

What i would like to do now is iterate through this and return the percentage for a given day or just display each key pair value separately. How do i go about it ?

I found the following query from here, and i tried to make it work for my purpose but the output is not as expected. Ideally I would like to do it in a query like this without using stored procedures or anything else,

select stats, i, concat('stats', element) from `test`, json_table(stats, '$[*]' columns (i for ordinality, element varchar(100) path '$')) jt;

So the output would be 50 if 15 was given as the day.

   day percentage
    15  50

Or the following result when iterating without filtering for a particluar day

   day percentage
    15  50
    5   100

Heres the fiddle with my attempt

The keys would be unique in my case.

Akina · Accepted Answer · 2021-02-09 12:58:12Z

1

SELECT stats, i, days, percentage
FROM test, 
     JSON_TABLE(stats, 
                '$[*]' COLUMNS (i FOR ORDINALITY, 
                                days VARCHAR(100) PATH '$.days', 
                                percentage VARCHAR(100) PATH '$.percentage')) jt;

fiddle

answered Feb 9, 2021 at 12:58

Akina

43.2k6 gold badges17 silver badges30 bronze badges

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

Collectives™ on Stack Overflow

Iterate through a json array mysql 8

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related