將格式不佳的 CSV/JSON 導入 SQL Server

我正在嘗試將 Android 通知日誌解析到數據庫中。列不均勻，因為不同的通知省略了某些屬性。本質上，它是一個鍵值對的 CSV 文件。換句話說，它是一個格式非常糟糕（且語法不正確）的 JSON 文件。也許向你展示會更容易……

{"packageName":"com.automatic.mustang","postTime":1489795785973,"systemTime":1489795786083,"offset":-14400000,"isOngoing":false}
{"packageName":"com.enhance.gameservice","postTime":1489040766066,"systemTime":1489040766136,"offset":-18000000,"isOngoing":false}
{"packageName":"com.automatic.mustang","postTime":1489795785973,"systemTime":1489795786083,"offset":-14400000,"isOngoing":false}
{"packageName":"com.enhance.gameservice","postTime":1489040766066,"systemTime":1489040766136,"offset":-18000000,"isOngoing":false}

**為了簡潔起見，我將它們截斷了，但它們並沒有排在行尾。**一些文本值被引用，一些沒有。*所有的鍵都被引用。*對列進行排序後，我可以使用字元串函式來修剪無關字元，但問題在於：如何將值排序到列中？我可以用大約 4 種不同的程式語言做到這一點，但我只是在學習 T-SQL。

如何在單個查詢中將其解析為 SQL Server (2016)？甚至可能嗎？

我可以將它導入一個臨時表，然後將其轉移到永久表，但我希望有一個查詢來消化資訊，因為這將每天（可能更多）重複。如果我可以聲明一個二級分隔符，那將會很神奇，因為所有值都以冒號開頭。將冒號聲明為分隔符會以另一種方式破壞它…arg(s)！

數據到處亂七八糟。即使是來自同一個應用程序的通知也可能截然不同。我在 PHP、C、JS 等中執行此操作的方式是循環遍歷鍵，直到我點擊所需的鍵，獲取它們後面的適當值（或整個對），然後修剪無關的字元。我只是不知道如何在 T-SQL 中做到這一點，並且線上文件還有一些不足之處。

預期的結果將是這樣的：

Package Name | Post Time | System Time | Offset | NotificationText
-----------------------------------------------------------------
automatic    | 1489795   | 786083      | -1400  | this is the note
gameservice  | 1489756   | 786090      | -1400  | this is the note
automatic    | 1489799   | 786045      | -1400  | this is the note

最大的問題是我需要的資訊分散在行中。我不需要太多，但它被掩埋了。

您可以使用Jovan Popovic 編寫的 SQL Server 數據庫引擎部落格上的在 SQL Server 2016 中載入行分隔的 JSON 文件中所示的技術。

例如，給定一個文件import.txt：

{"packageName":"com.automatic.mustang","postTime":1489795785973,"systemTime":1489795786083,"offset":-14400000,"isOngoing":false}
{"packageName":"com.enhance.gameservice","postTime":1489040766066,"systemTime":1489040766136,"offset":-18000000,"isOngoing":false}
{"packageName":"com.automatic.mustang","postTime":1489795785973,"systemTime":1489795786083,"offset":-14400000,"isOngoing":false}
{"packageName":"com.enhance.gameservice","postTime":1489040766066,"systemTime":1489040766136,"offset":-18000000,"isOngoing":false}

和一個格式文件import.fmt：

13.0
1
1 SQLCHAR 0 0 "\r\n" 1 json ""

該文件可以批量導入和解析：

SELECT
   Parsed.packageName,
   Parsed.postTime,
   Parsed.systemTime,
   Parsed.offset,
   Parsed.isOngoing
FROM OPENROWSET
(
   BULK 'C:\Temp\import.txt',
   FORMATFILE= 'C:\Temp\import.fmt'
) AS BulkLog
CROSS APPLY OPENJSON(BulkLog.[json])
WITH
(
   packageName varchar(50),
   postTime    bigint,
   systemTime  bigint,
   offset      bigint,
   isOngoing   bit
) AS Parsed;

給予：

不知道為什麼你認為這是語法錯誤的 JSON，但根據json.org，它看起來不錯。好吧，除了兩件小事：

1. {和之間需要逗號}
2. 需要[和]周圍的整個事情

但除此之外，應該是所有鍵都被引用，並且數字和布爾值不被引用。

SQL Server 2016 具有內置的 JSON 解析。在用問題中的範例數據修復這兩個小問題後，下面的解析它就好了：

DECLARE @JSON NVARCHAR(MAX) =N'
[{"packageName":"com.automatic.mustang","postTime":1489795785973,"systemTime":1489795786083,"offset":-14400000,"isOngoing":false},
{"packageName":"com.enhance.gameservice","postTime":1489040766066,"systemTime":1489040766136,"offset":-18000000,"isOngoing":false},
{"packageName":"com.automatic.mustang","postTime":1489795785973,"systemTime":1489795786083,"offset":-14400000,"isOngoing":false},
{"packageName":"com.enhance.gameservice","postTime":1489040766066,"systemTime":1489040766136,"offset":-18000000,"isOngoing":false}]
';

  SELECT [PackageName],
         [IsOngoing]
  FROM   OPENJSON(@JSON)
  WITH (
     [PackageName] VARCHAR(MAX)       '$.packageName',
     [IsOngoing]   BIT                '$.isOngoing'
  );

回報：

PackageName                IsOngoing
com.automatic.mustang      0
com.enhance.gameservice    0
com.automatic.mustang      0
com.enhance.gameservice    0

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