Format JSON

[jrestebanez]

Starting from the following JSON

[
  {
    "rowId": "rowId_cc4a3a7d-xxxx-xxxx-xxxx-bc22f1212c90",
    "columns": {
      "Quarter": {
        "label": "Q1"
      },
      "resources": "Resource 1",
      "cost": 12.11
    },
    "order": 0
  },
  {
    "rowId": "rowId_e9faaf28-xxxx-xxxx-xxxx-e447130e5c2f",
    "columns": {
      "Quarter": {
        "label": "Q2"
      },
      "resources": "Resource 2",
      "cost": 13.11
    },
    "order": 1
  },
  {
    "rowId": "rowId_33d422d6-xxxx-xxxx-xxxx-23b410ad4eff",
    "columns": {
      "Quarter": {
        "label": "Q1"
      },
      "resources": "Resource 3",
      "cost": 14.11
    },
    "order": 2
  }
]

I would like to get a JSON in which the resulting array only includes the columns and if any column has the label property, the value of the label key should be put in the value.
The final result would be something like this:

[
  {
    "Quarter": "Q1",
    "resources": "Resource 1",
    "cost": 12.11
  },
  {
    "Quarter": "Q2",
    "resources": "Resource 2",
    "cost": 13.11
  },
  {
    "Quarter": "Q1",
    "resources": "Resource 3",
    "cost": 14.11
  }
]

Another complicating factor is that when I make the API call, the existing columns can be called differently and the number of columns can also vary. I managed to solve this through xpath, converting the JSON to XML.

[v-bofeng-msft]

Hi @jrestebanez ,

I've made a test for your reference:

Result:

Best Regards,

Bof

[jrestebanez]

Thank you very much, but it is a bit more complex because of what I said, that the API (JIRA, Atlassian) when I make the query, the names of the columns are different as well as the property "label" can be in different columns, if it were always the same, is what you have done and that I know how to do it.

[jrestebanez · Accepted solution]

I have finally managed to do it and these are the steps I have followed, in case anyone can help. I have defined a few compose but to understand better what I have done

First, I declare 3 variables:

• JsonDummy --> of object type, I use it to be able to add a property to the JSON with the name of the column.
• Rows --> of array type, to store each row and then convert it to a string
• FinalRows --> of array type, with the final result

Then I make a call to my custom connector to get the data from the Atlassian table.

• Data Operation "Compose Columns" with expression: items('Apply_to_each')?['columns']
• Data Operation "Compose Root" with expression: xpath(xml(json(concat('{ "root": ', string(outputs('Columns')), ' }'))
  ), '/root/*') --> With this I convert a JSON Object in XML. I add root at the beginning and take all the elements that hang from root
• Data Operation "Select Columns":
  • From: outputs('Compose_Root')
  • Map: ColumnName I map with expression: xpath(item(), 'name(/*)') --> With this I get the name of the columns

Inside the loop for each row of the table, I add another loop to go through the columns and perform the following operations:

• Data Operation "Compose ColumnName" with expression: items('Apply_to_each_Column')?['ColumnName']
• Condition If expression: string(outputs('Columns')?[outputs('Compose_ColumnName')]) contains text label
  • If true, Append to array Rows value: addProperty(variables('JsonDummy'),outputs('Compose_ColumnName'),outputs('Columns')?[outputs('Compose_ColumnName')]?['label'])
  • If false, Append to array  Rows value: addProperty(variables('JsonDummy'),outputs('Compose_ColumnName'),outputs('Columns')?[outputs('Compose_ColumnName')])

Next, Inside the loop for each row of the table

• Data Operation "Compose Array To String" with expression: string(variables('Rows')) --> I convert array to String, because I want to convert it to a simple JSON without being an array. 
• Data Operation "Compose JSON" with expression: json(replace(replace(replace(replace(outputs('Compose_Array_To_String'),'{',''),'}',''),'[','{'),']','}')) --> I have to replace the characters so that I can convert it into a simple JSON
• Append to array FinalRows value: outputs('Compose_JSON')
• Finally, reset array Rows with value: []