Problem querying JSON using LINQ when there are semicolons in the name tag

Question

I'm trying to work with a Json file from an outside source. They put semicolons and additional data in some the field names. I'm trying to query the data using LINQ and having problems. Is there a way of specifying wildcards in LINQ queries?

I'm working in C# and using the Newton.Json library.

I would like to be able to do something like this (where "*" is a wildcard):

JObject jsonData = JObject.Parse(jsonString);

JArray customers = (JArray)jsonData["Data*"]["customers"];

or

var customer = from c in jsonData["Data"["customers"][customer*] where c["customerID"] == "A32942" select c["customerName"];

I've searched Google and wasn't able to find any examples that talked about wildcards in LINQ.

Wondering if anyone have any ideas, thoughts?

Below is a representation of what the json data looks like.

Richard

{"Data:2022":{
    "customers":[
       {"customer:8166": {
             "customerName": "John Doe",
             "customerID": "A19321"
           }
       {"customer:3296": {
             "customerName": "John Doe",
             "customerID": "A32942"
        }
    ]
  }
}

Answer 1

No you cannot use wildcards in a field lookup like you're asking for. Field names should be constant so I'd argue this JSON is poorly implemented (if it were syntactically correct). No JSON generator I'm aware of could convert this JSON to an equivalent language-supported type and that is just bad design.

However you can still use good old fashion looping to do what you want. JObject implements IEnumerable<string, JToken> so you can enumerate the fields of the object. You can do your wildcard matching there using LINQ. Unfortunately you'd have to do this at each level where the field name contains data.

//Get all root level fields that start with `Data:`
var dataItems = jsonData.Where(x => x.Key.StartsWith("Data:")).Select(x => x.Value);
foreach (var dataItem in dataItems) {
   //Get all customers for the data item
   var customers = dataItem["customers"]?.Where(x => x.Key.StartsWith("customer:")).Select(x => x.Value) ?? Enumerable.Empty<JToken>();
};

Of course you can combine these 2 sets of statements into a single statement if you want but I would probably lean toward wrapping all this in an extension method instead. Irrelevant this approach does throw away the field name on Data and customer. If you need that information then you'll have to either create a simple wrapper object or use the KeyValuePair<string, JToken> that the JSON type is returning. To do that remove the Select calls to filter down to just the value.

Answer 2

Thanks everyone for replying. While it's valid JSON to have colon, etc. in the names, I think it's crappy of them structure their data as they have. But, I have absolutely no input in how the data is generated.

FYI, Resa, it may not look pretty, and yes, I did removed a number of fields for brevity stake, but the JSON is valid (you can check it by copying it into this webpage: https://codebeautify.org/json-decode-online).

Since as I suspected, wildcards are not supported, Michael, I'm going to take a little different approach and just regenerate the JSON data. I will iterate through the JSON (using JsonReader) and rebuild the JSON formatted string, fixing the issues as I walk through the data. So, I will end up with something like this:

{
   "Data":{
      "originalName" : "Data:2022",
      "nameData" : "2022",
      "customers":[
      {
          "customer": {
              "originalName" : "customer:8166",
              "nameData" : "8166",
              "customerName": "John Doe",
              "customerID": "A19321"
          }
      },
      {
          "customer": {
               "originalName" : "customer:3296",
               "nameData" : "3296",
               "customerName": "John Doe",
               "customerID": "A32942"
          }
      }]
   }
}

Then I will be able to do my processing, without having to jump through any loops. I think in the long haul, it's simpler to do that, then having to deal with a bunch of funky queries.

Thanks again everyone for the replies,

Richard

Answer 3

@Richard Rosenheim , Welcome to Microsoft Q&A, after my attempt. you could try the following code to use linq to query the json string.

JObject jsonData = JObject.Parse(jsonString);

var list = jsonData.SelectToken("Data:2022").SelectToken("customers").ToList();
JArray arr = new JArray(list);
var customer = from ar in arr
              where ar.Children().FirstOrDefault().Last.Value<string>("customerID") =="A32942"
              select ar.Children().FirstOrDefault().Last.Value<string>("customerName") ;

Hope my code could help you.

Best Regards,

Jack

---

If the answer is the right solution, please click "Accept Answer" and upvote it.If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.