Parsing JSON in Azure LogicApps

Question

I am trying to parse some sales data in Azure LogicApps using the Parse JSON functionality. Here is a JSON object that I am working with...

{
  "12345": {
    "2020-09-03": {
      "date": "2016-11-24",
      "country": null,
      "iso": null,
      "product_id": 12345,
      "downloads": 11,
      "re_downloads": 10,
      "net_downloads": 11,
      "updates": 0,
      "revenue": "0.00",
      "gross_revenue": "0.00",
      "returns_amount": "0",
      "returns": 0,
      "gift_redemptions": 0,
      "promos": 0,
      "edu_revenue": "0.00"
      "gross_returns_amount": "0.00",
      "gross_edu_revenue": "0.00",
      "uninstalls": 0
    }
  },
  "123456": {
    "2020-09-03": {
      "date": "2016-11-24",
      "product_id": 123456,
      "downloads": 28,
      "re_downloads": 30,
      "net_downloads": 29,
      "updates": 6,
      "revenue": "19.02",
      "revenue": "27.20",
      "returns_amount": "0",
      "returns": 1,
      "gift_redemptions": 0,
      "promos": 0,
      "edu_revenue": "0.00"
      "gross_returns_amount": "0.00",
      "gross_edu_revenue": "0.00",
      "uninstalls": 2
    },
  "1234567": {
    "2020-09-03": {
      "date": "2016-11-24",
      "product_id": 1234567,
      "downloads": 28,
      "re_downloads": 30,
      "net_downloads": 29,
      "updates": 6,
      "revenue": "19.02",
      "revenue": "27.20",
      "returns_amount": "0",
      "returns": 1,
      "gift_redemptions": 0,
      "promos": 0,
      "edu_revenue": "0.00"
      "gross_returns_amount": "0.00",
      "gross_edu_revenue": "0.00",
      "uninstalls": 2
    }
  }
}

Before working with this in Azure, I need to create a JSON Schema, then I will be able to loop through the Products (12345, 123456, 1234567) using a ForEach. Within this I will be able to loop through the dates, and finally extract the sales data as required.

Using the in-build schema generator, and several other JSON schema generators, they don't seem to work as expected because this is more of a static data payload, where I was expecting an array of products, and an array of dates.

Any ideas?

Hi, if the solution provided below helps your problem, please accept it as answer(click on the check mark beside my answer to toggle it from greyed out to filled in). Thanks in advance~ — Hury Shen
– Hury Shen, Commented Mar 5, 2021 at 2:35

Hury Shen · Accepted Answer · 2021-03-04 03:11:17Z

First, the json sample you provided in your question is not a valid json (, is missing in several places and also missing } in some places). So if you use this json data to generate the schema, it can not generate schema success and it will show error message.

I modify the json sample as below (if below json data doesn't match your json sample, please modify your post and provide an valid sample).

{
    "12345": {
        "2020-09-03": {
            "date": "2016-11-24",
            "country": null,
            "iso": null,
            "product_id": 12345,
            "downloads": 11,
            "re_downloads": 10,
            "net_downloads": 11,
            "updates": 0,
            "revenue": "0.00",
            "gross_revenue": "0.00",
            "returns_amount": "0",
            "returns": 0,
            "gift_redemptions": 0,
            "promos": 0,
            "edu_revenue": "0.00",
            "gross_returns_amount": "0.00",
            "gross_edu_revenue": "0.00",
            "uninstalls": 0
        }
    },
    "123456": {
        "2020-09-03": {
            "date": "2016-11-24",
            "product_id": 123456,
            "downloads": 28,
            "re_downloads": 30,
            "net_downloads": 29,
            "updates": 6,
            "revenue": "19.02",
            "revenue": "27.20",
            "returns_amount": "0",
            "returns": 1,
            "gift_redemptions": 0,
            "promos": 0,
            "edu_revenue": "0.00",
            "gross_returns_amount": "0.00",
            "gross_edu_revenue": "0.00",
            "uninstalls": 2
        }
    },
    "1234567": {
        "2020-09-03": {
            "date": "2016-11-24",
            "product_id": 1234567,
            "downloads": 28,
            "re_downloads": 30,
            "net_downloads": 29,
            "updates": 6,
            "revenue": "19.02",
            "revenue": "27.20",
            "returns_amount": "0",
            "returns": 1,
            "gift_redemptions": 0,
            "promos": 0,
            "edu_revenue": "0.00",
            "gross_returns_amount": "0.00",
            "gross_edu_revenue": "0.00",
            "uninstalls": 2
        }
    }
}

Second, as you mentioned your requirement is to use "For each" to loop the the Products (12345, 123456, 1234567). But your json data is object but not array, so you can't loop it even if generate the schema success. You json data should be like below format, then you can loop it:

[
    {
        "12345": {
            "2020-09-03": {
                "date": "2016-11-24",
                "country": null,
                "iso": null,
                "product_id": 12345,
                "downloads": 11,
                "re_downloads": 10,
                "net_downloads": 11,
                "updates": 0,
                "revenue": "0.00",
                "gross_revenue": "0.00",
                "returns_amount": "0",
                "returns": 0,
                "gift_redemptions": 0,
                "promos": 0,
                "edu_revenue": "0.00",
                "gross_returns_amount": "0.00",
                "gross_edu_revenue": "0.00",
                "uninstalls": 0
            }
        }
    },
    {
        "123456": {
            "2020-09-03": {
                "date": "2016-11-24",
                "product_id": 123456,
                "downloads": 28,
                "re_downloads": 30,
                "net_downloads": 29,
                "updates": 6,
                "revenue": "19.02",
                "revenue": "27.20",
                "returns_amount": "0",
                "returns": 1,
                "gift_redemptions": 0,
                "promos": 0,
                "edu_revenue": "0.00",
                "gross_returns_amount": "0.00",
                "gross_edu_revenue": "0.00",
                "uninstalls": 2
            }
        }
    },
    {
        "1234567": {
            "2020-09-03": {
                "date": "2016-11-24",
                "product_id": 1234567,
                "downloads": 28,
                "re_downloads": 30,
                "net_downloads": 29,
                "updates": 6,
                "revenue": "19.02",
                "revenue": "27.20",
                "returns_amount": "0",
                "returns": 1,
                "gift_redemptions": 0,
                "promos": 0,
                "edu_revenue": "0.00",
                "gross_returns_amount": "0.00",
                "gross_edu_revenue": "0.00",
                "uninstalls": 2
            }
        }
    }
]

So please check your json data and do some modification.

Collectives™ on Stack Overflow

Parsing JSON in Azure LogicApps

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related