SQL Server 2016 Nested JSON Array

Question

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.

Answer 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

Answer 2

It's really a late answer, but the following suggestions are possible options.

How to create a computed column for the $.interfaces.commands part of the stored JSON?

The $.interfaces.commands part of the input JSON is a JSON array, so JSON_VALUE() is not an option, because this function extracts a scalar value from a JSON text. One possible solution is to create an UDF, although you'll need to test the performance:

Table:

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

Computed column:

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

Traditional approach with parsing the JSON:

Parsing the JSON is still an option:

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%'