0

In Logic Apps I have an API that returns multiple records in the following format:

{
  "meta": {
    "pagination": {
      "recordStart": 0,
      "pageSize": 25,
      "next": "eNoNzttugjAfufhyYTZ0PdTjmtav6pr6sFIFZ39AVovz9A7AsXYQ"
    },
    "status": 200
  },
  "data": [
    {
      "items": [
        {
          "size": 131783,
          "attachmentcount": 3,
          "subject": "Statement - Mr Big Statement in USD",
          "displaytoaddresslist": [],
          "displayfrom": "Microsoft Outlook",
          "displayfromaddress": "email@bob.com",
          "id": "eN_LwK3S1",
          "smash": "4da34cc873f844bc242397",
          "displayto": "people",
          "receiveddate": "2023-03-24T11:48:00+0000",
          "status": "ARCHIVED"
        },
        {
          "size": 122681,
          "attachmentcount": 1,
          "subject": "Statement - Joe Bloggs Statement in USD",
          "etc": "etc"
        }
      ]
    }
  ]
}

My aim is to insert each record returned into a SQL Server DB for archiving. I've tried experimenting with loading records directly into a stored procedure, but my attempts to extract a field directly (i.e. subject, size, etc) fail every time.

Examples I've tried include: @body('Parsed JSON')['items'][0]['subject'] @body('Submit Mimecast API')['data']['items'][0]['size']

Can anyone help please? Starting to think I might need a For Loop to do this, but unsure of the best approach.

Any advice would be very much appreciated.

Thanks

Skin
  • 9,085
  • 2
  • 13
  • 29
KodeNode
  • 45
  • 5
  • There are more than one records in the array and you want them all then yes, you’ll need to loop. I’m confused though, what’s the error cos your second expression looks ok as far as I can see compared to the JSON. Also, do us a favour and provide your JSON as text, not an image. – Skin May 20 '23 at 02:59
  • No problem I've replaced the image with the code text. – KodeNode May 23 '23 at 09:02

1 Answers1

0

After reproducing from my end, I could able to achieve this following the below.

For demonstration purposes, I have used the below sample JSON.

[
{
            "id": 1,
            "email": "george.bluth@reqres.in",
            "first_name": "George",
            "last_name": "Bluth",
            "avatar": "https://reqres.in/img/faces/1-image.jpg"
        },
        {
            "id": 2,
            "email": "janet.weaver@reqres.in",
            "first_name": "Janet",
            "last_name": "Weaver",
            "avatar": "https://reqres.in/img/faces/2-image.jpg"
        },
        {
            "id": 3,
            "email": "emma.wong@reqres.in",
            "first_name": "Emma",
            "last_name": "Wong",
            "avatar": "https://reqres.in/img/faces/3-image.jpg"
        },
        {
            "id": 4,
            "email": "eve.holt@reqres.in",
            "first_name": "Eve",
            "last_name": "Holt",
            "avatar": "https://reqres.in/img/faces/4-image.jpg"
        },
        {
            "id": 5,
            "email": "charles.morris@reqres.in",
            "first_name": "Charles",
            "last_name": "Morris",
            "avatar": "https://reqres.in/img/faces/5-image.jpg"
        },
        {
            "id": 6,
            "email": "tracey.ramos@reqres.in",
            "first_name": "Tracey",
            "last_name": "Ramos",
            "avatar": "https://reqres.in/img/faces/6-image.jpg"
        }
]

Below is the flow of my logic app

enter image description here

enter image description here

Parse JSON Schema of my logic app

{
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "id": {
                "type": "integer"
            },
            "email": {
                "type": "string"
            },
            "first_name": {
                "type": "string"
            },
            "last_name": {
                "type": "string"
            },
            "avatar": {
                "type": "string"
            }
        }
    }
}

Results:

enter image description here

enter image description here

SwethaKandikonda
  • 7,513
  • 2
  • 4
  • 18
  • thank you for this example. I understand the theory but unfortunately my API results format is quite different to your example. Can you share the code used to refer to each of your 'fields' please? – KodeNode May 22 '23 at 16:31