Issue with Accessing Nested Array(JSON handler) in Quickbase Pipeline

Question

json_records_path:/systems&nbsp;{&nbsp; "id": 6816675,&nbsp; "url": "https://api.opensolar.com/api/orgs/146358/systems/6816675/",&nbsp; "name": "No value",&nbsp; "uuid": "F234D90E-E9E4-4F18-ADC9-89C0B9420385",&nbsp; "order": 0,&nbsp; "system_lifetime": 0,&nbsp; "inverter_range": "No value",&nbsp; "dc_optimizer_active": false,&nbsp; "dc_optimizer_efficiency": 1,&nbsp; "show_customer": true,&nbsp; "is_current": false,&nbsp; "auto_string": true,&nbsp; "discount": 0,&nbsp; "adders_per_system": 0,&nbsp; "adders_per_panel": 0,&nbsp; "adders_per_watt": 0,&nbsp; "kw_stc": 1.68,&nbsp; "battery_total_kwh": 19.6,&nbsp; "price_including_tax": 5862.5,&nbsp; "price_excluding_tax": 5862.5,&nbsp; "net_profit": 13797.04,&nbsp; "module_quantity": 5,&nbsp; "co2_tons_lifetime": 15.48,&nbsp; "project": "https://api.opensolar.com/api/orgs/146358/projects/5616041/",&nbsp; "org": "https://api.opensolar.com/api/orgs/146358/",&nbsp; "pricing_scheme": "https://api.opensolar.com/api/orgs/146358/pricing_schemes/278737/",&nbsp; "pricing_scheme_data": {&nbsp; &nbsp; "battery_scheme": "No value",&nbsp; &nbsp; "output_annual_kwh": 2332,&nbsp; &nbsp; "consumption_offset_percentage": 49&nbsp; },&nbsp; "modules": [&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; "module_activation_id": 886178,&nbsp; &nbsp; &nbsp; "code": "SPR-P3-335-BLK",&nbsp; &nbsp; &nbsp; "manufacturer_name": "SunPower",&nbsp; &nbsp; &nbsp; "quantity": 5&nbsp; &nbsp; }&nbsp; ]}&nbsp;&nbsp;The above is the details I get when i access it through /systems json record path.However, I am having trouble accessing the modules array inside the system data when iterating over the JSON records in Quickbase Pipeline.Specifically, I cannot retrieve the modules array, which is nested within the system object. Despite multiple attempts to use paths like /systems[0]/modules or /systems[*]/modules or /modules or /systems/modules or system.modules&nbsp; I am not able to iterate over or access the modules array directly in the pipeline.Could anyone provide guidance on why the modules array is not being accessible within the Quickbase Pipeline, and suggest the correct JSONPath or method to properly access it?

mez · Answer

I have also been working (learning about) with json. I found an older thread, *https://community.quickbase.com/discussions/quickbase-discussions/pipeline-make-request---return-make-request-json-output-into-fields/86560, that linked to this site for seeing the path: json path finder validator

Since you've already defined your path as '/systems', you can access the items from the iterate step; assuming step b, it would be: b.modules[0].code to obtain the code from your sample json schema.

If there are multiple array's returned in modules, use the index (zero based) to get at the array you want.

*cannot link to it as 'content not published error'

srishha · Answer

Hey Mez, thanks for your reply. Yes, I agree that we can access it using b.modules[0].code. However, what if I want to iterate through the modules array to insert records into the field individually, without explicitly specifying b.modules[0].code? The challenge arises when we don't know the length of the modules array in advance.

Is there a way to dynamically provide the path for the modules during iteration, similar to how we access other elements via the /systems path?

mez · Answer

The easiest is to use a for loop. Depending on your use case, this may not be what you're after.&nbsp;{% for mod in b.modules %}
  mod.code
{% endfor %}A use case I'm working with is fetching a report from quickbase, then iterating over the results. Each row returned has an array of locations (similar to modules). Using two different fields, I capture all the locations returned, and then grab the last one based on the loop index within the for loop. Hope this helps.&nbsp;{# code for locations field #}
{% set vals = namespace(value=[]) %}
{% for val in d.n19.value %}
  {% set vals.value = vals.value | append(val) %}
{% endfor %}
{{ vals.value | join("; ") }}Which produces the values in the record as:Bangalore - Karnataka - India; Kochi - Kerala - India; Noida - Uttar Pradesh - India&nbsp;Grabbing just the last value in the array:{# one value in locations - example #}
{% for val in d.n19.value %}
{% if loop.last %}
  {{ val }}
{% endif %}
{% endfor %}Which produces the value in the record as:Noida - Uttar Pradesh - India

srishha · Answer

Thank you, Mez , for your valuable insights. However, my specific concern is navigating through the path of modules to iterate over JSON records and insert each record individually into the table rather than using for loop in Jinja. Any guidance on achieving this would be greatly appreciated.

mez · Answer

Who doesn't like a good challenge. Ok, assuming you need the 'systems' info in this json path, if you are creating a 'system' record to then have child module records, you could solve this a few different ways.

One way, assuming you first create the parent 'system' record, you could then call another pipeline to add the child record of the 'module' since you have the parent record ID you just created.

Create a callable pipeline and use the record ID of the parent, and the module details as the arguments to the call, something like:

createModuleForSystem(recIdPar, actId, code, manfName, [quantity])

then use the built-in create record step, voila!

Forum Discussion

Issue with Accessing Nested Array(JSON handler) in Quickbase Pipeline