Forum Discussion

UriGoldstein's avatar
UriGoldstein
Qrew Trainee
3 years ago

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

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

2 Replies

  • JustinTorrence's avatar
    JustinTorrence
    Qrew Cadet
    3 years ago

    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
    ------------------------------
    • UriGoldstein's avatar
      UriGoldstein
      Qrew Trainee
      3 years ago

      Hi Justin,

      Many thanks for the more succinct way of extracting the value from the nested JSON array. And thanks for the explanation and the link, they are most useful. 🌷

      I've opened a case with Quickbase to examine why the array is flattened to a Python string representation in the first place. Will update if there are any conclusions.

      Cheers,

      Uri



      ------------------------------
      Uri Goldstein
      ------------------------------