jq - reformat the JSON and put values as keys into new array and objects?

Question

How can I reformat the JSON, create a new array based on selection criteria and by and put values as keys?

Here is one fragment of the json file:

[
    {
        "Header": {
            "Tenant": "Tenant1"
        },
        "Body": {
            "values": [
                {
                    "id": "ca95",
                    "name": "Property1"
                },
                {
                    "id": "b126",
                    "name": "Property2"
                },
                {
                    "id": "aec0",
                    "name": "Property3"
                },
                {
                    "id": "a62b",
                    "name": "Property4"
                }
            ]
        }
    },
    {
        "Header": {
            "Tenant": "Tenant2"
        },
        "Body": {
            "values": [
                {
                    "id": "ca95",
                    "name": "Property1"
                },
                {
                    "id": "b4cd",
                    "name": "Property4"
                }
            ]
        }
    }
]

Values for the keys Header.Tenant and Body.values[].name are unique, but for the Body.values[].id are not - for the Property4 corresponding "id" from the same object is a62b for the Tenant1 and b4cd for the Tenant2.

I need to find out for the particular "name/Property" key/value how many different corresponding "id" and "Tenant" exist and group them by the value of "id" instead.

The result should look like the following:

[
    {
        "name": "Property1",
        "ca95": [
            "Tenant1",
            "Tenant2"
        ]
    },
    {
        "name": "Property2",
        "b126": [
            "Tenant1"
        ]
    },
    {
        "name": "Property3",
        "aec0": [
            "Tenant1"
        ]
    },
    {
        "name": "Property4",
        "a62b": [
            "Tenant1"
        ]
    },
    {
        "name": "Property4",
        "b4cd": [
            "Tenant2"
        ]
    }
]

How can this be done with jq? I guess I should use group_by and/or reduce?

Thanks

Answer 1

Here is a more efficient solution than is possible using group_by. It uses GROUPS_BY, which is a stream-oriented generalization of group_by:

# Emit a stream of arrays, each corresponding to a group
# defined by f, which can be any jq filter
# that maps every item in the stream to a single value.
def GROUPS_BY(stream; f): 
   reduce stream as $x ({};
     ($x|f) as $s
     | ($s|type) as $t
     | (if $t == "string" then $s else ($s|tojson) end) as $y
     | .[$t][$y] += [$x] )
   | select(.)
   | .[][] ;

The solution can now be written in a straightforward manner without further recourse to reduce:

[ GROUPS_BY(.[]
            | .Header.Tenant as $tenant
            | .Body.values[] | {name, id, tenant: $tenant};
            {name,id}) 
  | (.[0] | { name }) + { (.[0].id): map(.tenant) } ]

Answer 2

I guess I should use group_by and/or reduce?

It can be done quite easily with just group_by and map:

map(.Header.Tenant as $tenant
    | .Body.values[] | {name, id, tenant: $tenant})
| group_by(.name)
| map(group_by(.id))
| map( .[] |  (.[0] | { name }) + { (.[0].id): map(.tenant) })

One way to understand this is to run the first two lines of the program, and then the first three lines, and proceeding incrementally from there. You could also add debug statements along the way.