Filtering rows by JSON Key/Value pairs

Let’s say you have a dataset with a JSON column, and every row in this column has a different set of JSON keys/values that pertain to that row of information. This means not every row will contain the same keys.

Now, imagine you want to filter this dataset such that it only includes rows that contain a given JSON Key / Value pair; e.g., if your keys are different countries, and your values are different types of working rights, you might want to filter out rows that don’t contain a particular country key / working rights value.

If your org uses Snowflake, you can use the following steps/formulas to accomplish this filtering task:

  1. Add 2 new control elements of type “textbox”

  2. Change the control types to “Parameter” for each textbox control

  3. Update the control IDs to [Key-Parameter] and [KeyValue-Parameter]

  4. In your table, create a new column called [All Keys in Json] with the formula

    • Text(CallVariant("object_keys", [Json])).
    • Feel free to make the string result prettier; i.e., instead of using the above formula, use:
      • SplitPart(SplitPart(Text(CallVariant("object_keys", [Json])), "[", 2), "]", 1).
  5. In your table, create a new column called [Key Value] with the formula

    • Text(CallVariant("JSON_EXTRACT_PATH_TEXT", [Json], [Key-Parameter]))
  6. In your table, create a new column called [Indicator Column] with the formula

    • If(Contains([All Keys in Json], [Key-Parameter]) and Contains([Key Value], [KeyValue-Parameter]), 1, 0)

    • The above formula won’t return any 1’s if either parameter control is left empty, so be sure to use the below formula if you want to return all rows when either parameter is left empty:

      • If(Contains([All Keys in Json], [Key-Parameter]) and Contains([Key Value], [KeyValue-Parameter]) or IsNull([Key-Parameter]) or IsNull([KeyValue-Parameter]), 1, 0)
  7. Filter the [Indicator Column] so that it only includes values of 1

  8. Hide whatever columns you don’t want to see