cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sam_sonepar
Helper III
Helper III

Convert JSON Object to Array

Hi everyone,

 

I have a custom connector that grabs the exchange rate for a bunch of currencies and returns them in a JSON object. I'm trying to work with the values in an array so I can loop them in an Apply to Each action later.

 

End Goal

I have a SharePoint list that contains about 30 symbols and I want to update the associated rate.

My plan is to add each symbol that I need in an array and then use the filter array function to compare what I have in SharePoint and what is coming from my custom connector.

The only thing stopping me is that I can't use any array function because the body of my custom connector is considered an object.

 

This is the body I get from my custom connector:

 

{
  "result": "success",
  "documentation": "https://www.exchangerate-api.com/docs",
  "terms_of_use": "https://www.exchangerate-api.com/terms",
  "time_last_update_unix": 1619395201,
  "time_last_update_utc": "Mon, 26 Apr 2021 00:00:01 +0000",
  "time_next_update_unix": 1619481601,
  "time_next_update_utc": "Tue, 27 Apr 2021 00:00:01 +0000",
  "base_code": "USD",
  "conversion_rates": {
    "USD": 1,
    "AED": 3.6725,
    "AFN": 77.5194,
    "ALL": 102.2704,
    "AMD": 521.89,
    "ANG": 1.79,
    "AOA": 655.0129,
    "ARS": 93.027,
    "AUD": 1.2918,
    "AWG": 1.79,
    "AZN": 1.6975,
    "BAM": 1.6192,
    "BBD": 2,
    "BDT": 84.7524,
    "BGN": 1.6193,
    "BHD": 0.376,
    "BIF": 1948.7557,
    "BMD": 1,
    "BND": 1.3267,
    "BOB": 6.8889,
    "BRL": 5.4432,
    "BSD": 1,
    "BTN": 75.007,
    "BWP": 10.8197,
    "BYN": 2.5643,
    "BZD": 2,
    "CAD": 1.2479,
    "CDF": 1980.3907,
    "CHF": 0.9144,
    "CLP": 706.2131,
    "CNY": 6.492,
    "COP": 3639.8265,
    "CRC": 613.488,
    "CUC": 1,
    "CUP": 25.75,
    "CVE": 91.286,
    "CZK": 21.4481,
    "DJF": 177.721,
    "DKK": 6.1763,
    "DOP": 56.8983,
    "DZD": 132.7845,
    "EGP": 15.6804,
    "ERN": 15,
    "ETB": 41.9764,
    "EUR": 0.8278,
    "FJD": 2.0254,
    "FKP": 0.7209,
    "FOK": 6.1763,
    "GBP": 0.7212,
    "GEL": 3.4494,
    "GGP": 0.7209,
    "GHS": 5.7774,
    "GIP": 0.7209,
    "GMD": 51.9242,
    "GNF": 9948.7342,
    "GTQ": 7.704,
    "GYD": 213.3475,
    "HKD": 7.7604,
    "HNL": 24.0038,
    "HRK": 6.2376,
    "HTG": 85.2293,
    "HUF": 302.2035,
    "IDR": 14531.1212,
    "ILS": 3.2768,
    "IMP": 0.7209,
    "INR": 75.0078,
    "IQD": 1457.8722,
    "IRR": 41990.8421,
    "ISK": 125.39,
    "JMD": 150.5305,
    "JOD": 0.709,
    "JPY": 107.9281,
    "KES": 108.4943,
    "KGS": 84.8602,
    "KHR": 4045.3231,
    "KID": 1.2918,
    "KMF": 407.2894,
    "KRW": 1116.6055,
    "KWD": 0.2996,
    "KYD": 0.8333,
    "KZT": 430.1221,
    "LAK": 9407.3377,
    "LBP": 1507.5,
    "LKR": 193.2854,
    "LRD": 172.3247,
    "LSL": 14.2712,
    "LYD": 4.4773,
    "MAD": 8.8973,
    "MDL": 17.9533,
    "MGA": 3776.1948,
    "MKD": 51.054,
    "MMK": 1408.327,
    "MNT": 2847.8345,
    "MOP": 7.9944,
    "MRU": 35.9286,
    "MUR": 40.547,
    "MVR": 15.3474,
    "MWK": 790.498,
    "MXN": 19.876,
    "MYR": 4.1081,
    "MZN": 55.4652,
    "NAD": 14.2712,
    "NGN": 384.6131,
    "NIO": 35.0631,
    "NOK": 8.3139,
    "NPR": 120.0112,
    "NZD": 1.391,
    "OMR": 0.3845,
    "PAB": 1,
    "PEN": 3.7563,
    "PGK": 3.5059,
    "PHP": 48.3361,
    "PKR": 153.5206,
    "PLN": 3.7751,
    "PYG": 6440.4771,
    "QAR": 3.64,
    "RON": 4.0687,
    "RSD": 97.2763,
    "RUB": 75.1012,
    "RWF": 999.4429,
    "SAR": 3.75,
    "SBD": 7.9349,
    "SCR": 13.7978,
    "SDG": 379.0468,
    "SEK": 8.397,
    "SGD": 1.3257,
    "SHP": 0.7209,
    "SLL": 10216.8942,
    "SOS": 578.0796,
    "SRD": 14.1435,
    "SSP": 177.5622,
    "STN": 20.283,
    "SYP": 1258.2349,
    "SZL": 14.2712,
    "THB": 31.4809,
    "TJS": 11.3086,
    "TMT": 3.5,
    "TND": 2.7217,
    "TOP": 2.2605,
    "TRY": 8.3758,
    "TTD": 6.7883,
    "TVD": 1.2918,
    "TWD": 28.0382,
    "TZS": 2316.0685,
    "UAH": 27.9008,
    "UGX": 3599.065,
    "UYU": 44.1317,
    "UZS": 10437.8,
    "VES": 2647851.66,
    "VND": 23013.5229,
    "VUV": 108.6391,
    "WST": 2.5083,
    "XAF": 543.0525,
    "XCD": 2.7,
    "XDR": 0.6965,
    "XOF": 543.0525,
    "XPF": 98.7924,
    "YER": 250.1876,
    "ZAR": 14.2648,
    "ZMW": 22.2479
  }
}

 

 

