Replace Property Name in One Array with Value from a Second Array

[aevans16]

I have two arrays where the property name on one matches a value in the other. I am trying to replace the property name in the first array with a matching value from the second.

The first array is the output of an inspection checklist. Each property is the unique ID of a question and the value is the response to that question. Here is a sample output:

[

  {

    "QB000011": "Yes",

    "QB000012": "Yes",

    "QB000014": "Yes",

    "QB000013": "Yes",

    "QB000016": "No",

    "QB000010": "No",

    "QB000017": "No",

    "QB000015": "Good",

  }

]

The second array is the output of the table containing all of the questions that could possibly be on the inspection checklist. Here is a sample of that array:

[

{

        "Question ID": "QB000010",

        "Question": "Are there any hazards from nearby plant or machinery?"

    },

    {

        "Question ID": "QB000011",

        "Question": "Was site safety orientation provided?"

    },

    {

        "Question ID": "QB000012",

        "Question": "Were emergency procedures reviewed?"

    },

    {

        "Question ID": "QB000013",

        "Question": "Is the location of emergency equipment known?"

    },

    {

        "Question ID": "QB000014",

        "Question": "Do you know how to report an emergency/accident?"

    },

    {

        "Question ID": "QB000015",

        "Question": "Overall site conditions?"

    },

    {

        "Question ID": "QB000016",

        "Question": "Is a site escort required?"

    },

    {

        "Question ID": "QB000017",

        "Question": "Is your PPE inspected and in good condition?"

    },

    {

        "Question ID": "QB000018",

        "Question": "Is the installation area clean of excess dirt and dust?"

    },

    {

        "Question ID": "QB000019",

        "Question": "What is the general condition of the equipment?"

    },

    {

        "Question ID": "QB000020",

        "Question": "Is the equipment suitable for its intended purpose?"

    },

    {

        "Question ID": "QB000022",

        "Question": "Were all shipping materials disposed of properly upon completion of the installation?"

    },

    {

        "Question ID": "QB000023",

        "Question": "Do all doors between the loading dock and the final installation site allow for the passage of the equipment?"

    },

    {

        "Question ID": "QB000024",

        "Question": "Is a dedicated circuit with a grounded outlet required within 6 feet/1.83m of the equipment?"

    },

    {

        "Question ID": "QB000025",

        "Question": "Based on equipment size/diagram, does the site have the necessary space for installation?"

    },

    {

        "Question ID": "QB000026",

        "Question": "Is this a permanent installation?"

    },

    {

        "Question ID": "QB000027",

        "Question": "Will the installation require the removal of existing instrumentation or equipment?"

    },

    {

        "Question ID": "QB000028",

        "Question": "Were all necessary tests/checks completed successfully upon completion of installation?"

    },

    {

        "Question ID": "QB000030",

        "Question": "Check for Software Version. Was an update to latest version required?"

    },

    {

        "Question ID": "QB000031",

        "Question": "Are there other people within the location of where you will be working?"

    },

    {

        "Question ID": "QB000032",

        "Question": "Do you have all of the parts required to complete the installation?"

    },

    {

        "Question ID": "QB000033",

        "Question": "Do you have all of the tools required to complete the installation?"

    },

    {

        "Question ID": "QB000021",

        "Question": "Describe any other identifiable risks"

    },

    {

        "Question ID": "QB000034",

        "Question": "How long did it take to move the equipment from the delivery location to the installation location? (in minutes)"

    }

]

What I need to do is replace the question ID in the first array with the matching question in the second array. For example, instead of the first item in the first array being "QB000011": "Yes" it would be "Was site safety orientation provided?": "Yes" and so on. I have found documentation about combining arrays with shared values, but in this case it is the property of one that matches a value in the other. As a result, I'm having some trouble combining the two with Power Automate. Any help would be greatly appreciated.

[Chriddle]

Are you talking about setting the questions as property names?

I would not walk that dark path 😉

What is your goal?

Maybe we can find a better way.

[aevans16]

