I have 2 JSON files with arrays (extracted from restAPI using curl in bash). Both files are arrays with a .result
object at the top which needs to remain. The first has a .name
field and many (over 100) other fields including .ip_address
which is blank. The second has additional information like interface and ip address and the name (key) in a different field name. The key in the first file is not the same as the key in the second but should be related (I could change the key field name of course before and the key's data is often mixed case)
I want to merge them so that the first JSON's ip_address
is overwritten where the .name
fields are the same and the additional data added:
I get that it's either multiplying the arrays or reducing and adding but I can't get it right based on the examples already found. For a solution, I would be most grateful.
Servers:
{
"result": [
{
"os": "Microsoft Windows Server 2019 Standard",
"name": "SERVER1",
"ip_address": ""
},
{
"os": "Microsoft Windows Server 2019 Standard",
"name": "SERVER2",
"ip_address": ""
},
{
"os": "Microsoft Windows Server 2019 Standard",
"name": "server3",
"ip_address": ""
},
{
"os": "Microsoft Windows Server 2016 Standard",
"name": "server4",
"ip_address": ""
}
]
}
ips.txt
{
"result": [
{
"interface": "Intel Wireless-AC 9560 160MHz",
"cmdb.name": "server1",
"ip_address": "10.0.0.10"
},
{
"interface": "Wi-Fi",
"cmdb.name": "server2",
"ip_address": "10.0.0.10"
},
{
"interface": "Intel Dual Band Wireless-AC 8265",
"cmdb.name": "server4",
"ip_address": "10.0.0.10"
}
]
}
Desired output of Server data is:
{
"result": [
{
"os": "Microsoft Windows Server 2019 Standard",
"name": "SERVER1",
"interface": "Intel Wireless-AC 9560 160MHz",
"ip_address": "10.0.0.10"
},
{
"os": "Microsoft Windows Server 2019 Standard",
"name": "SERVER2",
"interface": "Wi-Fi",
"ip_address": "10.0.0.10"
},
{
"os": "Microsoft Windows Server 2019 Standard",
"name": "server3",
"ip_address": ""
},
{
"os": "Microsoft Windows Server 2016 Standard",
"name": "server4",
"interface": "Intel Dual Band Wireless-AC 8265",
"ip_address": "10.0.0.10"
}
]
}
Best Answer
The following uses the relational
JOIN()
function injq
to join the tworesult
arrays on the elements that are equal with respect to the lower-case variant of thename
key (servers) and thecmdb.name
key (IPs). It also usesINDEX()
to build an index of the IP file'sresult
array. TheJOIN()
function gives us arrays (pairs in the example) of matching objects that we merge using theadd
function. After joining and merging, we are left with objects containing both thename
and thecmdb.name
keys, so we delete the latter in each object.The
jq
expression, nicely formatted:Result: