using jq to merge two json values based on key value

user_14250441 0
Viewed 445

i have two json files that i would like to merge based on the value of a key. the key name is different in both json files but the value would be the same. i am using jq to try to get this done. most of the examples i have found all merge based on key name and not value.

sample1.json

 [
  {
    "unique_id": "pp1234",
    "unique_id_type": "netid",
    "rfid": "12245556890478",
 },
{
    "unique_id": "aqe123",
    "unique_id_type": "netid",
    "rfid": "12234556890478",
 }
]

sample2.json

[
 {
 "mailing_state": "New York",
  "mobile_phone_number": "(982) 2541212",
  "netid": "pp1234",
  "netid_reachable": "Y",
 },
 {
 "mailing_state": "New York",
  "mobile_phone_number": "(982) 5551212",
  "netid": "aqe123",
  "netid_reachable": "Y",
 }
]

i would want the output to look something like:

results.json

 [
      {
        "unique_id": "pp1234",
        "unique_id_type": "netid",
        "rfid": "12245556890478",
        "mailing_state": "New York",
        "mobile_phone_number": "(982) 2541212",
        "netid_reachable": "Y",
     },
     {
        "unique_id": "aqe123",
        "unique_id_type": "netid",
        "rfid": "12234556890478",
        "mailing_state": "New York",
        "mobile_phone_number": "(982) 5551212",
        "netid_reachable": "Y",
    }
]

order of results would not matter as long as the records are merged based on netid/unique_id keys. i am open to using something other than jq if necessary. thanks in advance.

jq key-value merge file json
1 Answers

Once the sample input files have been corrected, the following invocation should do the trick:

jq --argfile uid sample1.json '
  ($uid | INDEX(.unique_id)) as $dict
  | map( $dict[.netid] + del(.netid) )
' sample2.json

If you prefer not to use --argfile because it has been deprecated, you could (for example) use --slurpfile and change $uid in the jq program to $uid[0].

user_9973580
Related