SQL Server : JSON formatting grouping possible?

Question

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?

user_14868997 · Answer

The only simple way to do this is to self-join

SELECT 
    tOuter.hostIp AS [host.ip]
    ,ports = (
            SELECT
                tInner.port
               ,tInner.protocol
            FROM test tInner
            FOR JSON PATH
         )
FROM test tOuter
GROUP BY
  tOuter.hostIp
FOR JSON PATH;

It is obviously inefficient to self-join, but SQL Server does not support JSON_AGG. You can simulate it with STRING_AGG and no self-join

SELECT 
    t.hostIp AS [host.ip]
    ,ports = JSON_QUERY('[' + STRING_AGG(tInner.json, ',') + ']')
FROM test t
CROSS APPLY (
    SELECT
        t.port
       ,t.protocol
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
) tInner(json)
GROUP BY
  t.hostIp
FOR JSON PATH;

db<>fiddle

As you might imagine, it gets more complex as more and more levels of nesting are involved.

Note:

Do not use ' for quoting column names, only []

There is no need to alias a column for JSON if it is already that name

SQL Server : JSON formatting grouping possible?

1 Answers