Insert JSONL with two columns of comma separated fields into separate rows in mysql
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.