Get all non blank key value pairs from an object

[NilsKirsch]

Hi community,

I am stuck in the middle of a flow. My issue is to retrieve all non blank keys and values of an object.

[

 {

  "Training1": "",

  "Training2": "",

  "Training3": "01.01.2024"

 }

]

The solution should cover that in future new key value pairs could be added to the object. And I want still be able to identify if the new pair is empty. So I somehow have to loop through the key value pairs, but don't know how to do that.

In my simple example I would expect to retrieve the key value pair "Training3": "01.01.2024" as the result.

Background I have a form with available trainings. and the user can pick 1 or many trainings and different dates for each training as an option choice. I want to report to the manager which trainings (key) have been booked to which dates (value).

I hope I could express my issue with these few words.

thx a lot in advance 😉

[ekarim2020]

Hi @NilsKirsch ,

I think you will need to use XML to achieve this: select the keys that contain values. 

Although my knowledge of XML is not extensive, here is a demo flow using the following sample data:

[
  {
    "Training1": "",
    "Training2": "03.20.2024",
    "Training3": "",
    "Training4": "11.12.2023",
    "Training5": "",
    "Training6": "08.05.2023",
    "Training7": "10.10.2023",
    "Training8": "",
    "Training9": "",
    "Training10": "04.18.2024",
    "Training11": "",
    "Training12": "",
    "Training13": "09.28.2023",
    "Training14": "",
    "Training15": ""
  }
]

You can copy-and-paste the following code:

{
  "root": {
    "objectID": [
      @{outputs('Compose_Data')}
    ]
  }
}

 

xml(outputs('Compose'))

This is the XPath expression: selects all elements in the XML "document" that have at least one text node containing non-empty text. The xpath was generated with help from ChatGPT:

xpath(outputs('Compose_XML'),'//*[text()[normalize-space()]]')

Next use the Select action. Switch map to text mode for the Select action by clicking the icon as shown below:

base64toString(item()?['$content'])

Booking: split(split(item(),'>')?[1],'</')?[1]
Value:   split(split(item(),'>')?[1],'</')?[0]

This is the runtime output of my demo flow: 

 

This is the output that was vreated:

 

Hope this helps.

Ellis
____________________________________
If I have answered your question, please mark the post as ☑️ Solved.
If you like my response, please give it a Thumbs Up.
My Blog Site

 

[NilsKirsch]

@ekarim2020 thank you so much for taking the time to describe a solution. I will test that later this week. But it looks promising. If that works I'll mark your reply as the solution.
thanks again
Nils

[NilsKirsch]

Hi @ekarim2020,

you solution works like a charm. There is only one cosmetical thing. When doing the xpath or xml it seems like the space get's escaped by _x0020_ so the same as for sharepoint columns for example. 
so in my case it's "Training_x0020_13". Which is not quite readable and therefore not suitable if I report the booked trainings to Person XY. 
Is there a way of encoding this back to space (other special characters are affected as well; ? = _x003F_)?

Thanks a lot for your help.

[ekarim2020]

Hi @NilsKirsch ,

The simplest solution could be to add a compose action that converts the output of the Select 2 action to a string, then replace each of the encoded characters to their special characters:

json(replace(replace(string(body('Select_2')),'_x0020_',' '),'_x003F_','?'))

However,  you would need to replace each Hex Code with its corresponding Special Characters.

Source data. So this would only be suited a small number of Hex Codes:

[
    {
        "Training 1": "",
        "Training?2": "03.20.2024",
        "Training 3": "",
        "Training 4": "11.12.2023",
        "Training5": "",
        "Training?6": "08.05.2023",
        "Training7": "10.10.2023",
        "Training8": "",
        "Training9": "",
        "Training10": "04.18.2024",
        "Training11": "",
        "Training12": "",
        "Training13": "09.28.2023",
        "Training14": "",
        "Training15": ""
    }
]

[
  "<Training_x003F_2>03.20.2024</Training_x003F_2>",
  "<Training_x0020_4>11.12.2023</Training_x0020_4>",
  "<Training_x003F_6>08.05.2023</Training_x003F_6>",
  "<Training7>10.10.2023</Training7>",
  "<Training10>04.18.2024</Training10>",
  "<Training13>09.28.2023</Training13>"
]

Output:

[
    {
        "Booking": "Training?2",
        "Value": "03.20.2024"
    },
    {
        "Booking": "Training 4",
        "Value": "11.12.2023"
    },
    {
        "Booking": "Training?6",
        "Value": "08.05.2023"
    },
    {
        "Booking": "Training7",
        "Value": "10.10.2023"
    },
    {
        "Booking": "Training10",
        "Value": "04.18.2024"
    },
    {
        "Booking": "Training13",
        "Value": "09.28.2023"
    }
]

 
Ellis
____________________________________
If I have answered your question, please mark the post as ☑️ Solved.
If you like my response, please give it a Thumbs Up.
My Blog Site