JSON Query wildcard

[SorenB]

Hello

I am looking to extract several values in a JSON-object, perhaps by using a wildcard if possible.
The placement of the values is based on another key on the same level.

Based on the image below, I want to extract all tokens called “document-1”, “document-2” etc.
I always know that they are situated in the JSON where identifier = ElementUpload.

Before extracting/looping the “document”-keys, I want to make a .Count to check if they are present at all.

The expression below, among others, does not work.

jobj_Blanketdata.SelectTokens("..[?(@.identifier == 'ElementUpload')].values.document[*]").Count

I hope it makes sense.
@ppr perhaps you have a suggestion?

Best regards
Soren

[sven.wullum1]

Hi!

I’ve started using LINQ instead when handling JSON.
I find it easier to remember the syntax.
Querying JSON with LINQ

“Navigate” to your jsonArray and try something like this:

yourJsonArray _
            .Where(Function(e) e("identifier").HasValues AndAlso e("identifier").ToString= "ElementUpload") _
            .SelectMany(Function(e) e("values").Children()) _
            .Count()

I’d even go so far as deserializing it into an object of a record type. And then using linq on it

However SelectTokens and your expression should have worked

[ppr (Peter Preuss)]

Working with this sample data:

@SorenB

want to make a .Count to check if they are present at all.

would recommend to more sharpen the details:

Counting JTokens we can do:

Checking existing return we can do:

But, when counting/filtering the only document-X JTokens we may touch a limit at the JSON Path. (looks like we cannot touch the property name with a JSONPath function used within a filter expression. But it is not confirmed and maybe technically possible when involving keys() and regex?)

Here would LINQ help and can be involved within a hybrid mode:

 myJObject.SelectTokens("..[?(@.identifier == 'ElementUpload')].values.*").Any(function (x) x.Parent.Value(Of JProperty).Name.startswith("document-"))

 myJObject.SelectTokens("..[?(@.identifier == 'ElementUpload')].values.*").Count(function (x) x.Parent.Value(Of JProperty).Name.startswith("document-"))

And can also be ported to the Query syntax eg. for filtering, value retrieval …

UPD1- Added Check for empty filter result and that it is not resulting into an exception:

[SorenB]

Thank you for the suggestions.

I went with your .Count approach and in my For Each …values.* I simply have an If-activity to check whether currentvalue.Path.Contains(“values.document-”).
Perhaps not the prettiest solution I came up with, but it works

Assign int_NumberOfDocuments = jobj_Blanketdata.SelectTokens("..[?(@.identifier == 'ElementUpload')].values.*").Count(function (x) x.Parent.Value(Of JProperty).Name.startswith("document-"))

If int_NumberOfDocuments > 0
For Each jobj_Blanketdata.SelectTokens("..[?(@.identifier == 'ElementUpload')].values.*") as currentvalue
If currentvalue.Path.Contains("values.document-")
Add currentvalue to list_DocsForExtraction

Regards
Soren

[ppr (Peter Preuss)]

@SorenB

@SorenB

I went with your .Count approach and in my For Each …values.* I simply have an If-activity to check whether

Perfect, also have a look at this variation as mentioned on:

@ppr

And can also be ported to the Query syntax eg. for filtering, value retrieval …

myLKDict | Dictionary(Of String, String) =

(From jt In myJOBject.SelectTokens("..[?(@.identifier == 'ElementUpload')].values")
From pr In jt.Value(Of JObject).Properties()
Where pr.Name.toUpper().StartsWith("DOCUMENT-")
Select r=pr).ToDictionary(Function (x) x.Name, Function (x) x.Value.Value(Of String))

So we avoid doing the same retrieval job twice (result check and result retrieval)

And also the negative / empty result test:

for sure we can also go for variations