How to do a projection on json string

Question

How to query some json to select a specific property?

EX: If I have Json obj like this :

[
   {
      "grd_symbol":"A+",
      "count":21.23,
      "code":4,
      "name":"X",
      "batch_no":760
   },
   {
      "grd_symbol":"A ",
      "count":11.93,
      "code":4,
      "name":"X",
      "batch_no":760
   },
   {
      "grd_symbol":"A-",
      "count":8.49,
      "code":4,
      "name":"X",
      "batch_no":760
   }
]

This's the output of :

string JsonObj =  Converter.ConvertDataTabletoString(DT);



public static string ConvertDataTabletoString(DataTable dt)
    {

        System.Web.Script.Serialization.JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer();
        List<Dictionary<string, object>> rows = new List<Dictionary<string, object>>();
        Dictionary<string, object> row;
        foreach (DataRow dr in dt.Rows)
        {
            row = new Dictionary<string, object>();
            foreach (DataColumn col in dt.Columns)
            {
                row.Add(col.ColumnName, dr[col]);
            }
            rows.Add(row);
        }
        return serializer.Serialize(rows);
    }

Now I want to get the count only,The result will be like this:

[21.23,11.93,8.49]

The point was, that this is a duplicate, though i tagged the wrong one — TheGeneral, Feb 27 '19 at 10:08
@MichaelRandall : Could u help me to fix my problem, note the project is .net 3.5 — Anyname Donotcare, Feb 27 '19 at 10:09
@AnynameDonotcare you ask about a projection and yet the result you want is a *selection*. You can use JsonPath with JSON.NET. As for targeting .NET 3.5, that's important information since it's no longer supported. Maybe there is a JSON.NET version that targets 3.5, may not — Panagiotis Kanavos, Feb 27 '19 at 10:10
first you have to deserialise the JSON string into an object with a structure. Then you can do queries on it. You can't query it while it's in string format. The string format is just for data transfer and storage. If you want to actually use the data then you have to deserialise it first. I'm not sure why you are convinced you don't need to do that? But since you appear to be serialising it from a DataTable, it would make sense to do the counting while it's still a DataTable, before you serialise it. — ADyson, Feb 27 '19 at 10:11
Why don't you get the values from the *DataTable*? It's easier than deserializing the string back into an object form to get the data — Panagiotis Kanavos, Feb 27 '19 at 10:18
@PanagiotisKanavos Do U mean I have to select the `Count` field only from the datatable then use my converter method ? — Anyname Donotcare, Feb 27 '19 at 10:20
BTW JavascriptSerializer is obsolete and can't even produce dates properly. Everyone, including ASP.NET Web API and ASP.NET Core uses JSON.NET — Panagiotis Kanavos, Feb 27 '19 at 10:21
@PanagiotisKanavos I'm so restricted to .net 3.5 in this project :( — Anyname Donotcare, Feb 27 '19 at 10:22
@AnynameDonotcare So what? JSON.NET works on .NET 2.0 and above. Did you check the [documentation](https://www.newtonsoft.com/json/help/html/Introduction.htm)? — ADyson, Feb 27 '19 at 11:28

ThePerplexedOne · Accepted Answer · 2019-02-27T10:26:00.613

3

Take a look at Newtonsoft.JSON.

Using this, we can create an object to match the json string:

public class MyCustomObject 
{
    [JsonProperty("grd_symbol")] public string GridSymbol {get; set;}
    [JsonProperty("count")] public double Count {get; set;}
    [JsonProperty("code")] public int Code {get; set;}
    [JsonProperty("name")] public string Name {get; set;}
    [JsonProperty("batch_no")] public int BatchNumber {get; set;}
}

Then you can deserialize your json using the library mentioned above:

var myData = JsonConvert.DeserializeObject<MyCustomObject[]>(jsonString);

And then, since you want an array of the counts, you can use LINQ to get them using Select:

var countArray = myData.Select(x => x.Count);

And of course, if you want to output this as a string, you can just serialize it again:

var countString = JsonConvert.SerializeObject(countArray);

edited Feb 27 '19 at 10:26

answered Feb 27 '19 at 10:11

ThePerplexedOne

2,920
15
30

I edit my question, and illustrate the converter method, Could U take a look plz, the output is a result of converting `datatable to string` – Anyname Donotcare Feb 27 '19 at 10:14
2

And I gave you an answer. You can't just convert your data to a string and then perform selections without some really, really ugly code. You're better off creating objects for your data, as I've demonstrated. – ThePerplexedOne Feb 27 '19 at 10:17
2

@AnynameDonotcare if you already have a DataTable with the data you want there's no reason to try to retrieve the data from the *JSON* string – Panagiotis Kanavos Feb 27 '19 at 10:20

Drag and Drop · Answer 2 · 2019-02-27T10:34:16.420

1

You don't even have to to deserialise all property to select just one you can simple omit the unwanted one in the calss definition so they will be ignored.

Using :

public class SimplifyRootObject
{
    public double count { get; set; }
}

Instead of :

public class RootObject
{
    public string grd_symbol { get; set; }
    public double count { get; set; }
    public int code { get; set; }
    public string name { get; set; }
    public int batch_no { get; set; }
}

Then a simple string.Join() to add the comma and there you are.

$"[{string.Join("_separator_", myListOfDouble)}]"

MCVE:

public static void Main(string[] args)
{
    string input = @"[
   {
      ""grd_symbol"":""A+"",
      ""count"":21.23,
      ""code"":4,
      ""name"":""X"",
      ""batch_no"":760
   },
   {
      ""grd_symbol"":""A "",
      ""count"":11.93,
      ""code"":4,
      ""name"":""X"",
      ""batch_no"":760
   },
   {
      ""grd_symbol"":""A-"",
      ""count"":8.49,
      ""code"":4,
      ""name"":""X"",
      ""batch_no"":760
   }
]";
    // Deserialize All, select the wanted.
    var m = JsonConvert.DeserializeObject<List<RootObject>>(input);            
    var result = m.Select(x=> x.count);
    Console.WriteLine("["+string.Join(", ", result)+"]");


    // Deserialize only one, select it.
    var m2 = JsonConvert.DeserializeObject<List<SimplifyRootObject>>(input);            
    var result2 = m.Select(x=> x.count);
    Console.WriteLine("["+string.Join(", ", result2)+"]");

    }
}

