1

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": "roney@domain.com",
   "Approver Name": "Tim",
   "Approver Email": "tim@domain.com",
   "Customer Name": "Alex",
   "Customer Email": "alex@domain.com",   
   "Invoice": "001",
   "Date": "2022-02-14"   
}

Expected output using jq, map,

{
    "Presenter": {
      "email_address": "roney@domain.com",
      "name": "Roney",
      "role": "Presenter"
    },
    "Approver": {
      "email_address": "tim@domain.com",
      "name": "Tim",
      "role": "Approver"
    },
    "Customer": {
      "email_address": "alex@domain.com",
      "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

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
NJ Bhanushali
  • 901
  • 1
  • 12
  • 21
  • `add` would be useful if you had `[ { "Presenter": { ... } }, { "Approver": { ... } }, ... ]`. It would also be useful for a given role if you had `[ { "email_address": ... }, { "name": ... }, { "role": ... } ]`. But' it's more convenient to place it in the final format directly than in these formats. – ikegami Feb 13 '22 at 22:34

3 Answers3

3

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": "tim@domain.com",
    "name": "Tim",
    "role": "Approver"
  },
  "Customer": {
    "email_address": "alex@domain.com",
    "name": "Alex",
    "role": "Customer"
  },
  "Presenter": {
    "email_address": "roney@domain.com",
    "name": "Roney",
    "role": "Presenter"
  }
}

Demo

pmf
  • 24,478
  • 2
  • 22
  • 31
  • I have tried this. I am getting this error `jq: error: INDEX/2 is not defined at , line 1:`. I make 2 change. 1- Removed `role: .Name.key[0]`, 2- Use Email instead of Name like this `role: .Email.key[0]`. Can you please help me? I don't want name anymore. – NJ Bhanushali Feb 21 '22 at 18:39
  • @NJBhanushali `INDEX/2` is part of the [SQL-StyleOperators](https://stedolan.github.io/jq/manual/v1.6/#SQL-StyleOperators) introduced in jq v1.6. It looks like you are running an older version. – pmf Feb 21 '22 at 18:42
  • I am using this code in ActiveCampaign. It uses older version and I can't upgrade it. Can you please let me to work around this. I am doing code in ActiveCampaign App Studio: https://developers.activecampaign.com/docs/configuration-walkthrough-1 – NJ Bhanushali Feb 21 '22 at 18:45
  • I want to get result like this https://jqplay.org/s/yMC7enLLaL – NJ Bhanushali Feb 21 '22 at 18:47
  • 1
    @NJBhanushali It'd be much simpler for us (and more comprehensible for others reading this) if you opened a new question with full details (as you did originally in this one: what is the source, what is the desired output, etc.) – pmf Feb 21 '22 at 18:51
  • I have post new question: https://stackoverflow.com/questions/71211622/how-to-get-object-list-from-key-value-pairs-complex-json-using-jq-and-map-acti – NJ Bhanushali Feb 21 '22 at 18:58
2

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": "roney@domain.com"
  },
  "Approver": {
    "name": "Tim",
    "role": "Approver",
    "email_address": "tim@domain.com"
  },
  "Customer": {
    "name": "Alex",
    "role": "Customer",
    "email_address": "alex@domain.com"
  }
}

Demo

pmf
  • 24,478
  • 2
  • 22
  • 31
  • What changes I should do to get list of objects `[ { "Presenter": { ... } }, { "Approver": { ... } }, { "Customer": { ... } } ]` rather current one `{ { "Presenter": { ... } }, { "Approver": { ... } }, { "Customer": { ... } } }` – NJ Bhanushali Feb 14 '22 at 17:28
  • @NJBhanushali Add `| to_entries | map({(.key): .value})` to this solution. [Demo](https://jqplay.org/s/cHjYa5zPeK) – pmf Feb 14 '22 at 18:11
1
{ "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", "roney@domain.com" ],
   [ "Approver",  "role",          "Approver"         ],
   [ "Approver",  "name",          "Tim"              ],
   [ "Approver",  "role",          "Approver"         ],
   [ "Approver",  "email_address", "tim@domain.com"   ],
   [ "Customer",  "role",          "Customer"         ],
   [ "Customer",  "name",          "Alex"             ],
   [ "Customer",  "role",          "Customer"         ],
   [ "Customer",  "email_address", "alex@domain.com"  ]
]

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 )
ikegami
  • 367,544
  • 15
  • 269
  • 518