Search code examples
jsonazureazure-logic-apps

In azure logic app extract urls from nested json string

I would like to create an azure logic app which saves images in sharepoint. The input file is a json string, where the url of images are nested inside. I have tried to imitate my json input string down below. The story behind it is that Tim Testmann has to take notes about a tree within an app. There are many fields which he has to fill in such as "Person", "Timestamp", and many fields where he can decide how many pictures he takes. Every image objectname, e.g. "Fotos_Apppletree", has the word "foto" or "photo" inside. Every image object contains an foto description (always in the form Photo_description_) and another object,e.g. "_1_Foto_Appletree" which contains the foto information. Every Foto object has the same properties fileId, fileName, timestamp, size and url. The input strings starts with some general properties such as "comment". The image info is nested with in parent property "data".

{
  "comment": null,
  "data": {
    "id": "123",
    "Person": {
      "Name": "Testmann",
      "Firstname": "Tim"
    },
    "Timestamp": "2023-03-22T20:16:04.663252+01:00",
    "Fotos_Apppletree": [
      {
        "photo_description_1": null,
        "_1_Foto_Appletree": {
          "fileId": "10",
          "fileName": "Appletree_2023_03_22_19_16_09.png",
          "timestamp": "2023-03-22T19:16:09.082243Z",
          "size": 63865,
          "url": "https://path10"
        }
      },
      {
        "photo_description_2": null,
        "_1_Foto_Appletree": {
          "fileId": "11",
          "fileName": "Appletree_2023_03_22_19_16_15.png",
          "timestamp": "2023-03-22T19:16:15.082243Z",
          "size": 63865,
          "url": "https://path11"
        }
      }
    ],
    "Photos_roots": {
      "photo_description_3": null,
      "Photo_roots": {
        "fileId": "20",
        "fileName": "Roots_2023_03_22_19_16_09.png",
        "timestamp": "2023-03-22T19:16:09.082243Z",
        "size": 63865,
        "url": "https://path20"
      }
    },
    "Fotos_leaves": {
      "photo_description_4": null,
      "_1_Foto_leaves": {
        "fileId": "30",
        "fileName": "Leaves_2023_03_22_19_16_09.png",
        "timestamp": "2023-03-22T19:16:09.082243Z",
        "size": 63865,
        "url": "https://path30"
      }
    }
  }
}

My approach is the following: I first want to enter the "data" property with a condition, then loop over all objects containing foto or photo in their name, then looping again over all these object with the condition does not contain "photo_description" to get the objects containing the image url. I am stuck as I don't know what to enter in the yellow highlighted parts. As this is quite a specific question I have not found much help while researching. Does anyone know what to add in the yellow parts? Or are there other/better approaches to tackle the problem? I would highly appreciate any help. I already apologize for how specific the problem is and that some things are likely to be unclear. Thanks a lot in advance!

enter image description here

Edit:

Using JSON to table, how can I loop over every object? What to fill in yellow parts? enter image description here

Edit 2:

I get an error when I want to save the logic app. Something seems to be wrong with the items()['fileName'] input at create file.

enter image description here

enter image description here

enter image description here

Solution

  • If you're using the Advanced Data Operations connector, I think you should look at using the Json to Table operation. Here's an example ...

    Json to Table

    In there, if you throw a filter into the call to make sure fileName is not null, it'll help you to zero in on the data you're after.

    This is the output ...

    [
  {
    "id": "123",
    "Timestamp": "2023-03-22T19:16:04",
    "url": "https://path10",
    "size": 63865,
    "fileName": "Tree_2023_03_22_19_16_09.png",
    "fileId": "10",
    "Name": null,
    "Firstname": null
  },
  {
    "id": "123",
    "Timestamp": "2023-03-22T19:16:04",
    "url": "https://path11",
    "size": 63865,
    "fileName": "Tree_2023_03_22_19_16_15.png",
    "fileId": "11",
    "Name": null,
    "Firstname": null
  },
  {
    "id": "123",
    "Timestamp": "2023-03-22T19:16:04",
    "url": "https://path30",
    "size": 63865,
    "fileName": "Tree3_2023_03_22_19_16_09.png",
    "fileId": "30",
    "Name": null,
    "Firstname": null
  },
  {
    "id": "123",
    "Timestamp": "2023-03-22T19:16:04",
    "url": "https://path20",
    "size": 63865,
    "fileName": "Appletree_2023_03_22_19_16_09.png",
    "fileId": "20",
    "Name": null,
    "Firstname": null
  }
]

    From there, you can loop over each file and do what you need.