Quickbase Pipelines: How to extract nested JSON array, using Jinja, into Multi-Select Text field?

Question

I am using Quickbase Pipelines to pull data from a REST API and save it into a table. The flow of the pipeline is:

B. Fetch JSON
C. Prepare Bulk Record Upsert
D. Iterate over JSON records
For each JSON Handler - JSON Objects (D) Do
    E. Add Bulk Upsert Row
F. Commit Upsert

In the "Add a Bulk Upsert Row" step I am mapping fields from my table to properties of incoming JSON records.

I am facing a challenge around "custom fields" in my JSON records. There are fields where instead of simply appearing as "key": "value" pairs in JSON, they appear in a nested array similar to this:

"custom_fields": [
    {
        "key": "street",
        "value": "foo",
    },
    {
        "key": "city",
        "value": "bar",
    },
    {
        "key": "state",
        "value": "baz",
    }
]

I've been able to extract specific values by their key from this structure using this Jinja code block inside my upsert step:

{% for field in d.custom_fields %}
{% if field['key'] == 'street' %}
    {{ field['value'] }}
{% endif %}
{% endfor %}

This works well for Text-type fields but this technique fails when trying to get a JSON array value into a Multi-Select Text-type field.

When I use the a Jinja code block like the one above to parse this bit of JSON:

"custom_fields": [
    {
        "key": "choices",
        "value": [
            "foo",
            "bar",
            "baz"
        ],
    }
]

I get these 3 values in my Multi-Select Text field: [u'foo', u'bar' and u'baz'].

It's as if Quickbase / Jinja are parsing the value from JSON as a literal string rather than an array.

My question is - How can I get the above JSON value to be transformed into the correct choices: foo, bar and baz?

Update: Having examined the activity logs for the pipeline, I've found that the JSON Object that QB derived from the actual JSON has the value as "[u'foo', u'bar', u'baz']" - a string - which explains the odd values ending up in the table field. Might this be a bug?

I've also found that the same JSON Object has a copy of itself under the property raw_record and in that copy the value is a sensible ['foo', 'bar', 'baz']. I've not been able, however, to make use of the raw_record in my Jinja templates.

What action are you using to query/get the data that you are then trying to transform... an action from the Records section of the Quickbase channel or are you making a call to the Rest API (Make Request action from Quickbase channel or Webhook channel)? — Erich Wehrmann, Feb 14 '23 at 15:57
Thanks. I've updated my question to reflect that I'm using the *Fetch JSON* action in Pipelines. — urig, Feb 14 '23 at 16:15
Replicating this now...one more Q: Is your Fetch JSON pulling data from QB or some other source? — Erich Wehrmann, Feb 14 '23 at 17:35

score 0 · Answer 1 · answered Feb 14 '23 at 17:54

0

In your a value for a Multi-Select field in an Add Bulk Upsert Row step you need to transform the array to a comma separated string (similar pattern to what you are using for a logic check on the key 'street'):

{% for item in array %}
{{item}}
{% if not loop.last %},{% endif %}{#Adds a comma between each value from array except the last one #}
{% endfor %}

answered Feb 14 '23 at 17:54

Erich Wehrmann

464
2
11

Thank you but this technique does not yield the correct results. It returns an array with these values: `[;u;';f;o;o;';;;;u;';b;a;r;';;;;u;';b;a;z;';]`. I think the root cause is that `{{ field['value'] }}` in my example (`array` in yours) is a string representation of the array and not the array itself. Possibly a bug in Quickbase Pipelines. – urig Feb 15 '23 at 09:22

urig · Answer 2 · 2023-02-16T07:23:31.543

I've found a workaround based on my understanding that Quickbase Pipelines makes the JSON array available to the Jinja template as a string representation of a Python array rather than as the actual Python array.

In my example above, the value of field['value'] is "[u'foo', u'bar', u'baz']".

The workaround looks like this:

{% for field in d.custom_fields %}
{% if field['key'] == 'street' %}
    {{ field['value'] 
       | replace("[", "")
       | replace("]", "") 
       | replace("u'", "")
       | replace("'", "") }}
{% endif %}
{% endfor %}

The result of this template is "foo, bar, baz" which the "Add Bulk Upsert Row" step correctly enters into my Multi-Choice Text-type field.

Quickbase Pipelines: How to extract nested JSON array, using Jinja, into Multi-Select Text field?

2 Answers2