Forum Discussion

JamesCarlos's avatar
JamesCarlos
Qrew Member
3 years ago

Pipeline Iterate over JSON nested data

I was pulling in JSON data from Ground Control and was having some trouble pulling in some nested data while creating a record. Here is how I solved it. I hope I can save some people time.

Used the method posted here to get this started: JSON Handler details

 
Data initially pulled was not an issue at the top level.

Nested inside my data is "customFieldValues". 
Sample: 

        "customFieldValues": [
            {
                "name": "SomeFieldA",
                "value": "Abcdefg"
            },
            {
                "name": "SomeFieldB",
                "value": "1234567"
            },
            {
                "name": "SomeFieldC",
                "value": "CwhatIDidThere?"
            }
]


To pull in this value I needed to use a raw_record Jinja expression and state the location in the array.

{{b.raw_record['customFieldValues'][0]['value']}} 

0 is used since this is the first location in the Array.
(This assumes that b. is the reference used for the other fields such as {{b.status}} )
This is placed in the field reference in Create Record step for SomeFieldA.






------------------------------
James Carlos
------------------------------
Pipelines

3 Replies

  • PrashantMaheshw's avatar
    PrashantMaheshw
    Qrew Captain
    3 years ago
    HI James, 

    Thanks for posting this , I was actually struggling with JSON yesterday . I had to give up .

    Can you post more screenshots (specially the iteration of JSON records part) or post YAML of your pipeline (without sensitive parts)

    ------------------------------
    Prashant Maheshwari
    ------------------------------
    • JamesCarlos's avatar
      JamesCarlos
      Qrew Member
      3 years ago

      First, I use postman to confirm I am getting the data.

      For the "Iterate over JSON records" step, copy a record that is returned by Postman (or other tool) and paste that in to the JSON Schema Sample. Make sure it is only one record and include the {}.


      Next I put in a low limit number so I am not using the whole data set for testing and I set some filters. Model and Status were used in this case. 

      This stumped me the first time since I thought it already looped through the records but in only creates a JSON object for the next step. You will now need to add the For each step and put Create Record under Do to complete the record insert to your Quickbase table. 

      In Create Record I created a token to access the App and table from the Pipeline so that token is referenced in the Account section. Select the Table in the App you want to update and you can then add the table fields to update in "Select Fields to Specify Values section. If you did Fetch JSON and Iterate over JSON Records settings correctly you will see the fields on the right under JSON Objects and you can then drag and drop in to the box for each field. https://help.quickbase.com/pipelines/images/json_handler_channel_2.png

      My customFieldValues section was not at the top level that can be dropped in and that is were my raw_record example, {{b.raw_record['customFieldValues'][0]['value']}}  comes in. Almost all of this is from https://help.quickbase.com/pipelines/json_handler_details.html so I am really just standing on the shoulders of those that wrote the help post. 



      ------------------------------
      James Carlos
      ------------------------------
      • PrashantMaheshw's avatar
        PrashantMaheshw
        Qrew Captain
        3 years ago

        HI James, 

        Thanks for detailed answer. With you help , I'm able to at least see JSON file properly with it's structure.  
        Are you able to create multiple records from your JSON file? I'm not

        My assumption is with this JSON two records should be formed with my JSON below

        Name | FILE

        Prashant | Record 1
        Prashant | Record 2 


        {
  "file": [
    "Record 1",
    "Record 2"
  ],
  "Name": "Prashant"
}


        Currently only 1 record is Created with Record 1 & Record 2 together in 1 value with
        1. {{b.raw_record['file']['value']}} OR
        2 {{b.raw_record['file'][0]['value']}}

        ------------------------------
        Prashant Maheshwari
        ------------------------------