SQL Server 2016 嵌套 JSON 數組

我將以下 JSON 儲存在 SQL Server 2016 的列中。我試圖弄清楚是否可以為嵌套數組創建計算列。

{
"global": {
   "commands": [{
       "commandname": "hostname xxx-yyy"
   }, {
       "commandname": "boot-start-marker"
   }]
},
"interfaces": [{
   "name": "Loopback0",
   "commands": [{
       "commandname": "description Psuedo Interface used for Management Purposes"
   }, {
       "commandname": "ip address 178.100.100.0 255.255.255.255"
   }, {
       "commandname": "no ip redirects"
   }, {
       "commandname": "no ip proxy-arp"
   }]
}, {
   "name": "GigabitEthernet1/1",
   "commands": [{
       "commandname": "no ip address"
   }, {
       "commandname": "shutdown"
   }]
}]
}

global我使用以下命令為對象創建了一個計算列：

ALTER TABLE [MyTable]
ADD vGlobalCommands AS JSON_QUERY(configinfo, '$.global.commands')

還有一個與之配套的全文索引，效果很好。

現在我想為它創建相同的$.interfaces.commands但我似乎無法弄清楚如何創建計算列。我試過這條路徑，但它為計算列返回空條目。 Interfaces是一個數組而global不是，所以我想我只是不知道讓它工作的語法。或者也許這是不可能的？

我基本上希望能夠查詢所有介面以查看哪些包含命令’xyz’。上面的 JSON 只是一個例子，實際數據要大得多，因此需要全文索引。

有任何想法嗎？

更新：根據這個 Stack Overflow Q & A，不可能索引嵌套數組。

如果有人能想出一種“重新設計”json 或其他想法的方法，我很想听聽他們的意見。

因為有兩個commands你必須指定你想要的一個，例如

SELECT
   JSON_QUERY( configInfo, 'strict $.interfaces[0].commands' ),
   JSON_QUERY( configInfo, 'strict $.interfaces[1].commands' )
FROM dbo.jsonWorking

這確實是一個遲到的答案，但以下建議是可能的選擇。

$.interfaces.commands如何為儲存的 JSON 部分創建計算列？

輸入 JSON的$.interfaces.commands部分是 JSON 數組，因此JSON_VALUE()不是一個選項，因為此函式從 JSON 文本中提取標量值。一種可能的解決方案是創建一個 UDF，儘管您需要測試性能：

桌子：

CREATE TABLE Data (configinfo nvarchar(max))
INSERT INTO Data (configinfo) VALUES (N'{
"global": {
   "commands": [{
       "commandname": "hostname xxx-yyy"
   }, {
       "commandname": "boot-start-marker"
   }]
},
"interfaces": [{
   "name": "Loopback0",
   "commands": [{
       "commandname": "description Psuedo Interface used for Management Purposes"
   }, {
       "commandname": "ip address 178.100.100.0 255.255.255.255"
   }, {
       "commandname": "no ip redirects"
   }, {
       "commandname": "no ip proxy-arp"
   }]
}, {
   "name": "GigabitEthernet1/1",
   "commands": [{
       "commandname": "no ip address"
   }, {
       "commandname": "shutdown"
   }]
}]
}')

UDF：

CREATE FUNCTION vInterfacesCommands(@configinfo nvarchar(max))
RETURNS nvarchar(max) AS  
BEGIN
  RETURN CONCAT(
     N'[',
     STUFF((
     SELECT CONCAT(',', j2.[value])
     FROM OPENJSON(@configinfo, '$.interfaces') WITH (commands nvarchar(max) '$.commands' AS JSON) j1
     CROSS APPLY OPENJSON(j1.commands) j2
     FOR XML PATH('')
     ), 1, 1, N''),
     N']'
  )
END

計算列：

ALTER TABLE Data ADD vInterfacesCommands AS dbo.vInterfacesCommands(configinfo)

解析 JSON 的傳統方法：

解析 JSON 仍然是一種選擇：

SELECT j2.commandname
FROM Data d
CROSS APPLY OPENJSON(d.configinfo, '$.interfaces') WITH (commands nvarchar(max) '$.commands' AS JSON) j1
CROSS APPLY OPENJSON(j1.commands) WITH (commandname nvarchar(100) '$.commandname') j2
WHERE j2.commandname LIKE N'%xyz%' 

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