- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
06-22-2023 09:24 AM
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!
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
06-25-2023 02:05 PM
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
06-23-2023 03:32 AM
Try this expression in a Mapper snap:
- jsonPath($,“$…[?(value == “” || value == null)]”) and as target empty and check pass trough.
Regards
Jens
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
06-23-2023 10:22 AM
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)]”)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
06-24-2023 02:06 AM
Same error with && too
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
06-24-2023 02:13 AM
Thanks for the suggestions @darshthakkar and @JensDeveloper
However, I found a way myself
-
JSON Generator has the Input JSON,
-
Checking if value is null || “” in conditional snap and writing to separate target paths if the key has value and key is “” || null
-
Created an XML for the same JSON and populating the XML tags only when the JSON key have data in it
-
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
-
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.