Discussions

 View Only
  • 1.  Pipeline Iterate over JSON nested data

    Posted 07-28-2022 11:35
    Edited by James Carlos 07-28-2022 11:37
    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.

    Steps to iterate over JSON






    ------------------------------
    James Carlos
    ------------------------------


  • 2.  RE: Pipeline Iterate over JSON nested data

    Posted 07-28-2022 13:04
    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
    ------------------------------



  • 3.  RE: Pipeline Iterate over JSON nested data

    Posted 07-28-2022 14:10

    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 {}.

    Iterate Over JSON Schema Sample
    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. 
    Iterate Over JSON step Limit And Filter settings

    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
    ------------------------------



  • 4.  RE: Pipeline Iterate over JSON nested data

    Posted 07-29-2022 11:01

    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
    ------------------------------