Skip to main content
Question

Quickbase Pipelines: How to upsert nested JSON array to multi-select text field?

  • February 14, 2023
  • 0 replies
  • 262 views
Forum|alt.badge.img+1

Hello,

I am using  Quickbase Pipelines to pull data from a REST API and save it into a table. I have a "Add a Bulk Upsert Row" step where 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 These 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 fields but my first question is - Is this the right way to go about getting in done? Might there be a more succinct way to do this using Jinja?

Unfortunately for me, this technique fails when trying to get a JSON array value into a Multi-Select Text 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 as a literal string rather than a JSON array.

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

Thanks,
Uri



------------------------------
Uri Goldstein
------------------------------
    This topic has been closed for replies.

    How interesting. I just had to solve this problem the other night.
    To get a more succinct Jinja2 expression you'll want to use combinations of following filters:

    If I understand your schema correctly, the expression for the first issue (where you are simply writing to a text field) would look like this:

    {{ d.custom_fields | selectattr('key', 'eq', 'street') | map(attribute='value') | join('') }}

    The second problem may be a little less elegant because there isn't a way that I know of to combine multiple lists using jinja2.
    {% for choice in d.custom_fields | selectattr('key', 'eq', 'choices') -%}
    {{ choice.value | join(';') -}}
    {% if not loop.last %};{% endif -%}
    {% endfor -%}

    The important part to remember about multi-select text is that a semicolon should be used as a delimiter (unless you are posting the data directly via the RESTful API).

    Here is a live parser that allows you to test with json instead of only YAML.
    https://j2live.ttl255.com/



    ------------------------------
    Justin Torrence
    Quickbase Expert, Jaybird Technologies
    jtorrence@jaybirdtechnologies.com
    https://www.jaybirdtechnologies.com/#community-post
    ------------------------------
      Terms & ConditionsAccessibility statement