While unnesting an object in jq, how can I avoid restating labels at each stage in the pipeline?

Question

Summary:

I have successfully worked out how to unnest objects in jq; however, the working code I have written requires a lot of repetition. I feel it's likely there is a cleaner or less verbose way to achieve this same result and I would like to know what it is.

Example:

With the following nested structure of companies, suppose the goal is to extract the name, ID, company and site for each person listed. (We can ignore the address.)

Input:

{
  "company": "Initrode",
  "sites": [
    {
      "name": "HQ",
      "address": "123 Main Street",
      "personnel": [
        {
          "name": "John Smith",
          "UID": 12345
        },
        {
          "name": "Jane Doe",
          "UID": 23456
        }
      ]
    },
    {
      "name": "Branch Office",
      "address": "Spodunk, Nowhereville",
      "personnel": [
        {
          "name": "Fred Anderson",
          "UID": 56789
        },
        {
          "name": "Bill Jones",
          "UID": 34567
        }
      ]
    }
  ]
}
{
  "company": "Inittech",
  "sites": [
    {
      "name": "Main Office",
      "address": "5678 Avenue Blvd",
      "personnel": [
        {
          "name": "Fred Johnson",
          "UID": 6543
        },
        {
          "name": "James Fredson",
          "UID": 9876
        }
      ]
    },
    {
      "name": "Testing Station",
      "address": "Alaskan Wilderness",
      "personnel": [
        {
          "name": "Sally May",
          "UID": 5432
        },
        {
          "name": "Jack James",
          "UID": 8765
        }
      ]
    }
  ]
}

Working code:

jq '{company,site: .sites[]}|
{company,site: .site.name,personnel: .site.personnel[]}|
{name: .personnel.name,id: .personnel.UID,company,site}' sample.json

Correct output:

{
  "name": "John Smith",
  "id": 12345,
  "company": "Initrode",
  "site": "HQ"
}
{
  "name": "Jane Doe",
  "id": 23456,
  "company": "Initrode",
  "site": "HQ"
}
{
  "name": "Fred Anderson",
  "id": 56789,
  "company": "Initrode",
  "site": "Branch Office"
}
{
  "name": "Bill Jones",
  "id": 34567,
  "company": "Initrode",
  "site": "Branch Office"
}
{
  "name": "Fred Johnson",
  "id": 6543,
  "company": "Inittech",
  "site": "Main Office"
}
{
  "name": "James Fredson",
  "id": 9876,
  "company": "Inittech",
  "site": "Main Office"
}
{
  "name": "Sally May",
  "id": 5432,
  "company": "Inittech",
  "site": "Testing Station"
}
{
  "name": "Jack James",
  "id": 8765,
  "company": "Inittech",
  "site": "Testing Station"
}

The problem:

There is a lot of repetition involved here. Aside from repeating the outer labels at each stage of the pipeline, there's also the repetition of .site and .personnel in the second and third parts of the pipeline respectively.

My real data is much more complicated, so this repetition is even worse and is much harder to read.

Incidentally, here is some NON-WORKING code that I tried earlier for the same goal above:

jq '{company,site: .sites[].name,name: .sites[].personnel[].name,id: .sites[].personnel[].UID}' sample.json

That is much less repetition, but unfortunately it returns every person associated with every ID and site at their company - incorrect results, like a database "cross join" instead of "inner join."

I don't quite know how to describe in words what's needed here, but hopefully the above sample helps make it clear.

One way to describe it is that I'm trying to merge multiple name-value pairs from arrays of sub-objects into the top-level object, without returning together any combinations of name-value pairs taken from different sub-objects within the same array value. But that's not exactly easy to follow even for me; hence the above example input/output.

Just for interest, here is the real working code I have, with attribute names obfuscated:

jq '.pears[]|{pear: .name,file: .somepath,toBeFiltered: (.appletypes[]|select(.name == "orange")|.bananas[]|{banana: .name,apples: .apples[]})}|{pear,file,banana: .toBeFiltered.banana,applestem: .toBeFiltered.apples.applestem,orangecomment: (.toBeFiltered.apples.peaches[]|select(.akey == "string")|.avalue.value),linenumber: (.toBeFiltered.apples.peaches[]|select(.akey == "string")|.line)}' realfile.json

user_997358 · Answer

Perhaps the thing you're missing is the utility of jq variables:

.company as $company
| .sites[]
| .name as $site
| .personnel[]
| { name, id: .UID, $company, $site }

({$x} is shorthand for { x: $x }.)

However it's also possible to avoid variables by using parentheses with care. If you don't mind the keys being in a slightly different order, you could write:

(.sites[] | ( (.personnel[] | { name, id: .UID} ) +  {site: .name} )) + {company}

If the keys must be in the order shown in the Q, you could simply append the following filter to the above pipeline:

{name, id, company, site}