1

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.

1

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
  • Thanks for response @wBob. Unfortunately there are a dynamic number of commands per interface and I need to be able to search them all. As you can see in the example, the first interface has 4 commands. There is also a dynamic number of interfaces. – Rick S Aug 3 '17 at 14:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.