查詢 JSON 數據

我正在努力從表中提取數據，該表是從 JSON 文件中部分插入的。該表有兩個欄位（name nvarchar(50) 和 details nvarchar(max)）。欄位的值是：

name      details
user01    [{"data": ["SQL Server", "DBA"], "id": 123456}, {"data": ["Profiler", "Tuning"], "recno": 123123}]
user02    [{"data": [], "recno": 121212}, {"data": ["MySQL"], "recno": 112233}]

我需要在新表中的三列（名稱、id 和數據）中包含這些數據。

name,   id,     data
user01  123456  SQL Server, DBA 
user01  123123  Profiler, Tuning
user02  112233  MySQL

你知道怎麼做嗎？

我已經用您的範例數據設置了一個表格，並進行了一些更正：

• 將“日期”更改為“數據”（為了與其他 JSON 條目保持一致）
• 將“recno”更改為“id”（為了與所需輸出和跨 JSON 條目保持一致

讓我知道這些更正是否無效。如果“recno”和“id”的值在實際數據中可以互換使用，則可以解決/合併它們的值。

USE [tempdb];
GO

CREATE TABLE #JsonStuff
(
   [name] varchar(10) NOT NULL,
   details nvarchar(max) NOT NULL
);
GO

INSERT INTO #JsonStuff
   ([name], details)
VALUES
   ('user01', N'[{"data": ["SQL Server", "DBA"], "id": 123456}, {"data": ["Profiler", "Tuning"], "id": 123123}]'),
   ('user02', N'[{"data": [], "id": 121212}, {"data": ["MySQL"], "id": 112233}]');

您可以使用此查詢接近您想要的結果：

SELECT 
   [name], 
   main.id, 
   main.[data]
FROM #JsonStuff js
CROSS APPLY OPENJSON (js.details)
WITH
(
   id int '$.id',
   [data] nvarchar(max) AS JSON
) AS main
WHERE EXISTS 
(
   SELECT 1/0 
   FROM OPENJSON (main.[data])
);

查詢[name]從表中選擇每個。

該OPENJSON函式用於訪問文件內的結構化數據。

然後，它使用CROSS APPLYJSON 數據，以便在 JSON 文件中為每個數組條目獲取一行。

最後，該WHERE EXISTS子句用於消除 JSON 文件中“data”數組為空的行。結果如下所示：

為了擺脫方括號和雙引號，需要OPENJSON在元素上再次使用data以獲取每個內部數組元素的一行，然後在此之後進行正常的字元串連接。像這樣的東西：

SELECT 
   [name], 
   main.id, 
   STUFF
   (
       (
           SELECT ',' + [sublist].[value]
           FROM OPENJSON(main.[data]) AS sublist 
           FOR XML PATH ('')
       ), 1, 1, ''
   ) AS [data]
FROM #JsonStuff js
CROSS APPLY OPENJSON (js.details)
WITH
(
   id int '$.id',
   [data] nvarchar(max) AS JSON
) AS main
WHERE EXISTS 
(
   SELECT 1/0 
   FROM OPENJSON (main.[data])
);

結果如下所示：

我應該注意，這可能對性能不太好（OPENJSON被呼叫了很多次，字元串連接只是我遇到的第一個）。

我不知道表格及其內容目前是什麼樣的，但據我從您的問題中了解到，我建議您創建一個新列，然後執行類似的操作

update tablename
set your_new_column = (select COALESCE (column1_info, column2_info) from tablename where id = your_rows_id)

其中 column1_info 和 column2_info 是您希望從該列中提取的資訊

請參閱https://docs.microsoft.com/de-de/sql/t-sql/language-elements/coalesce-transact-sql?view=sql-server-ver15

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