'SQL Server : JSON formatting grouping possible?

I have a single SQL Server table of the form:

192.168.1.1, 80 , tcp
192.168.1.1, 443, tcp
...

I am exporting this to json like this:

SELECT 
    hostIp AS 'host.ip',
    'ports' = (SELECT port AS 'port', protocol AS 'protocol'
               FOR JSON PATH)
FROM 
    test
FOR JSON PATH

This query results in:

[
    {
        "host": {
            "ip": "192.168.1.1"
        },
        "ports": [
            {
                "port": 80,
                "protocol": "tcp"
            }
        ]
    },
    {
        "host": {
            "ip": "192.168.1.1"
        },
        "ports": [
            {
                "port": 443,
                "protocol": "tcp"
            }
        ]
    },
....

However I wanted all data from a single IP grouped as such:

[
    {
        "host": {
            "ip": "192.168.1.1"
        },
        "ports": [
            {
                "port": 80,
                "protocol": "tcp"
            },
            {
                "port": 443,
                "protocol": "tcp"
            }
        ]
    },
...

I have found that there seems to be aggregate functions, but they either don't work for me or are for Postgresql, my example is in SQL Server.

Any idea how to get this to work?



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source