Sql-server – SQL Server 2016 Nested JSON Array

jsonsql serversql-server-2016

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.

Best Answer

As there are two commands you have to specify the one you want, eg

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