Querying JSON using SelectTokens for a JSON object with a changing structure

Question

This question is very related to the following one in

Querying JSON with JSONPath or SelectTokens? With JSON.NET in C#

Similar to the question above, how can I make Where queries when the JSON object does not always have the same simplified data structure, i.e.

{
  "video": {
    "local_recording_device": {
      "codecs": null
    },
    "preferred_string": "___PREFERRED___",
    "streams": {
      "22855218": {
        "id": "22855218",
        "name": "AJA Camera"
      },
      "99176901": {
        "id": "99176901",
        "name": "PTZ Camera",
        "site": "someone",
        "email": "someone@awebsite.com",
        "codec": [
          "VP8",
          "HD1",
          "(720p)"
        ]
      },
      "3091494011": {
        "id": "3091494011",
        "name": "Logitech Webcam C930e",
        "site": "Joe Smith",
        "email": "joe@awebsite.com",
        "codec": [
          "VP8",
          "Medium",
          "(CIF)"
        ]
      },
      "3798287599": {
        "id": "3798287599",
        "name": "Drive Camera",
        "site": "ASiteName",
        "email": "asitesame@awebsite.com",
        "codec": [
          "HD1",
          "(720p)"
        ]
      }
    }
  }
}

How can I get the Where query to work on the given codec, e.g. "VP8"?

It doesn't seem to work with my applications if I query it like this:

string json = GetJson();
var obj = JObject.Parse(json);
var testcodec = "VP8";

var streamQuery = obj.SelectTokens("video.streams.*.codec")
                     .Where(s =>(string)s == testcodec);

But my result should look like:

{  
  "video":{  
    "local_recording_device":{  
        "codecs":null
    },
    "preferred_string":"___PREFERRED___",
    "streams":{  
        "99176901":{  
            "id":"99176901",
            "name":"PTZ Camera",
            "site":"someone",
            "email":"someone@awebsite.com",
            "codec":[  
                "VP8"
            ]
        },
        "3091494011":{  
            "id":"3091494011",
            "name":"Logitech Webcam C930e",
            "site":"Joe Smith",
            "email":"joe@awebsite.com",
            "codec":[  
                "VP8"
            ]
        }
    }
  }
}

what exactly you expect to get and what you're actually getting? — Felix Av, Jul 27 '17 at 10:39
I would like to get an array of similar structure to the original but with all elements that fulfill my query. That is in this case the complete json tree with the following two streams 3091494011, 99176901 — Andres, Jul 27 '17 at 12:59

score 1 · Answer 1 · answered Jul 27 '17 at 15:16

1

Your query doesn't work because it is asking for just the codec arrays and then trying to compare those arrays to a string value. You need a query that asks for the streams, and then checks whether each stream's codec array contains the correct string.

Try like this instead:

var streamQuery = obj.SelectTokens("video.streams.*")
                     .Where(t => t["codec"] != null &&
                                 t["codec"].Any(c => (string)c == testcodec));

Then you can do something like this to get the data from the query results:

foreach (var stream in streamQuery)
{
    Console.WriteLine("id: " + stream["id"]);
    Console.WriteLine("name: " + stream["name"]);
    Console.WriteLine("site: " + stream["site"]);
    Console.WriteLine("email: " + stream["email"]);
    Console.WriteLine("codecs: " + string.Join(", ", stream["codec"].Select(c => (string)c)));
    Console.WriteLine();
}

Fiddle: https://dotnetfiddle.net/Ak8PMZ

answered Jul 27 '17 at 15:16

Brian Rogers

125,747
31
299
300

awesome, almost there!! In my codec list I would like to get only the word I am looking for, i.e. in this case `"codec":["VP8"]`. Do you think this is possible? – Andres Jul 28 '17 at 13:19
You've already got it-- it's in your `testcodec` variable. You already know all the streams in the result match this codec based on the query. So for each result you can simply do `Console.WriteLine("codec: " + testcodec);` – Brian Rogers Jul 28 '17 at 14:21
Thanks, That would be a bit too trivial. I have updated my question and really appreciate your help! – Andres Jul 28 '17 at 14:57
It sounds like you're not merely looking to query the JSON and use the result, but to *transform* the JSON from one form to another -- is that what I'm hearing? – Brian Rogers Jul 28 '17 at 15:22
Maybe that is the terminology I was aiming for. Is there an simple way to do that? Or do I have to go through the complete tree and extract the data as I go? Thanks a lot!! – Andres Jul 31 '17 at 06:55

score 0 · Answer 2 · answered Jul 27 '17 at 11:15

0

var streamQuery = obj.SelectTokens("$.video.streams.*.codec") .Where(x=>x.First.Value<string>()==testcodec);

answered Jul 27 '17 at 11:15

Ansil F

284
1
8

While this code may answer the question, providing additional context regarding why and/or how this code answers the question improves its long-term value. – Aurril Jul 27 '17 at 11:45
right now with `obj.SelectTokens("$.video.streams.*.codec")` I get a null – Andres Jul 27 '17 at 13:02

Querying JSON using SelectTokens for a JSON object with a changing structure

2 Answers2