How do I remove null or " " key:value pairs from an Airkit object?

Do we have any Airscript functions that we could use to remove null values from the objects.

Context: I have a customer’s app where the contents of a form is passed to an object, but further on users have the possibility to edit their personal info, including erasing optional data. If they delete a field that previously was completed it would leave this object with a null property.
This is part of a payload that is sent to an external platform where a data validation is performed which can’t accept null values.

Changing the external validation logic might be a possibility to but I’m trying to find a way to do this from our end.

image

Hey Adrian,

In situations like this, we’ve called a data flow in the journey to filter out null or "" values in key value pairs. To do this, I’d add a step in a data flow to remove any key:value pairs if the value is null or "". The Airscript would looks something like this:

FROM
  item,
  key
IN
  test
WHERE
  item != "" AND item != NULL
SELECT
  "{{key}}": "{{item}}"

Or the following screenshot.

There might be other options, but I’ve used this before.

5 Likes

Drew - Thanks for this! Do you know how I could adapt that query in the case of more than one object in a variable? For example, I want to remove the key/value pairs as you did, but return all the variables instead of the “[object Object]” showing here:

@eric just remove the "{{ }}" from that item field to select the whole object:

FROM
  item,
  key
IN
  test
WHERE
  item != "" AND item != NULL
SELECT
  "{{key}}": item
1 Like

hey everyone!

A couple of questions come to mind before solving this…

  1. Is the "provider" field always a JSON obj (as opposed to List of < JSON >)?
  2. Do we always know the key name (in this example "provider")?

If the answer to both is YES, then a quick way to recursively get this sorted could be

MERGE_OBJECTS(
  MERGE_OBJECTS(
      FROM value, key IN myJsonObj 
          WHERE ISNOTEMPTY(value) 
          SELECT { "{{key}}": value }),
          { "provider": FROM value, key IN myJsonObj.provider 
              WHERE ISNOTEMPTY(value) 
              SELECT "{{key}}": value }
)

Let’s break that down:

 FROM value, key IN myJsonObj 
       WHERE ISNOTEMPTY(value) 
       SELECT { "{{key}}": value })

This bit only checks and returns non nulls/empty values from the main obj.

The following:

SELECT { "{{key}}": value }),
          { "provider": FROM value, key IN myJsonObj.provider 
              WHERE ISNOTEMPTY(value) 
              SELECT "{{key}}": value }

Checks and returns a "provider" object that contains non null/empty values.

By merging both (in that order) the provider obj of the main myJsonObj is replaced with the second iteration, the provide JSON obj that contains non-null/empty fields.


Now, this is not to say that there’s no better way to do this, as my initial questions show, depending on the use case (and even if both answers are YES) there are a few ways to go about this.

@Adrim Let me know if this helps, or if you have an extensive use case that might require handling of a more complex JSON structure, always happy to look at it.

cheers!

knowledge is but a universal collection of data, always evolving, always expanding, and every human’s addition to it is valuable and valued

1 Like

Depending on the depth of your object, you can also use a User-Defined Function to recursively crawl and remove the empty values from these objects.

To do this, make a new user-defined function called #REMOVE_EMPTY. Have it take 1 input, obj of type “Any”, and return an output of type “Any”

Define the body of the function as:

IF(
  TYPEOF(obj) = "appobject", 
  FROM item, key IN obj WHERE ISNOTEMPTY(item) SELECT "{{key}}": #REMOVE_EMPTY(item),
  obj
)

Then simply call that function on your object:

In:

#REMOVE_EMPTY({
  "blah": "foo",
  "foo": "",
  "obj": {
    "blah": "foo",
    "foo": "",
    "bar": null,
    "subobj": {
      "a": "a",
      "b": "",
      "c": null
    }
  },
  "bar": "foo"
})

Out:

{
  "blah": "foo",
  "obj": {
    "blah": "foo",
    "subobj": {
      "a": "a"
    }
  },
  "bar": "foo"
}
2 Likes