Question 109716 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

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

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.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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.


  2. 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 %}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search