Insert JSONL with two columns of comma separated fields into separate rows in mysql I have json datas in the format as shown below.
**the number of objects under 'images'(VIEWS, ROOMS, etc.) varied for each JSON object. Not all objects have VIEWS...POOL, it may only have ROOMS.
    &quot;propertyId&quot;:{&quot;ea&quot;:&quot;12345678&quot;,&quot;h&quot;:&quot;&quot;,&quot;vo&quot;:&quot;&quot;},
    &quot;thumbnail&quot;:{&quot;width&quot;:1000,&quot;height&quot;:500,&quot;link&quot;:&quot;xxx.jpg&quot;},
    &quot;hero&quot;:{&quot;title&quot;:&quot;Featured Image&quot;,&quot;width&quot;:1000,&quot;height&quot;:669,&quot;link&quot;:&quot;yyy.jpg&quot;},
    &quot;images&quot;:{
       &quot;VIEWS&quot;:[
        {&quot;title&quot;:&quot;View from Property&quot;,&quot;width&quot;:1000,&quot;height&quot;:666,&quot;link&quot;:&quot;a1.jpg&quot;},
        {&quot;title&quot;:&quot;View from Property&quot;,&quot;width&quot;:1000,&quot;height&quot;:666,&quot;link&quot;:&quot;a2.jpg&quot;}
       ],
        &quot;ROOMS&quot;:[
            {&quot;title&quot;:&quot;Room&quot;,&quot;width&quot;:1000,&quot;height&quot;:669,&quot;link&quot;:&quot;b1.jpg&quot;},
            {&quot;title&quot;:&quot;Room&quot;,&quot;width&quot;:1000,&quot;height&quot;:662,&quot;link&quot;:&quot;b2.jpg&quot;}
       ],
        &quot;EXTERIOR&quot;:[
            {&quot;title&quot;:&quot;Terrace&quot;,&quot;width&quot;:1000,&quot;height&quot;:666,&quot;link&quot;:&quot;c1.jpg&quot;},
            {&quot;title&quot;:&quot;Terrace&quot;,&quot;width&quot;:1000,&quot;height&quot;:666,&quot;link&quot;:&quot;c2.jpg&quot;}
       ],
        &quot;AMENITIES&quot;:[
             {&quot;title&quot;:&quot;Property Amenity&quot;,&quot;width&quot;:1000,&quot;height&quot;:666,&quot;link&quot;:&quot;d1.jpg&quot;}
       ],
        &quot;POOL&quot;:[
            {&quot;title&quot;:&quot;Outdoor Pool&quot;,&quot;width&quot;:1000,&quot;height&quot;:666,&quot;link&quot;:&quot;e1&quot;},
            {&quot;title&quot;:&quot;Outdoor Pool&quot;,&quot;width&quot;:1000,&quot;height&quot;:666,&quot;link&quot;:&quot;e2.jpg&quot;}
    ]}
    }

I wanted to insert the following data into the table with the following format in MySQL Ver 8.0.23 database.

JSON -&gt; Table Reference
    HotelId = propertyId.ea
    hero_url = hero.link
    hero_title = hero.title
    media_title = images.(VIEWS/ROOMS/EXTERIOR/...).title 
    media_url = images.(VIEWS/ROOMS/EXTERIOR/...).link
    &gt;&gt; ...other objects may have different category (VIEWS/ROOMS...)
    created_at and updated_at = date

After searching through the old topics, I was able to use the select statement below to display the format above. However, I'm confused as to it how works when using INSERT.
  SELECT t.HotelID, t.hero_url, t.hero_title,SUBSTRING_INDEX(SUBSTRING_INDEX(t.media_title, ',', n.n), ',', -1) media_title, SUBSTRING_INDEX(SUBSTRING_INDEX(t.images, ',', n.n), ',', -1) images
  FROM hotel_sample.property_photos t CROSS JOIN 
  (
   SELECT a.N + b.N * 10 + 1 n
     FROM 
    (SELECT 0 AS N 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) a
   ,(SELECT 0 AS N 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) b
    ORDER BY n 
   ) n
 WHERE n.n &lt;= 1 + (LENGTH(t.media_title) - LENGTH(REPLACE(t.media_title, ',', '')))
 ORDER BY HotelID