This is the schema I use to parse the body:

 

{
  "type": "object",
  "properties": {
    "result": {
      "type": "string"
    },
    "documentation": {
      "type": "string"
    },
    "terms_of_use": {
      "type": "string"
    },
    "time_last_update_unix": {
      "type": "integer"
    },
    "time_last_update_utc": {
      "type": "string"
    },
    "time_next_update_unix": {
      "type": "integer"
    },
    "time_next_update_utc": {
      "type": "string"
    },
    "base_code": {
      "type": "string"
    },
    "conversion_rates": {
      "type": "object",
      "properties": {
        "USD": {
          "type": "integer"
        },
        "AED": {
          "type": "number"
        },
        "AFN": {
          "type": "number"
        },
        "ALL": {
          "type": "number"
        },
        "AMD": {
          "type": "number"
        },
        "ANG": {
          "type": "number"
        },
        "AOA": {
          "type": "number"
        },
        "ARS": {
          "type": "number"
        },
        "AUD": {
          "type": "number"
        },
        "AWG": {
          "type": "number"
        },
        "AZN": {
          "type": "number"
        },
        "BAM": {
          "type": "number"
        },
        "BBD": {
          "type": "integer"
        },
        "BDT": {
          "type": "number"
        },
        "BGN": {
          "type": "number"
        },
        "BHD": {
          "type": "number"
        },
        "BIF": {
          "type": "number"
        },
        "BMD": {
          "type": "integer"
        },
        "BND": {
          "type": "number"
        },
        "BOB": {
          "type": "number"
        },
        "BRL": {
          "type": "number"
        },
        "BSD": {
          "type": "integer"
        },
        "BTN": {
          "type": "number"
        },
        "BWP": {
          "type": "number"
        },
        "BYN": {
          "type": "number"
        },
        "BZD": {
          "type": "integer"
        },
        "CAD": {
          "type": "number"
        },
        "CDF": {
          "type": "number"
        },
        "CHF": {
          "type": "number"
        },
        "CLP": {
          "type": "number"
        },
        "CNY": {
          "type": "number"
        },
        "COP": {
          "type": "number"
        },
        "CRC": {
          "type": "number"
        },
        "CUC": {
          "type": "integer"
        },
        "CUP": {
          "type": "number"
        },
        "CVE": {
          "type": "number"
        },
        "CZK": {
          "type": "number"
        },
        "DJF": {
          "type": "number"
        },
        "DKK": {
          "type": "number"
        },
        "DOP": {
          "type": "number"
        },
        "DZD": {
          "type": "number"
        },
        "EGP": {
          "type": "number"
        },
        "ERN": {
          "type": "integer"
        },
        "ETB": {
          "type": "number"
        },
        "EUR": {
          "type": "number"
        },
        "FJD": {
          "type": "number"
        },
        "FKP": {
          "type": "number"
        },
        "FOK": {
          "type": "number"
        },
        "GBP": {
          "type": "number"
        },
        "GEL": {
          "type": "number"
        },
        "GGP": {
          "type": "number"
        },
        "GHS": {
          "type": "number"
        },
        "GIP": {
          "type": "number"
        },
        "GMD": {
          "type": "number"
        },
        "GNF": {
          "type": "number"
        },
        "GTQ": {
          "type": "number"
        },
        "GYD": {
          "type": "number"
        },
        "HKD": {
          "type": "number"
        },
        "HNL": {
          "type": "number"
        },
        "HRK": {
          "type": "number"
        },
        "HTG": {
          "type": "number"
        },
        "HUF": {
          "type": "number"
        },
        "IDR": {
          "type": "number"
        },
        "ILS": {
          "type": "number"
        },
        "IMP": {
          "type": "number"
        },
        "INR": {
          "type": "number"
        },
        "IQD": {
          "type": "number"
        },
        "IRR": {
          "type": "number"
        },
        "ISK": {
          "type": "number"
        },
        "JMD": {
          "type": "number"
        },
        "JOD": {
          "type": "number"
        },
        "JPY": {
          "type": "number"
        },
        "KES": {
          "type": "number"
        },
        "KGS": {
          "type": "number"
        },
        "KHR": {
          "type": "number"
        },
        "KID": {
          "type": "number"
        },
        "KMF": {
          "type": "number"
        },
        "KRW": {
          "type": "number"
        },
        "KWD": {
          "type": "number"
        },
        "KYD": {
          "type": "number"
        },
        "KZT": {
          "type": "number"
        },
        "LAK": {
          "type": "number"
        },
        "LBP": {
          "type": "number"
        },
        "LKR": {
          "type": "number"
        },
        "LRD": {
          "type": "number"
        },
        "LSL": {
          "type": "number"
        },
        "LYD": {
          "type": "number"
        },
        "MAD": {
          "type": "number"
        },
        "MDL": {
          "type": "number"
        },
        "MGA": {
          "type": "number"
        },
        "MKD": {
          "type": "number"
        },
        "MMK": {
          "type": "number"
        },
        "MNT": {
          "type": "number"
        },
        "MOP": {
          "type": "number"
        },
        "MRU": {
          "type": "number"
        },
        "MUR": {
          "type": "number"
        },
        "MVR": {
          "type": "number"
        },
        "MWK": {
          "type": "number"
        },
        "MXN": {
          "type": "number"
        },
        "MYR": {
          "type": "number"
        },
        "MZN": {
          "type": "number"
        },
        "NAD": {
          "type": "number"
        },
        "NGN": {
          "type": "number"
        },
        "NIO": {
          "type": "number"
        },
        "NOK": {
          "type": "number"
        },
        "NPR": {
          "type": "number"
        },
        "NZD": {
          "type": "number"
        },
        "OMR": {
          "type": "number"
        },
        "PAB": {
          "type": "integer"
        },
        "PEN": {
          "type": "number"
        },
        "PGK": {
          "type": "number"
        },
        "PHP": {
          "type": "number"
        },
        "PKR": {
          "type": "number"
        },
        "PLN": {
          "type": "number"
        },
        "PYG": {
          "type": "number"
        },
        "QAR": {
          "type": "number"
        },
        "RON": {
          "type": "number"
        },
        "RSD": {
          "type": "number"
        },
        "RUB": {
          "type": "number"
        },
        "RWF": {
          "type": "number"
        },
        "SAR": {
          "type": "number"
        },
        "SBD": {
          "type": "number"
        },
        "SCR": {
          "type": "number"
        },
        "SDG": {
          "type": "number"
        },
        "SEK": {
          "type": "number"
        },
        "SGD": {
          "type": "number"
        },
        "SHP": {
          "type": "number"
        },
        "SLL": {
          "type": "number"
        },
        "SOS": {
          "type": "number"
        },
        "SRD": {
          "type": "number"
        },
        "SSP": {
          "type": "number"
        },
        "STN": {
          "type": "number"
        },
        "SYP": {
          "type": "number"
        },
        "SZL": {
          "type": "number"
        },
        "THB": {
          "type": "number"
        },
        "TJS": {
          "type": "number"
        },
        "TMT": {
          "type": "number"
        },
        "TND": {
          "type": "number"
        },
        "TOP": {
          "type": "number"
        },
        "TRY": {
          "type": "number"
        },
        "TTD": {
          "type": "number"
        },
        "TVD": {
          "type": "number"
        },
        "TWD": {
          "type": "number"
        },
        "TZS": {
          "type": "number"
        },
        "UAH": {
          "type": "number"
        },
        "UGX": {
          "type": "number"
        },
        "UYU": {
          "type": "number"
        },
        "UZS": {
          "type": "number"
        },
        "VES": {
          "type": "number"
        },
        "VND": {
          "type": "number"
        },
        "VUV": {
          "type": "number"
        },
        "WST": {
          "type": "number"
        },
        "XAF": {
          "type": "number"
        },
        "XCD": {
          "type": "number"
        },
        "XDR": {
          "type": "number"
        },
        "XOF": {
          "type": "number"
        },
        "XPF": {
          "type": "number"
        },
        "YER": {
          "type": "number"
        },
        "ZAR": {
          "type": "number"
        },
        "ZMW": {
          "type": "number"
        }
      }
    }
  }
}

 

 

