Splitting your lookup to include new fields "President", "VP", "Manager" would work but doesn't really scale if the role field has high cardinality. Here is another approach that is more scalable ...
See more...
Splitting your lookup to include new fields "President", "VP", "Manager" would work but doesn't really scale if the role field has high cardinality. Here is another approach that is more scalable and can be generalized. You could make a net-new field in your lookup named role_json that would contain the mapping info of role<-->name. Edit: Just realized your request was to not have results in the mvexpanded format I first showed. So here is an updated method. <base_search>
| lookup orgchart Org, Branch OUTPUT role_json
| foreach mode=multivalue role_json
[
| eval
tmp_json=if(
isnull(tmp_json),
json_object(spath('<<ITEM>>', "Role"), spath('<<ITEM>>', "Name")),
json_set(tmp_json, spath('<<ITEM>>', "Role"), spath('<<ITEM>>', "Name"))
)
]
``` capture any role_json values that are single values ```
| eval
tmp_json=if(
mvcount(role_json)==1,
json_object(spath(role_json, "Role"), spath(role_json, "Name")),
'tmp_json'
)
``` remove role_json (no longer needed) ```
| fields - role_json
``` parse out tmp_json to table all the proper mappings ```
| spath input=tmp_json
``` remove tmp_json (no longer needed) ```
| fields - tmp_json Directly after the lookup results would look something like this: Then after the foreach loops. I added a few extra entries to lookup to demonstrate that this method is dynamic and doesn't need any hardcoded fieldnames to account for potential new values. To get a new json object field into your existing lookup would look something like this (Provided its a CSV, if it is a kvstore then you would probably need to update the definition to include the new field) | inputlookup orgchart
| tojson str(Role) str(Name) output_field=role_json
| outputlookup orgchart