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!

expression_language
pipeline
sample_pipeline
transform
  • alchemiz's avatar
    alchemiz
    3 years ago

    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

  • JensDeveloper's avatar
    JensDeveloper
    Contributor II
    3 years ago
    Copy LinkReport

    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
      3 years ago
      Copy LinkReport

      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's avatar
    ajs
    New Contributor II
    3 years ago
    Copy LinkReport

    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.

    • alchemiz's avatar
      alchemiz
      Contributor III
      3 years ago
      Copy LinkReport

      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