Skip to main content
Question

Parse JSON field value in Pipelines based on another field value from the object

  • October 13, 2023
  • 0 replies
  • 753 views
Frontman
Forum|alt.badge.img+4

Hi All,

I'm trying to parse a specific value from the JSON. I'm trying to get the value of the "display_value" where the object's GID is equal to "1205664665921946". I need to get it based on the GID field value because the order of the custom_field objects can change.

JSON sample (yellow number is what I need to parse)

{
  "data": {
    "gid": "1205719503165790",
    "custom_fields": [
      {
        "gid": "1202162434601082",
        "name": "Request Priority",
        "display_value": "Low"
      },
      {
        "gid": "1202169451444424",
        "name": "Associated Product",
        "display_value": "C"
      },
      {
        "gid": "1205664665921946",
        "name": "Primary URL",
        "display_value": "https://support.placeholder-text.us/hc/201363173"
      },
      {
        "gid": "1205713042708147",
        "name": "Reporting URL",
        "display_value": null
      }
    ]
  }
}

Method 1: Filter query


I tried using filter queries in the Iterate over JSON records step, but I get "Found no JSON object items" in the activity log. See this screenshot for the filter.


I then use this formula in the Regex step to extract the number from the URL: {{c.data_0.custom_fields[0].display_value}}

Method 2: Jinja

I also tried using this Jinja, but I'm getting an error: "Validation error: {'text': 'required field'}"

{% set target_gid = "1205664665921946" %}
{% set url = "" %}

{% for custom_field in c.data_0.custom_fields %}
{% if custom_field.gid == target_gid %}
{% set url = custom_field.display_value|string %}
{% endif %}
{% endfor %}

{{ url }}



------------------------------
Frontman
------------------------------

    This topic has been closed for replies.

    JimHarrison
    Forum|alt.badge.img+18
    • JimHarrison
    • Registered
    • October 16, 2023

    What are the results if the filter is removed?

    Usually when I am working on parsing JSON, there is some odd syntax missing or I am thinking about the filter differently than how to extract the desired result.

    An alternative is to extract the entire array "custom_fields" and place the values in a new object, then loop through that instead of looping through the nested array.



    ------------------------------
    Jim Harrison
    transparency = knowledge + understanding : The Scrum Dudes
    ------------------------------
    Forum|alt.badge.img+6
    • DougHenning1
    • Quickbase Staff
    • October 16, 2023

    The jinja isn't working as you expect because you need to use a namespace to access variables inside a for loop:

    {% set target_gid = "1205664665921946" -%}
{% set ns = namespace(url='') -%}
{% for custom_field in c.data_0.custom_fields -%}
{% if custom_field.gid == target_gid -%}
{% set ns.url = custom_field.display_value|string -%}
{% endif -%}
{% endfor -%}
{{ ns.url }

    Also, you should add a hyphen before the closing statement tags (see code above) to suppress line breaks otherwise you'll have a bunch of blank lines before the final value.

    Hope that helps!



    ------------------------------
    Doug Henning
    ------------------------------
    Terms & ConditionsAccessibility statement