What problem are you observing?
We are experiencing an issue with the JSONPath filtering logic while trying to extract LegalEntityId from the JSON structure.
The JSONPath we used is:
$.items[*].workRelationships[?(@.PrimaryFlag==true && @.assignments[?(@.AssignmentStatusType=='ACTIVE')])].LegalEntityId
However, the result is incorrect. The expected outcome is that only the LegalEntityId from the work relationships that have at least one active assignment should be returned. Instead, the JSONPath returns:
300000002654097, 300000002655020
This is incorrect because there are work relationships where the assignments have an AssignmentStatusType of “INACTIVE”, which should be excluded from the result.
What is the correct behavior?
The correct behavior would be to filter out the workRelationships that do not have at least one assignment with the AssignmentStatusType set to “ACTIVE”. The expected result should only return the LegalEntityId from work relationships with active assignments.
What product feature is this related to?
Oracle HCM Cloud Connector JSONPath
What are the steps to reproduce the issue?
- Use the provided JSON file.
- Apply the JSONPath query:
$.items[*].workRelationships[?(@.PrimaryFlag==true && @.assignments[?(@.AssignmentStatusType=='ACTIVE')])].LegalEntityId - Observe that the result includes work relationships that should be excluded.
Do you have any other information about your environment that may help?
Attached is the screenshot where we tested the JSONPath in our JSONPath-evaluator site (JSONPath Evaluator), and another screenshot from a public site used for testing JSONPaths (JSONPath.com). On the external site, the result is correct, but on our site, it is not.
Please investigate this issue, as the filtering logic does
data.json (4,9 KB)
not seem to work as expected when handling nested assignment data.