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