Forum Discussion

ajs's avatar
ajs
New Contributor II
3 years ago
Solved

Remove JSON Key when value is "" or null

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!

  • 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

    result after doing some string manipulation

    Thanks,
    MM

6 Replies

  • Try this expression in a Mapper snap:

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

    Regards

    Jens

    • darshthakkar's avatar
      darshthakkar
      Valued Contributor

      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)]”)

  • Thanks for the suggestions @darshthakkar and @JensDeveloper

    However, I found a way myself

    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

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

    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.

    • 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

      result after doing some string manipulation

      Thanks,
      MM