Forum Discussion

kmiesse's avatar
kmiesse
Contributor
8 years ago
Solved

Delete Array or Create Empty Array Based on Expression

If I have the following,

[{
	"fieldType": null,
	"array": [{
		"fieldA": null,
		"subArray": [{
			"field1": null
		}]
	}]
},
{
	"fieldType": null,
	"array": [{
		"fieldA": null,
		"subArray": [{
			"field1": 1,
			"field2": "asdf",
			"field3": "fdff"
		}]
	}]
}]

how do I set the subArray with field1 = null to an empty array or completely remove subArray like the following:
– EMPTY ARRAY

[{
	"fieldType": null,
	"array": [{
		"fieldA": null,
		"subArray": []
	}]
},
{
	"fieldType": null,
	"array": [{
		"fieldA": null,
		"subArray": [{
			"field1": 1,
			"field2": "asdf",
			"field3": "fdff"
		}]
	}]
}]

OR

– DELETE ARRAY

[{
	"fieldType": null,
	"array": [{
		"fieldA": null
	}]
},
{
	"fieldType": null,
	"array": [{
		"fieldA": null,
		"subArray": [{
			"field1": 1,
			"field2": "asdf",
			"field3": "fdff"
		}]
	}]
}]
  • tstack's avatar
    tstack
    8 years ago

    You might want to look at this post that had a similar request.

    In short, you can use a Mapper with a JSON-Path in the left field of a mapping row with an empty right field to delete all elements on the path. In this case, you’d probably use a path like the following:

    $array[*].subArray[?(value.field1 == null)]

    Make sure you have the ‘Passthrough’ option enabled in the Mapper.

8 Replies

  • tstack's avatar
    tstack
    Former Employee
    8 years ago

    You might want to look at this post that had a similar request.

    In short, you can use a Mapper with a JSON-Path in the left field of a mapping row with an empty right field to delete all elements on the path. In this case, you’d probably use a path like the following:

    $array[*].subArray[?(value.field1 == null)]

    Make sure you have the ‘Passthrough’ option enabled in the Mapper.

    • kmiesse's avatar
      kmiesse
      Contributor
      8 years ago

      Thank you so much. It worked by creating an empty $array[*].subArray. Do you know where I can learn more about the syntax to use to manipulate JSON like this? I get stuck and spin my wheels a lot on these types of issues.

      Also, is there a way to delete just a simple field, not an array/list, with the Mapper if its null like the above expression did for the array? When the right Target path is empty, I’m not sure what expression to use on the left to return the field when its null.

      • tstack's avatar
        tstack
        Former Employee
        8 years ago

        We have our own documentation for JSON-Path. There are also online testers, like:

        Note that most JSON-Path implementations are only for reading data, whereas we use it for updating and deletions. So,

        In the Designer, you should be able to drill down into the document structure in the Mapper and in the drop-down for expression properties.

        It’s pretty much the same thing, except instead of checking for value.field1 == null you can check if value == null. So, you would write a path to the object whose fields you want to delete (e.g. $.foo.bar) and then use the filter operator ([?(... expr ...)]) to select the fields to delete:

        $.foo.bar[?(value == null)]
  • Harikrish's avatar
    Harikrish
    New Contributor
    6 years ago

    Hi kmiesse,

    Was an solution provided to this? I am still facing the issue while trying to filter a key whose value is null

  • acesario's avatar
    acesario
    Contributor II
    5 years ago

    Thank you tstack for the answer to a problem I was facing. I was able to remove nulls and dashes across a document using this a mapper: $.[?(value == ‘-’ || value == null)]

    My inbound document looked like this:

    [
    {
    “TRANSACTION”: “32”,
    “DATE_OF_BIRTH”: “1995-09-21”,
    “SSN”: null,
    “BOX_OR_RESIDENCE”: “-”,
    “PRONOUN_DESC”: “-”,
    “AUDIT_ONLY”: “N”,
    “HOME”: “Y”,
    “HOME_SUPPRESSION”: “tbd”
    }
    ]