Parsing out JSON column Array data in Mysql 5.7

Question

I have some data like this

Product Siz Color     RFIDs
GO1199  5XL WHIT    "[\"300ED89F335000B333B8CA8D\",\"300ED89F335000B333B8C5F3\",\"E2009A4050026AF000001928\"]"
GO1189  LAR WHIT    "[\"300ED89F335000B333B8CA8D\",\"300ED89F335000B333B8C5F3\",\"E2009A4050026AF000001928\"]"
GO1179  LAR WHIT    "[\"300ED89F335000B333B8CA76\",\"300ED89F335000B333B8C7C8\",\"300ED89F335000B333B8C58D\"]",
GO1169  LAR WHIT    "[\"300ED89F335000999A72D381\",\"300ED89F3350007FC4FDCCFB\",\"300ED89F3350007FC4FDDEF9\"]",
GO1199  LAR WHIT    "[\"300ED89F3350007FC4FDDF5E\",\"300ED89F3350007FC4FDDDE1\",\"300ED89F3350007FC4FDDDDF\"]"

The last column is array of RFID's associated wit that product. I want prase it out and display it like so using SQL

Product Siz Color     RFID
GO1199  5XL WHIT    300ED89F335000B333B8CA8D
GO1199  5XL WHIT    300ED89F335000B333B8C5F3
GO1199  5XL WHIT    E2009A4050026AF000001928
GO1189  LAR WHIT    300ED89F335000B333B8CA8D
GO1189  LAR WHIT    300ED89F335000B333B8C5F3
GO1189  LAR WHIT    E2009A4050026AF000001928

Thanks.

Sorry this is in 5.7 not 8

Answer 1

In MySQL 8.0, use json_table():

select t.product, t.size, t.color, x.rfid
from mytable t
cross join json_table(t.rfids, '$[*]' columns (rfid varchar(50) path '$')) x

In earlier versions, it is a bit more complicated. Basically you need a table of numbers to unnest the arrays:

select t.product, t.size, t.color, 
    json_unquote(json_extract(t.rfids, concat('$[', n.i, ']'))) rfid
from mytable t
inner join (select 0 i union all select 1 union all select 2) n
    on n.i < json_length(t.rfids)

You can expand the subquery with more numbers to handle more than 3 items per array.

Demo on DB Fiddle:

product | size | color | rfid                    
:------ | :--- | :---- | :-----------------------
GO1199  | 5XL  | WHIT  | E2009A4050026AF000001928
GO1199  | 5XL  | WHIT  | 300ED89F335000B333B8C5F3
GO1199  | 5XL  | WHIT  | 300ED89F335000B333B8CA8D
GO1189  | LAR  | WHIT  | E2009A4050026AF000001928
GO1189  | LAR  | WHIT  | 300ED89F335000B333B8C5F3
GO1189  | LAR  | WHIT  | 300ED89F335000B333B8CA8D
GO1179  | LAR  | WHIT  | 300ED89F335000B333B8C58D
GO1179  | LAR  | WHIT  | 300ED89F335000B333B8C7C8
GO1179  | LAR  | WHIT  | 300ED89F335000B333B8CA76
GO1169  | LAR  | WHIT  | 300ED89F3350007FC4FDDEF9
GO1169  | LAR  | WHIT  | 300ED89F3350007FC4FDCCFB
GO1169  | LAR  | WHIT  | 300ED89F335000999A72D381
GO1199  | LAR  | WHIT  | 300ED89F3350007FC4FDDDDF
GO1199  | LAR  | WHIT  | 300ED89F3350007FC4FDDDE1
GO1199  | LAR  | WHIT  | 300ED89F3350007FC4FDDF5E