Forum Discussion

amardeep2021's avatar
amardeep2021
New Contributor III
4 years ago

How to remove columns with null value alone in pipeline?

Hi,

I have a scenario where I have few columns have null value few not. Objective is to not to bring in columns with null value for each records.

source:

id a b c d e
abc null 1 2 null null
def 11 null 22 33 null
ijk 111 222 null 333 444
lmo null null null null 5555

Expected:
abc 1 2

samething with other records as well. Does anyone know any trick on this? Please let me know.

Regards,
Amar.

6 Replies

  • @amardeep2021

    Hi,

    You can achieve this by using a mapper snap with the following expression:
    $.filter(value => value != null) mapped to the target path $
    this will filter all values out that equal null, keeping only the key-value pairs in which the value is not null.

    Keep in mind this will only work for 1 level of nesting as I noticed your data was. If it does need to work with nested JSON fields it would be a different expression that I can provide you with as well if needed.

  • amardeep2021's avatar
    amardeep2021
    New Contributor III

    Thanks Cole. Kindly share the other expression also.

    Regards,
    Amar.

    • cjhoward18's avatar
      cjhoward18
      Employee

      You can use this expression:

      { filterEmptyKeys: (value, key) => value != null, cleanupTree: value => value instanceof Object ? value.mapValues(this.cleanupTree).filter(this.filterEmptyKeys) : value }.cleanupTree($)

      mapped to the target path $ to remove all null values from a nested JSON object

  • amardeep2021's avatar
    amardeep2021
    New Contributor III

    Thanks Cole again. For the first expression, I am getting none null columns all coming as array for each rows. Is that expected?

    • cjhoward18's avatar
      cjhoward18
      Employee

      Not sure what you mean. Can you share a version of the input-output example?