https://rextester.com/PFJZN10272;

edited Feb 27 '19 at 10:34

answered Feb 27 '19 at 10:27

Drag and Drop

2,672
3
25
37

Okay first of all, why would you do a string join, when you have the json library referenced and can just serialize the array? Second of all, why are you creating two separate root objects only to then select the same thing? You're better off just creating the entire object and then selecting what you want. – ThePerplexedOne Feb 27 '19 at 10:34
@ThePerplexedOne, 1/. "why are you creating two separate root objects": I use 2 for the sake of showing that they have the same result. You may want to avoid create useless property to avoid big chunk of data getting load in memory. A better way would be using linQ2Json to select only one token directly. Sometimes creating the entire object will result in an overflow. – Drag and Drop Feb 27 '19 at 10:38
If they were useless properties, why would he select them in the first place? It's a matter of consistency, at least for me. – ThePerplexedOne Feb 27 '19 at 10:39
@ThePerplexedOne, and for the string concatenation. Op insisted for having the result as a string. Yes serialisation will give that exact result. but I found the expected result unclear so i went for something more adaptable. – Drag and Drop Feb 27 '19 at 10:40
@ThePerplexedOne, for exemple using Google.Api you can get an incredibly big Json result with photo and stuff. Most of the time you don't care about the photo. Having it in the object property will make a 1Go ram every time. But I see your point, but most of the time we don't have controle over the Json we consume. – Drag and Drop Feb 27 '19 at 10:42
I understand where you're coming from, I also omit properties for large objects simply because I know I'll never use or need them. But in OP's case, the data is fairly small anyway and he may very well need those other properties. – ThePerplexedOne Feb 27 '19 at 10:45
I don't even know where op is going. This answer doesn't predate yours. I just took more time with RexTester.com as the only EDI I had. And format and pasting is hard on tablet. The onglet were I posted on wasn't refresh.. – Drag and Drop Feb 27 '19 at 10:49

score 0 · Answer 3 · answered Feb 27 '19 at 10:33

The label of your topic is "how to do a projection on json string" and you're telling us that you already have a DataTable that you want to convert into a string.

The code you gave us though means "I want to serialize my datatable into a JSON string".

In the end, what do you even need ? An array of string ? a string ? a json object ?

If it's a string, i would suggest you to override the ToString() method of DataTable to get the format you want i.e [number1, number2, number3] by fetching the datatable and returning a STRING and not a JSON.

Edit. @ThePerplexedOne has answered the question, i think.

How to do a projection on json string

3 Answers3