Forum Discussion

benrizz17's avatar
benrizz17
Qrew Member
6 months ago
Solved

JSON Array Iteration

I have a pipeline set up to Fetch JSON from a third party application and iterate over JSON records. It returns the results without an issue, however the main data I want to extract from the results is an array that is nested within that results object. My main goal is to iterate through that nested array and create a record for each item in that array. Is that possible?

I have tried a few different things without any luck. I don't have a ton of experience with for loops using Webhooks, but that seemed to be the most likely way to do this so I have tried using that without any luck.

I can post an example code if necessary. Any help would be greatly appreciated!

api
  • JenniferMarque1's avatar
    JenniferMarque1
    6 months ago

    It looks like your use case is a little different than mine.  I actually receive the Customer IDs (or Part IDs in your case) two different ways in the data from my other application.  I get it like you do (I had left this part out of my original schema sample I showed previously just due to length):

    ...
    "DateModified": "2025-06-12T16:19:02-05:00",
    "CustomerIDs": [
        405
    ],
    "SiteIDs": [
        778
    ],
    "Customers": [
        {
            "ID": 405,
            "CompanyName": "CompanyA",
            "GivenName": "",
            "FamilyName": ""
        }
    ],
...

    So I get "CustomerIDs" in an array, but I also get additional data (including the ID) in the "Customers" array.

    If I recall, I had difficulty pulling data from the CustomerIDs array which is why I started using the Customers array.  The only info I'm pulling is the ID, so if I could have gotten CustomerIDs to work I would have used that.  I know when I look at what's available to me for that step in the Quick Reference Widget, CustomerIDs has the icon (and different color) that I assume indicates it's an array. However, it doesn't expand like the Customers array does.  I assume it has something to do with that but don't know exactly what.

    So unfortunately I don't know if I can be much more help.  Maybe someone else will have some additional information

7 Replies

  • JenniferMarque1's avatar
    JenniferMarque1
    Qrew Trainee
    6 months ago

    So I am far from an expert in this (a beginner really), but I have a method which I believe works (been a while since I used/tested since it's in an app not currently in production). As I recall, it does in fact work.  Essentially you have to iterate over the nested array in a second Iterate over JSON loop inside the "main" Iterate over JSON loop.  The difference is that for the sub-loop you have to provide the JSON Records Path (in the main loop it is left blank).  Here is a sample from my pipeline. In this case I'm pulling in Contacts data. Each contact can be associated with multiple Customers (unusual but possible in our 3rd party app).

    "Main" JSON Schema Sample - in an Iterate over JSON Loop step
    JSON Records Path left blank
    {
        "ID": 2202,
        "Title": "",
        "GivenName": "John",
        "FamilyName": "Doe",
        "Email": "[email protected]",
        "DateModified": "2025-06-12T16:19:02-05:00",
        "Customers": [
            {
                "ID": 405,
                "CompanyName": "CompanyA",
                "GivenName": "",
                "FamilyName": ""
            }
            {
                "ID": 406,
                "CompanyName": "CompanyB",
                "GivenName": "",
                "FamilyName": ""
            }

        ],
    }


    "Sub" JSON Schema Sample - a secondary Iterate over JSON loop inside the main loop
    JSON Records Path = /Customers
    {
        "ID": 405,
        "CompanyName": "CompanyA",
        "GivenName": "",
        "FamilyName": ""
    }

    Again, the key is the JSON Records Path; just set it to whatever the array is called. I believe I was pointed towards this solution from a Pipelines Qrew meetup.  It wasn't explicitly about this scenario, but I noticed in one of the examples that the JSON Records Path was set to similar as above.  When I was working on this, it sparked a memory.

    There may be better/other ways to do this, but maybe this will help.

  • benrizz17's avatar
    benrizz17
    Qrew Member
    6 months ago

    Thank you for the reply! I tried this method without any luck, however my lack of expertise in this realm could be contributing to that. 
    Here is an example snip of the JSON result from the 3rd party application:

    { "success": true, "result": { "id": "XXXXX", "title": "Test", "description": "Test", "priority": 1, "images": [], "asset": "XXXXX", "location": "XXXXX", "workOrderNo": "12345", "parts": [ "Part ID 1", "Part ID 2", "Part ID 3" ], "respectivePartQuantityUsed": [ 1, 2, 3 ] }}

    This is the schema example I gave:

    {
"id": "",
"title": "",
"workOrderNo": "",
"parts": [  "" ],
"respectivePartQuantityUsed": [  "" ],
}

    I have tried inputting JSON Records paths of "/result/parts" and "/parts", however keep getting errors that the record path is not found. 

    Please let me know if you see anything that may help me out. It would be so greatly appreciated if I could get this figured out! Thank you again!

    • JenniferMarque1's avatar
      JenniferMarque1
      Qrew Trainee
      6 months ago

      It looks like your use case is a little different than mine.  I actually receive the Customer IDs (or Part IDs in your case) two different ways in the data from my other application.  I get it like you do (I had left this part out of my original schema sample I showed previously just due to length):

      ...
    "DateModified": "2025-06-12T16:19:02-05:00",
    "CustomerIDs": [
        405
    ],
    "SiteIDs": [
        778
    ],
    "Customers": [
        {
            "ID": 405,
            "CompanyName": "CompanyA",
            "GivenName": "",
            "FamilyName": ""
        }
    ],
...

      So I get "CustomerIDs" in an array, but I also get additional data (including the ID) in the "Customers" array.

      If I recall, I had difficulty pulling data from the CustomerIDs array which is why I started using the Customers array.  The only info I'm pulling is the ID, so if I could have gotten CustomerIDs to work I would have used that.  I know when I look at what's available to me for that step in the Quick Reference Widget, CustomerIDs has the icon (and different color) that I assume indicates it's an array. However, it doesn't expand like the Customers array does.  I assume it has something to do with that but don't know exactly what.

      So unfortunately I don't know if I can be much more help.  Maybe someone else will have some additional information

      • benrizz17's avatar
        benrizz17
        Qrew Member
        6 months ago

        I see the difference here. I think what this boils down to is if it is possible to convert an array of values into a more usable format, such as creating a record for each entry. I was able to make it work with an array of objects, but this is an array of purely values.

        Thank you for trying!

  • GeorgiPeev's avatar
    GeorgiPeev
    Quickbase Staff
    4 months ago

    Hey folks, I published a blogpost yesterday on dealing with nested arrays/lists. Would be very grateful if you glance over it and check if I'm missing some corner cases.

    Thank you!