{ "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" }] }] } 

UPDATE: According to this post it isn't possible to index nested arrays. https://stackoverflow.com/questions/40584687/ms-sql-json-query-where-clause-nested-array-items

If anyone can think of a way to "redesign" the json or other ideas I would love to hear them. Thanks

{ "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" }] }] } 

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.

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?

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 post it isn't possible to index nested arrays. https://stackoverflow.com/questions/40584687/ms-sql-json-query-where-clause-nested-array-items

If anyone can think of a way to "redesign" the json or other ideas I would love to hear them. Thanks