將帶有兩列逗號分隔欄位的 JSONL 插入到 mysql 中的單獨行中

我有格式如下所示的 json 數據。

**“圖像”下的對像數量（視圖、房間等）因每個 JSON 對象而異。並非所有對像都有 VIEWS…POOL，它可能只有 ROOMS。

   "propertyId":{"ea":"12345678","h":"","vo":""},
   "thumbnail":{"width":1000,"height":500,"link":"xxx.jpg"},
   "hero":{"title":"Featured Image","width":1000,"height":669,"link":"yyy.jpg"},
   "images":{
      "VIEWS":[
       {"title":"View from Property","width":1000,"height":666,"link":"a1.jpg"},
       {"title":"View from Property","width":1000,"height":666,"link":"a2.jpg"}
      ],
       "ROOMS":[
           {"title":"Room","width":1000,"height":669,"link":"b1.jpg"},
           {"title":"Room","width":1000,"height":662,"link":"b2.jpg"}
      ],
       "EXTERIOR":[
           {"title":"Terrace","width":1000,"height":666,"link":"c1.jpg"},
           {"title":"Terrace","width":1000,"height":666,"link":"c2.jpg"}
      ],
       "AMENITIES":[
            {"title":"Property Amenity","width":1000,"height":666,"link":"d1.jpg"}
      ],
       "POOL":[
           {"title":"Outdoor Pool","width":1000,"height":666,"link":"e1"},
           {"title":"Outdoor Pool","width":1000,"height":666,"link":"e2.jpg"}
   ]}
   }

我想將以下數據插入到 MySQL Ver 8.0.23 數據庫中具有以下格式的表中。

JSON -> 表參考

   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
   >> ...other objects may have different category (VIEWS/ROOMS...)
   created_at and updated_at = date

在搜尋了舊主題之後，我能夠使用下面的 select 語句來顯示上面的格式。但是，我對使用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 <= 1 + (LENGTH(t.media_title) - LENGTH(REPLACE(t.media_title, ',', '')))
ORDER BY HotelID

如果有人能告訴我如何將上述 JSON 數據插入表中，將不勝感激。(MySQL 版本 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`)

解析由 2 個階段/查詢組成。

第一階段解析根對象屬性。

WITH
cte1 AS (SELECT @json AS val)
SELECT val->>"$.propertyId.ea" + 0 AS ea,
      val->>"$.propertyId.h" AS h,
      val->>"$.propertyId.vo" AS vo,
      val->>"$.thumbnail.width" + 0 AS t_width,
      val->>"$.thumbnail.height" + 0 AS t_height,
      val->>"$.thumbnail.link" AS t_link,
      val->>"$.hero.title" AS h_title,
      val->>"$.hero.width" + 0 AS h_width,
      val->>"$.hero.height" + 0 AS h_height,
      val->>"$.hero.link" AS h_link
FROM cte1

輸出：

第二步解析其子對象。

WITH
cte1 AS (SELECT @json AS val),
cte2 AS (SELECT val->>"$.propertyId.ea" + 0 AS ea,
               val->"$.images.VIEWS" AS views,
               val->"$.images.ROOMS" AS rooms,
               val->"$.images.EXTERIOR" AS exterior,
               val->"$.images.AMENITIES" AS amenities,
               val->"$.images.POOL" AS pool
        FROM cte1),
cte3 AS (SELECT cte2.ea, 'views' type, jsontable.*
        FROM cte2
        CROSS JOIN JSON_TABLE(CAST(cte2.views AS JSON),
                              "$[*]" COLUMNS (title VARCHAR(255) PATH "$.title",
                                              width INT PATH "$.width",
                                              height INT PATH "$.height",
                                              link VARCHAR(255) PATH "$.link")) 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),
                              "$[*]" COLUMNS (title VARCHAR(255) PATH "$.title",
                                              width INT PATH "$.width",
                                              height INT PATH "$.height",
                                              link VARCHAR(255) PATH "$.link")) 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),
                              "$[*]" COLUMNS (title VARCHAR(255) PATH "$.title",
                                              width INT PATH "$.width",
                                              height INT PATH "$.height",
                                              link VARCHAR(255) PATH "$.link")) 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),
                              "$[*]" COLUMNS (title VARCHAR(255) PATH "$.title",
                                              width INT PATH "$.width",
                                              height INT PATH "$.height",
                                              link VARCHAR(255) PATH "$.link")) 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),
                              "$[*]" COLUMNS (title VARCHAR(255) PATH "$.title",
                                              width INT PATH "$.width",
                                              height INT PATH "$.height",
                                              link VARCHAR(255) PATH "$.link")) jsontable
        WHERE cte2.pool IS NOT NULL)
SELECT * FROM cte3;

輸出：

---

您可以將這些查詢用作 INSERT 語句的行源，如果需要，可以組合這些查詢。

如果"$.propertyId.ea"不足以辨識唯一根對象，則將必要的列添加到最後一步查詢中。

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=81ebab30922db5f99abd90c8b39622e5

PS。+ 0將提取的字元串值轉換為數字數據類型。

引用自：https://dba.stackexchange.com/questions/291156