Given the json, I need to group by key userName the object userClientDetailDTOList across all sites->buildings->floors and remove any duplicate mac addresses.

I have been able to do it using jq expression -

[.billingDetailPerSiteDTOList[].billingDetailPerBuildingDTOList[].billingDetailsPerFloorDTOList[].userClientDetailDTOList[] ] | group_by(.userName) | map((.[0]|del(.associatedMacs)) { associatedMacs: (map(.associatedMacs[]) | unique) })

This groups by userName and also removes duplicate macs belonging to particular user. This results in a list as

[
  {
    "userName": "1",
    "associatedMacs": [
      "3:3:3:3:3:3",
      "5:5:5:5:5:5"
    ]
  },
  {
    "userName": "10",
    "associatedMacs": [
      "4:4:4:4:4:4",
      "6:6:6:6:6:6"
    ]
  },
  {
    "userName": "2",
    "associatedMacs": [
      "1:1:1:1:1:1",
      "2:2:2:2:2:2"
    ]
  },
  {
    "userName": "3",
    "associatedMacs": [
      "2:2:2:2:2:2"
    ]
  }
]

Live example

Questions:

1. Can the expression be simplified?
2. How do I remove duplicate mac addresses across all users? The mac address 2:2:2:2:2:2 is repeated for users 2 and 3

CodePudding user response：

The filter is practically as good as it can get. If you really wanted to, you could still change

• del(.associatedMacs) to {userName} for a positive definition, and
• (…) {…} to {userName: …, associatedMacs: …} to avoid the addition,

resulting in

… | map({userName: (.[0].userName), associatedMacs: (map(.associatedMacs[]) | unique)})

Demo

As for the second question, if you treated the input as an INDEX on the IPs, you could mostly reuse the code from earlier (of course, the unique part wouldn't be necessary anymore)

[INDEX(…; .associatedMacs[])[]] | group_by(.userName) | map(…)

[
  {
    "userName": "1",
    "associatedMacs": [
      "3:3:3:3:3:3",
      "5:5:5:5:5:5"
    ]
  },
  {
    "userName": "10",
    "associatedMacs": [
      "4:4:4:4:4:4",
      "6:6:6:6:6:6"
    ]
  },
  {
    "userName": "2",
    "associatedMacs": [
      "1:1:1:1:1:1"
    ]
  },
  {
    "userName": "3",
    "associatedMacs": [
      "2:2:2:2:2:2"
    ]
  }
]

Demo