Merge JSON arrays on dissimilar keys

Question

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"
     }
  ]
}

Answer 1

The following uses the relational JOIN() function in jq to join the two result arrays on the elements that are equal with respect to the lower-case variant of the name key (servers) and the cmdb.name key (IPs). It also uses INDEX() to build an index of the IP file's result array. The JOIN() function gives us arrays (pairs in the example) of matching objects that we merge using the add function. After joining and merging, we are left with objects containing both the name and the cmdb.name keys, so we delete the latter in each object.

jq '.result = [JOIN(INDEX(input.result[]; ."cmdb.name"|ascii_downcase); .result[]; .name|ascii_downcase) | add | del(."cmdb.name")]' servers.json ips.json

The jq expression, nicely formatted:

.result =
  [
    JOIN(
      # index on the second file's .cmdb.name key in each result object
      INDEX(
        input.result[];
        ."cmdb.name" | ascii_downcase
      );
      .result[];             # join on the first file's result objects
      .name | ascii_downcase # match using the .name key
    )
    | add               # merge the matched objects
    | del(."cmdb.name") # delete that key we don't want
  ]

Result:

{
  "result": [
    {
      "os": "Microsoft Windows Server 2019 Standard",
      "name": "SERVER1",
      "ip_address": "10.0.0.10",
      "interface": "Intel Wireless-AC 9560 160MHz"
    },
    {
      "os": "Microsoft Windows Server 2019 Standard",
      "name": "SERVER2",
      "ip_address": "10.0.0.10",
      "interface": "Wi-Fi"
    },
    {
      "os": "Microsoft Windows Server 2019 Standard",
      "name": "server3",
      "ip_address": ""
    },
    {
      "os": "Microsoft Windows Server 2016 Standard",
      "name": "server4",
      "ip_address": "10.0.0.10",
      "interface": "Intel Dual Band Wireless-AC 8265"
    }
  ]
}