Would appreciate if anyone could show me how I could insert the above JSON data into the table. (MySQL Ver 8.0.23)
CREATE TABLE `property_photos` (
  `id` int NOT NULL AUTO_INCREMENT,
  `HotelID` varchar(10) DEFAULT NULL,
  `hero_url` varchar(255) DEFAULT NULL,
  `hero_title` text,
  `category` varchar(30) DEFAULT NULL,
  `media_url` varchar(255) DEFAULT NULL,
  `media_title` text,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
  PRIMARY KEY (`id`)
The parsing consists from 2 stages/queries.
First stage parses root object properties.
WITH
cte1 AS (SELECT @json AS val)
SELECT val-&gt;&gt;&quot;$.propertyId.ea&quot; + 0 AS ea,
       val-&gt;&gt;&quot;$.propertyId.h&quot; AS h,
       val-&gt;&gt;&quot;$.propertyId.vo&quot; AS vo,
       val-&gt;&gt;&quot;$.thumbnail.width&quot; + 0 AS t_width,
       val-&gt;&gt;&quot;$.thumbnail.height&quot; + 0 AS t_height,
       val-&gt;&gt;&quot;$.thumbnail.link&quot; AS t_link,
       val-&gt;&gt;&quot;$.hero.title&quot; AS h_title,
       val-&gt;&gt;&quot;$.hero.width&quot; + 0 AS h_width,
       val-&gt;&gt;&quot;$.hero.height&quot; + 0 AS h_height,
       val-&gt;&gt;&quot;$.hero.link&quot; AS h_link
FROM cte1

Output:




ea
h
vo
t_width
t_height
t_link
h_title
h_width
h_height
h_link




12345678


1000
500
xxx.jpg
Featured Image
1000
669
yyy.jpg




Second step parses its subobjects.
WITH
cte1 AS (SELECT @json AS val),
cte2 AS (SELECT val-&gt;&gt;&quot;$.propertyId.ea&quot; + 0 AS ea,
                val-&gt;&quot;$.images.VIEWS&quot; AS views,
                val-&gt;&quot;$.images.ROOMS&quot; AS rooms,
                val-&gt;&quot;$.images.EXTERIOR&quot; AS exterior,
                val-&gt;&quot;$.images.AMENITIES&quot; AS amenities,
                val-&gt;&quot;$.images.POOL&quot; AS pool
         FROM cte1),
cte3 AS (SELECT cte2.ea, 'views' type, jsontable.*
         FROM cte2
         CROSS JOIN JSON_TABLE(CAST(cte2.views AS JSON),
                               &quot;$[*]&quot; COLUMNS (title VARCHAR(255) PATH &quot;$.title&quot;,
                                               width INT PATH &quot;$.width&quot;,
                                               height INT PATH &quot;$.height&quot;,
                                               link VARCHAR(255) PATH &quot;$.link&quot;)) jsontable
         WHERE cte2.views IS NOT NULL
         UNION ALL
         SELECT cte2.ea, 'rooms' type, jsontable.*
         FROM cte2
         CROSS JOIN JSON_TABLE(CAST(cte2.rooms AS JSON),
                               &quot;$[*]&quot; COLUMNS (title VARCHAR(255) PATH &quot;$.title&quot;,
                                               width INT PATH &quot;$.width&quot;,
                                               height INT PATH &quot;$.height&quot;,
                                               link VARCHAR(255) PATH &quot;$.link&quot;)) jsontable
         WHERE cte2.rooms IS NOT NULL
         UNION ALL
         SELECT cte2.ea, 'exterior' type, jsontable.*
         FROM cte2
         CROSS JOIN JSON_TABLE(CAST(cte2.exterior AS JSON),
                               &quot;$[*]&quot; COLUMNS (title VARCHAR(255) PATH &quot;$.title&quot;,
                                               width INT PATH &quot;$.width&quot;,
                                               height INT PATH &quot;$.height&quot;,
                                               link VARCHAR(255) PATH &quot;$.link&quot;)) jsontable
         WHERE cte2.exterior IS NOT NULL
         UNION ALL
         SELECT cte2.ea, 'amenities' type, jsontable.*
         FROM cte2
         CROSS JOIN JSON_TABLE(CAST(cte2.amenities AS JSON),
                               &quot;$[*]&quot; COLUMNS (title VARCHAR(255) PATH &quot;$.title&quot;,
                                               width INT PATH &quot;$.width&quot;,
                                               height INT PATH &quot;$.height&quot;,
                                               link VARCHAR(255) PATH &quot;$.link&quot;)) jsontable
         WHERE cte2.amenities IS NOT NULL
         UNION ALL
         SELECT cte2.ea, 'pool' type, jsontable.*
         FROM cte2
         CROSS JOIN JSON_TABLE(CAST(cte2.pool AS JSON),
                               &quot;$[*]&quot; COLUMNS (title VARCHAR(255) PATH &quot;$.title&quot;,
                                               width INT PATH &quot;$.width&quot;,
                                               height INT PATH &quot;$.height&quot;,
                                               link VARCHAR(255) PATH &quot;$.link&quot;)) jsontable
         WHERE cte2.pool IS NOT NULL)
SELECT * FROM cte3;

Output:




ea
type
title
width
height
link




12345678
views
View from Property
1000
666
a1.jpg


12345678
views
View from Property
1000
666
a2.jpg


12345678
rooms
Room
1000
669
b1.jpg


12345678
rooms
Room
1000
662
b2.jpg


12345678
exterior
Terrace
1000
666
c1.jpg


12345678
exterior
Terrace
1000
666
c2.jpg


12345678
amenities
Property Amenity
1000
666
d1.jpg


12345678
pool
Outdoor Pool
1000
666
e1.jpg


12345678
pool
Outdoor Pool
1000
666
e2.jpg




You may use these queries as a rowsource for INSERT statement, combine these queries if needed.
If &quot;$.propertyId.ea&quot; is not enough for unique root object identifying then add necessary columns into the last step query.
https://dbfiddle.uk/?rdbms=mysql_8.0&amp;fiddle=81ebab30922db5f99abd90c8b39622e5
PS. + 0 converts extracted string value to numeric datatype.