從文本檔案中查詢資料并獲取JSON列

使用 SQL Server 2019 速成版。

我有一個這樣的文本檔案：

/type/author    /authors/OL1002354A 2   2008-08-20T18:07:53.62084   {"name": "Don L. Brigham", "personal_name": "Don L. Brigham", "last_modified": {"type": "/type/datetime", "value": "2008-08-20T18:07:53.62084"}, "key": "/authors/OL1002354A", "type": {"key": "/type/author"}, "revision": 2}
/type/author    /authors/OL100246A  1   2008-04-01T03:28:50.625462  {"name": "Talib Samat.", "personal_name": "Talib Samat.", "last_modified": {"type": "/type/datetime", "value": "2008-04-01T03:28:50.625462"}, "key": "/authors/OL100246A", "type": {"key": "/type/author"}, "revision": 1}
/type/author    /authors/OL1002700A 1   2008-04-01T03:28:50.625462  {"name": "Bengt E. Gustafsson Symposium (5th 1988 Stockholm, Sweden)", "last_modified": {"type": "/type/datetime", "value": "2008-04-01T03:28:50.625462"}, "key": "/authors/OL1002700A", "type": {"key": "/type/author"}, "revision": 1}
/type/author    /authors/OL1002807A 2   2008-08-20T18:12:02.683498  {"name": "Ary J. Lamme", "personal_name": "Ary J. Lamme", "last_modified": {"type": "/type/datetime", "value": "2008-08-20T18:12:02.683498"}, "key": "/authors/OL1002807A", "birth_date": "1940", "type": {"key": "/type/author"}, "revision": 2}
/type/author    /authors/OL1002994A 5   2012-03-03T06:50:39.836886  {"name": "R. Baxter Miller", "personal_name": "R. Baxter Miller", "created": {"type": "/type/datetime", "value": "2008-04-01T03:28:50.625462"}, "photos": [7075806, 6974916], "last_modified": {"type": "/type/datetime", "value": "2012-03-03T06:50:39.836886"}, "latest_revision": 5, "key": "/authors/OL1002994A", "type": {"key": "/type/author"}, "revision": 5}
/type/author    /authors/OL100301A  1   2008-04-01T03:28:50.625462  {"name": "Ghazali Basri.", "personal_name": "Ghazali Basri.", "last_modified": {"type": "/type/datetime", "value": "2008-04-01T03:28:50.625462"}, "key": "/authors/OL100301A", "type": {"key": "/type/author"}, "revision": 1}
/type/author    /authors/OL1003201A 2   2008-08-20T18:14:55.775993  {"name": "Robert Smaus", "personal_name": "Robert Smaus", "last_modified": {"type": "/type/datetime", "value": "2008-08-20T18:14:55.775993"}, "key": "/authors/OL1003201A", "type": {"key": "/type/author"}, "revision": 2}
/type/author    /authors/OL1003202A 2   2008-08-20T18:14:56.005766  {"name": "Richard Mark Friedhoff", "personal_name": "Richard Mark Friedhoff", "last_modified": {"type": "/type/datetime", "value": "2008-08-20T18:14:56.005766"}, "key": "/authors/OL1003202A", "type": {"key": "/type/author"}, "revision": 2}
/type/author    /authors/OL1003235A 1   2008-04-01T03:28:50.625462  {"name": "Hunbatz Men", "personal_name": "Hunbatz Men", "last_modified": {"type": "/type/datetime", "value": "2008-04-01T03:28:50.625462"}, "key": "/authors/OL1003235A", "birth_date": "1941", "type": {"key": "/type/author"}, "revision": 1}
/type/author    /authors/OL1003719A 1   2008-04-01T03:28:50.625462  {"name": "NATO Advanced Research Workshop on Ras Oncogenes (1988 Athens, Greece)", "last_modified": {"type": "/type/datetime", "value": "2008-04-01T03:28:50.625462"}, "key": "/authors/OL1003719A", "type": {"key": "/type/author"}, "revision": 1}
/type/author    /authors/OL1003744A 2   2008-08-20T18:20:16.351762  {"name": "Jeanne Thieme", "personal_name": "Jeanne Thieme", "last_modified": {"type": "/type/datetime", "value": "2008-08-20T18:20:16.351762"}, "key": "/authors/OL1003744A", "type": {"key": "/type/author"}, "revision": 2}
/type/author    /authors/OL1003901A 2   2008-08-20T18:21:31.331678  {"name": "Kiiti Morita", "personal_name": "Kiiti Morita", "last_modified": {"type": "/type/datetime", "value": "2008-08-20T18:21:31.331678"}, "key": "/authors/OL1003901A", "birth_date": "1915", "type": {"key": "/type/author"}, "revision": 2}
/type/author    /authors/OL1004047A 1   2008-04-01T03:28:50.625462  {"name": "Murphy, William M.", "personal_name": "Murphy, William M.", "last_modified": {"type": "/type/datetime", "value": "2008-04-01T03:28:50.625462"}, "key": "/authors/OL1004047A", "birth_date": "1942", "type": {"key": "/type/author"}, "revision": 1}

列由制表分隔，行由換行符分隔。

我需要在 JSON 結構的第 4 列中獲取資料。例如，我需要所有“名稱”屬性的值。

我已經使用 SSIS 將資料匯入到表中，然后我可以CROSS APPLY OPENJSON(json_column)很好地獲取鍵和值。但我想知道是否不能單獨使用 SQL/TSQL 來完成，OPENROWSET直接使用并僅使用JSON 格式的列。嘗試使用OPENROWSETwithCROSS APPLY OPENJSON(BulkColumn)但無法完成，因為其余列不是 JSON 格式。

關于如何避免此錯誤或不同方法的任何想法？

uj5u.com熱心網友回復：

您可以使用BULK INSERT將檔案放入臨時表并將其決議為制表符分隔的檔案。然后OPENJSON用于獲取 JSON 資料。以下對我有用：

DROP TABLE IF EXISTS #Temp;
CREATE TABLE #Temp (
    /* Just some random column names*/
    Author NVARCHAR(100),
    AuthorPath NVARCHAR(100),
    IntValue INT,
    Created DATETIME2(3),
    JsonData NVARCHAR(MAX)
);

BULK INSERT #Temp
FROM 'C:\Users\andre\Documents\temp\test.txt'
WITH (
    FIELDTERMINATOR = '\t', --Tab delimited
    ROWTERMINATOR = '\n' --New-line character for row termination
)

SELECT 
    Temp.*,
    JsonData.[name]
FROM #Temp Temp
CROSS APPLY OPENJSON(Temp.JsonData,'$') 
WITH(
    [name] NVARCHAR(200) '$.name'
) JsonData

標籤：sql json sql-server 查询语句 打开行集

上一篇：如何自動決議JSON并獲取變數中的所有鍵值對？

下一篇：PostGreSql中日期和時間的資料型別是什么？