Thanks for your reply! The end goal is to produce a document (probably a PDF) that shows the questions and the responses to the questions in a table format. But rather than, for example, the document listing "QB000011" as the question that was answered I would like it to display the actual words from that question. In the case of QB000011 we would want it to read "Was site safety orientation provided?" in the final product. However, the checklist results (the answers to the questions) are maintained in a table where it only shows the question ID and not the actual question itself. So I am trying to "merge" the data. If there is a better/easier way to do that I'm all ears!

[aevans16]

Sorry, realized I replied to the post and not to you (still learning to use this forum):

Thanks for your reply! The end goal is to produce a document (probably a PDF) that shows the questions and the responses to the questions in a table format. But rather than, for example, the document listing "QB000011" as the question that was answered I would like it to display the actual words from that question. In the case of QB000011 we would want it to read "Was site safety orientation provided?" in the final product. However, the checklist results (the answers to the questions) are maintained in a table where it only shows the question ID and not the actual question itself. So I am trying to "merge" the data. If there is a better/easier way to do that I'm all ears!

[ekarim2020]

Hi @aevans16 ,

I would agree with @Chriddle  and not use the question text as the Key.  The following data structure could fit your needs:

[
  {
    "Question": "Are there any hazards from nearby plant or machinery?",
    "Answer": "No"
  },
  {
    "Question": "Was site safety orientation provided?",
    "Answer": "Yes"
  },
  {
    "Question": "Were emergency procedures reviewed?",
    "Answer": "Yes"
  },
  {
    "Question": "Is the location of emergency equipment known?",
    "Answer": "Yes"
  },
  {
    "Question": "Do you know how to report an emergency/accident?",
    "Answer": "Yes"
  },
  {
    "Question": "Overall site conditions?",
    "Answer": "Good"
  },
  {
    "Question": "Is a site escort required?",
    "Answer": "No"
  },
  {
    "Question": "Is your PPE inspected and in good condition?",
    "Answer": "No"
  }
]

Ellis

[aevans16]

Sorry, I should have clarified that I did not structure this data nor do I really have control over how it is structured. This is coming from a third-party platform that we do not manage. I totally agree that I would probably have structured things differently.

The structure that you are suggesting though is exactly the output I need though. Do we think it is possible to manipulate the two separate arrays into that? 

[ekarim2020 · Accepted solution]

It could be done like this:

 

json(slice(string(outputs('Compose_1')),1,-1))

The expression in the Compose action produces the following JSON object:

In the Select action below, we will use the JSON object to lookup the Answer when we provide a Question ID:

 The expressions uses are:

Question: 
item()?['Question']

Answer:
outputs('Compose_Object')?[item()?['Question ID']]

Finally we remove questions with no answers (i.e.  Answers that have null or empty values):

 The expressions for the Filter array action are:

item()?['Answer']

 

The Filter array action produces the following array of JSON objects:

[
  {
    "Question": "Are there any hazards from nearby plant or machinery?",
    "Answer": "No"
  },
  {
    "Question": "Was site safety orientation provided?",
    "Answer": "Yes"
  },
  {
    "Question": "Were emergency procedures reviewed?",
    "Answer": "Yes"
  },
  {
    "Question": "Is the location of emergency equipment known?",
    "Answer": "Yes"
  },
  {
    "Question": "Do you know how to report an emergency/accident?",
    "Answer": "Yes"
  },
  {
    "Question": "Overall site conditions?",
    "Answer": "Good"
  },
  {
    "Question": "Is a site escort required?",
    "Answer": "No"
  },
  {
    "Question": "Is your PPE inspected and in good condition?",
    "Answer": "No"
  }
]

 
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

[aevans16]

Success! Thank you so much!

[Chriddle]

Why are you using this "hack"

json(slice(string(outputs('Compose_1')),1,-1))

when you (probably) want to do this 

first(outputs('Compose_1'))

Is it just a holdover from the days before functions like first() existed, or is there another purpose behind it?

[ekarim2020]

Hi @Chriddle ,

Yes, I agree that's an easier and better way to do it.

Many times I use string manipulation to change data, may be I've become a bot? 😉

Ellis