JSON - filter array by string

Question

I am working in MS Power Automate (Flow) and I want to filter a JSON array by a string to retrieve the value...

here is a subset of my JSON Data (this data has been extracted from a SharePoint list by a Flow Get Items step):

[
 {
"@odata.etag": "\"2\"",
"ItemInternalId": "1",
"ID": 1,
"Setting1": {
  "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
  "Id": 0,
  "Value": "Email Addresses"
},
"Setting1#Id": 0,
"Setting2": {
  "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
  "Id": 0,
  "Value": "Retail Marketing Team Email"
},
"Setting2#Id": 0,
"Value1": "person1@company.co.uk; person2@company.co.uk; "
},
{
"@odata.etag": "\"2\"",
"ItemInternalId": "2",
"ID": 2,
"Setting1": {
  "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
  "Id": 0,
  "Value": "Email Addresses"
},
"Setting1#Id": 0,
"Setting2": {
  "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
  "Id": 1,
  "Value": "Social Media Email"
},
"Setting2#Id": 1,
"Value1": "paidsocial@company.co.uk; person3@company.co.uk; person4@company.co.uk;"     
},
....
]

I need to filter that on the field Setting2 = "search string", and return Value1 by matching a string

So, for Setting2 I would use 'Retail Marketing Team Email' and extract the value of the Value1 field which is '*person1@company.co.uk; person2@company.co.uk; *'

Now, with $..['Value1'] I can get all of the Value1 values, and with filtering I should be able to retrieve a specific Value1 element...

I have tried this:

$.[?(@.Setting2 == 'Retail Marketing Team Email')]['Value1']

and

$..['Setting2'].['Value' == 'Retail Marketing Team Email']?['Value']

and several other variations but never seem to get any data back, or OI get a JSON Parse error

what is the right way to go about this?

I don't believe this can be done with jsonpath (since it calls for nested filters). It can probably be done with jsonpath+ or jq. — Jack Fleeting, Aug 14 '20 at 19:35
@JackFleeting: What about if I did it in two steps - passing the results from the first filter into the second? — Our Man in Bananas, Aug 15 '20 at 10:38
It won't work, I'm afraid. `Value` is a child of `Setting2` while `Value1` is its sibling, so to get to `Value1` you need to get back to `Value`'s parent and then down to the parent's sibling `Value1` - and jsonpath (unlike, for example, xpath) isn't sophisticated enough to do that. In this particular case (but not all), jsonpath+ can help, because it has a "parent" operator. — Jack Fleeting, Aug 15 '20 at 13:39
@JackFleeting: Thanks, where can I read-up on `Jsonpath+` please? A google search didn't return much useful information... — Our Man in Bananas, Aug 16 '20 at 01:18
You can find more info here: https://www.npmjs.com/package/jsonpath-plus. I'm not aware of a python version, unfortunately. — Jack Fleeting, Aug 16 '20 at 01:53
@JackFleeting: it occurred to me that I could simplify the source SharePoint list just to two columns: one for **Setting2** and one for **Value1** - then we would just have to filter on one column and return it's sibling *Value1*. would that work? — Our Man in Bananas, Aug 17 '20 at 11:07
Not really sure. The best way to approach it is to create a new question with the simplified version of the list and post it. Another option, if you can manipulate the SharePoint output, is to save it as xml, which will make the whole process much simpler. — Jack Fleeting, Aug 17 '20 at 11:43
@JackFleeting: Thanks, I'll create a new question with the simplified list structure ... — Our Man in Bananas, Aug 17 '20 at 12:02
@JackFleeting: I've created a new question: https://stackoverflow.com/questions/63451441/filter-json-array-using-string-value — Our Man in Bananas, Aug 17 '20 at 13:05

JSON - filter array by string

0 Answers0