Home > database >  How to get key value pairs of the objects from complex JSON using jq and map? (Active Campaign)
How to get key value pairs of the objects from complex JSON using jq and map? (Active Campaign)

Time:02-14

I have following JSON. I want to get key-value pair objects based on their role. In this example there are 3 roles(Presenter, Approver, Customer) but there can be more as it is dynamic.

JSON

{
   "Presenter Name": "Roney",
   "Presenter Email": "[email protected]",
   "Approver Name": "Tim",
   "Approver Email": "[email protected]",
   "Customer Name": "Alex",
   "Customer Email": "[email protected]",   
   "Invoice": "001",
   "Date": "2022-02-14"   
}

Expected output using jq, map,

{
    "Presenter": {
      "email_address": "[email protected]",
      "name": "Roney",
      "role": "Presenter"
    },
    "Approver": {
      "email_address": "[email protected]",
      "name": "Tim",
      "role": "Approver"
    },
    "Customer": {
      "email_address": "[email protected]",
      "name": "Alex",
      "role": "Customer"
    }
}

I have tried till following but didn't get what to do next. Please advice.

to_entries |map( { (.key): { name: .value, email_address:.value, role: .key} } ) | add

CodePudding user response:

This splits the keys at the space character while discarding any items that don't have one in it. Then it assigns the three fields to their values accordingly, using reduce to combine the grouping.

to_entries
| map(.key |= split(" ") | select(.key[1]))
| reduce group_by(.key[0])[] as $g ({};
    .[$g[0].key[0]] = (
      INDEX($g[]; .key[1]) | {
        email_address: .Email.value,
        name: .Name.value,
        role: .Name.key[0]
      }
    )
  )
{
  "Approver": {
    "email_address": "[email protected]",
    "name": "Tim",
    "role": "Approver"
  },
  "Customer": {
    "email_address": "[email protected]",
    "name": "Alex",
    "role": "Customer"
  },
  "Presenter": {
    "email_address": "[email protected]",
    "name": "Roney",
    "role": "Presenter"
  }
}

Demo

CodePudding user response:

{ "Name": "name",  "Email": "email_address" } as $key_map |
to_entries |
map (
   ( .key | split(" ") | select( length == 2 ) ) as [ $role, $raw_key ] |
   [ $role, "role",             $role  ],
   [ $role, $key_map[$raw_key], .value ]
) |
reduce .[] as [ $role, $key, $val ] ( {}; .[ $role ][ $key ] = $val )

Demo on jqplay


In the above, we start by making the data uniform. Specifically, we start by producing the following:

[
   [ "Presenter", "role",          "Presenter"        ],
   [ "Presenter", "name",          "Roney"            ],
   [ "Presenter", "role",          "Presenter"        ],
   [ "Presenter", "email_address", "[email protected]" ],
   [ "Approver",  "role",          "Approver"         ],
   [ "Approver",  "name",          "Tim"              ],
   [ "Approver",  "role",          "Approver"         ],
   [ "Approver",  "email_address", "[email protected]"   ],
   [ "Customer",  "role",          "Customer"         ],
   [ "Customer",  "name",          "Alex"             ],
   [ "Customer",  "role",          "Customer"         ],
   [ "Customer",  "email_address", "[email protected]"  ]
]

There's redundant information, but that doesn't matter.

Then, the final simple reduce builds the desired structure.


.key | split(" ") | select( length == 2 )

can be replaced with the safer

.key | match("^(.*) (Name|Email)$") | .captures | map( .string )

CodePudding user response:

Here's another, shorter approach that doesn't use group_by. Instead, this directly iterates over the initial object using reduce and imediately sets all the fields accordingly if the key followed the space-separated role-key pattern.

reduce (to_entries[] | .key /= " ") as {key: [$role, $key], $value} ({};
  if $key then
    .[$role]  = {({Email: "email_address", Name: "name"}[$key]): $value, $role}
  else . end
)
{
  "Presenter": {
    "name": "Roney",
    "role": "Presenter",
    "email_address": "[email protected]"
  },
  "Approver": {
    "name": "Tim",
    "role": "Approver",
    "email_address": "[email protected]"
  },
  "Customer": {
    "name": "Alex",
    "role": "Customer",
    "email_address": "[email protected]"
  }
}

Demo

  • Related