I have the following JSON stored in a column in SQL Server 2016. I am trying to figure out if it's possible to create a computed column for a nested array.
{
"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"
}]
}]
}
I created a computed column for the global
object with the following command:
ALTER TABLE [MyTable]
ADD vGlobalCommands AS JSON_QUERY(configinfo, '$.global.commands')
And a full text index to go along with it and it works great.
Now I want to create the same for the $.interfaces.commands
but I can't seem to figure out how to create the computed column. I've tried this path but it returns null entries for the computed column. Interfaces
is an array and global
is not, so I guess I just don't know the syntax to get this to work. Or maybe it's not possible?
I basically want to be able to query all the interfaces to see which ones CONTAIN command 'xyz'. The above JSON is just an example, the real data is much larger, hence the need for a full text index.
Any ideas?
Update: According to this Stack Overflow Q & A it isn't possible to index nested arrays.
If anyone can think of a way to "redesign" the json or other ideas I would love to hear them.