cancel
Showing results for 
Search instead for 
Did you mean: 

Remove JSON Key when value is "" or null

ajs
New Contributor II

I have a json

[
  {
    "opportunity_Id": "Abc1",
    "sales_account_id": "Abc2",
    "account_name": "Pitney Bowes Inc.",
    "city": "Shelton",
    "address": "27 Waterview Dr",
    "requested_by": "Abc3@Abc3.com",
    "postal_code": 6484,
    "fax": "",
    "entitlement_group_id": "",
    "message_source": "Snaplogic",
    "contacts": [
      {
        "last_name": "T",
        "first_name": "",
        "email": "Abc4@Abc4.com",
        "contact_role": "AdminContact",
        "contact_id": "Abc5"
      }
    ],
    "entitlements": [
      {
        "quote_line_id": "Abc6",
        "transaction_type": "Quote",
        "status": "Active",
        "start_date": {
          "_snaptype_datetime": "2023-03-14T00:00:00.000 UTC"
        },
        "sku": "Abc7",
        "sfdc_product_id": "Abc8",
        "product_type": "Primary",
        "product_name": "Abc9",
        "product_id": "Abc10",
        "license_type": "",
        "license_group_id": "",
        "license_features": [
          {
            "unit_of_measure": "HOST",
            "quantity": 1
          }
        ],
        "is_trial": "false",
        "entitlement_contacts": [
          {
            "last_name": "T",
            "first_name": "",
            "email": "Abc11@Abc11.com",
            "contact_role": "AdminContact",
            "contact_id": "Abc12"
          }
        ],
        "end_date": {
          "_snaptype_datetime": "2024-03-14T00:00:00.000 UTC"
        },
        "configuration": [
          {
            "plan_type": ""
          }
        ],
        "channel_platform": "Salesforce  CPQ",
        "delivery_number": "",
        "delivery_line": "",
        "serial_number": "",
        "marketplace_product_id": "Abc13"
      }
    ]
  }
]

I want the output as

[
  {
    "opportunity_Id": "Abc1",
    "sales_account_id": "Abc2",
    "account_name": "Pitney Bowes Inc.",
    "city": "Shelton",
    "address": "27 Waterview Dr",
    "requested_by": "Abc3@Abc3.com",
    "postal_code": 6484,
    "message_source": "Snaplogic",
    "contacts": [
      {
        "last_name": "T",
        "email": "Abc4@Abc4.com",
        "contact_role": "AdminContact",
        "contact_id": "Abc5"
      }
    ],
    "entitlements": [
      {
        "quote_line_id": "Abc6",
        "transaction_type": "Quote",
        "status": "Active",
        "start_date": {
          "_snaptype_datetime": "2023-03-14T00:00:00.000 UTC"
        },
        "sku": "Abc7",
        "sfdc_product_id": "Abc8",
        "product_type": "Primary",
        "product_name": "Abc9",
        "product_id": "Abc10",
        "license_features": [
          {
            "unit_of_measure": "HOST",
            "quantity": 1
          }
        ],
        "is_trial": "false",
        "entitlement_contacts": [
          {
            "last_name": "T",
            "email": "Abc11@Abc11.com",
            "contact_role": "AdminContact",
            "contact_id": "Abc12"
          }
        ],
        "end_date": {
          "_snaptype_datetime": "2024-03-14T00:00:00.000 UTC"
        },
        "channel_platform": "Salesforce  CPQ",
        "marketplace_product_id": "Abc13"
      }
    ]
  }
]

Basically, I want to remove/ignore the JSON keys when the value is “” or null

Urgent!

1 ACCEPTED SOLUTION

alchemiz
Contributor III

Hi @ajs,

Here’s another approached a string manipulation using regex 🙂

JSON.parse(JSON.stringify($).replace(/"\w+":"",|"\w+":null,|,"\w+":""|,"\w+":null|"\w+":""|"\w+":null/g,'').replace(/\{\},/g,''))

Incoming document
image

result after doing some string manipulation
image

Thanks,
MM

View solution in original post

6 REPLIES 6

JensDeveloper
Contributor II

Try this expression in a Mapper snap:

  • jsonPath($,“$…[?(value == “” || value == null)]”) and as target empty and check pass trough.

Regards

Jens

I second the solution provided by @JensDeveloper however using a OR (||) has given me some errors before so if you @ajs face that as well, use an AND (&&) operator.

jsonPath($,“$…[?(value == “” && value == null)]”)

ajs
New Contributor II

Same error with && too

image

ajs
New Contributor II

Thanks for the suggestions @darshthakkar and @JensDeveloper

However, I found a way myself
image

  1. JSON Generator has the Input JSON,

  2. Checking if value is null || “” in conditional snap and writing to separate target paths if the key has value and key is “” || null
    image

  3. Created an XML for the same JSON and populating the XML tags only when the JSON key have data in it
    image

  4. Then again convert the XML to JSON using Mapper2 where only XML is written to content and converting it to binary using Document to Binary

  5. Then parse the XML to convert the same to JSON

This solution is working fine for me

but I did not test this for multiple objects, I’m still skeptical on multiple objects.