Parameters from the Parse JSON

sam_sonepar_0-1619495904835.png

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
fchopo
Super User
Super User

Hi @sam_sonepar 

I've done a test on my side as the following:

split.png

 

1) Initialize an array variable called Currencies.

 

2) Get the "conversion_rates" element : variables('customConnectorResult')?['conversion_rates']

3) Split the conversion rates elements by ",".: split(string(outputs('compose')),',')

4) For each element.

  4.1.) Get the currency and its value (have to remove some special characters):

Currency: replace(replace(replace(split(item(),':')[0],'"',''),'{',''),'}','')

Value: split(item(),':')[1]

  4.2) Append this element to the currencies array

5) Filter the array by the wanted currency (EUR in the example)

@equals(item()?['currency'], 'EUR')

 

Hope it helps!

Ferran

Did I answer your question? Please consider to mark my post as a solution to help others.
Proud to be a Flownaut!

View solution in original post

1 REPLY 1
fchopo
Super User
Super User

Hi @sam_sonepar 

I've done a test on my side as the following:

split.png

 

1) Initialize an array variable called Currencies.

 

2) Get the "conversion_rates" element : variables('customConnectorResult')?['conversion_rates']

3) Split the conversion rates elements by ",".: split(string(outputs('compose')),',')

4) For each element.

  4.1.) Get the currency and its value (have to remove some special characters):

Currency: replace(replace(replace(split(item(),':')[0],'"',''),'{',''),'}','')

Value: split(item(),':')[1]

  4.2) Append this element to the currencies array

5) Filter the array by the wanted currency (EUR in the example)

@equals(item()?['currency'], 'EUR')

 

Hope it helps!

Ferran

Did I answer your question? Please consider to mark my post as a solution to help others.
Proud to be a Flownaut!

View solution in original post

Helpful resources

Announcements
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Users online (1,094)