Merge JSON files but preserve empty keys (preferably with JQ)

user_7848350 0
Viewed 126

Is there a way to merge two JSON files on a key but preserve empty keys, preferably in JQ?

The files are merged on url.

File 1:

[
  {
    "url": "ABC",
    "name": "Search",
    "version": "2020",
    "Ocurrences": "25"
  },
  {
    "url": "DEF",
    "name": "Welcome",
    "version": "2021",
    "Ocurrences": "10"
  }
]

File 2:

[
  {
    "url": "DEF",
    "title": "Support"
  },
  {
    "url": "GHI",
    "title": "Contact"
  }
]

Desired result:

[
  {
    "url": "ABC",
    "title": "",
    "name": "Search",
    "version": "2020",
    "Ocurrences": "25"
  },
  {
    "url": "DEF",
    "title": "Support",
    "name": "Welcome",
    "version": "2021",
    "Ocurrences": "10"
  },
  {
    "url": "GHI",
    "title": "Contact",
    "name": "",
    "version": "",
    "Ocurrences": ""
  }
]

Current JQ script:

jq -M '[group_by(.url)[]|add]' fi1e1.json file2.json > output.json

Current result:

[
  {
    "url": "ABC",
    "name": "Search",
    "version": "2020",
    "Ocurrences": "25"
  },
  {
    "url": "DEF",
    "title": "Support",
    "name": "Welcome",
    "version": "2021",
    "Ocurrences": "10"
  },
  {
    "url": "GHI",
    "title": "Contact"
  }
]

The real files have 100+ objects. Order of keys doesn't matter. JQ is preferred because I know Bash only.

The goal is to merge data extracted from Azure Application Insights with data on a local hard drive.

Thank you for any help or direction.

jq join bash json
1 Answers

Yes.

Using reduce avoids the sorting that is entailed by group_by. For example, you could write:

. as $one
| input as $two
| ($one[0] + $two[0] | map_values("")) as $defaults
| INDEX($one[]; .url) as $one
| INDEX($two[]; .url) as $two
| reduce ($one+$two|keys_unsorted)[] as $k ({}; 
    .[$k] = ($defaults + $one[$k] + $two[$k]) )
| [.[]]

Notes:

  1. The above assumes an invocation of the form: jq ... file1.json file2.json

  2. Notice how jq supports shadowing of "$-variables".

  3. It might be worth considering using null rather than "" as the value for what you've called "empty keys".

  4. Depending on the detailed requirements, you might want to set $defaults along the lines of:

    (($one|add) + ($two|add) | map_values(""))

user_9973580
Related