How to find particular json element from txt file in c# using xpath/jsonpath?

Question

I have a lot of JSON format data into text file and format is like:

[{"ev":"AM","sym":"TMHC","v":1000,"av":74917,"op":18.92,"vw":19.1305,"o":19.13,"c":19.15,"h":19.15,"l":19.13,"a":19.143,"z":90,"n":1,"s":1549380300000,"e":1549380360000},{"ev":"AM","sym":"SPYG","v":7103,"av":184266,"op":35.27,"vw":35.3148,"o":35.3264,"c":35.34,"h":35.34,"l":35.3258,"a":35.3345,"z":710,"n":1,"s":1549380300000,"e":1549380360000},
{"ev":"AM","sym":"AAPL","v":73,"av":1866,"op":35.27,"vw":35.3148,"o":35.3264,"c":35.34,"h":35.34,"l":35.3258,"a":35.3345,"z":710,"n":1,"s":1549380300000,"e":1549380360000}]

So I need to find json element of particular symbol. Like if I use AAPL then it gives us all AAPL element data from txt file. Like

{"ev":"AM","sym":"AAPL","v":73,"av":1866,"op":35.27,"vw":35.3148,"o":35.3264,"c":35.34,"h":35.34,"l":35.3258,"a":35.3345,"z":710,"n":1,"s":1549380300000,"e":1549380360000}

So please can you help me to how should I make it ?

static void xPathUsing()
{
  const string filePath = @"D:\Aggregate_Minute_AAPL.txt";
  using (StreamReader r = new StreamReader(filePath))
  {
      string json = r.ReadToEnd();
  }
}

Why not just [deserialize the JSON](https://www.newtonsoft.com/json/help/html/SerializingJSON.htm) and use linq? — Fildor, Feb 18 '19 at 14:34
I need to find data for 10,000 symbols and it's lots time consumable so I need to use jsonpath. — Hardik Dhankecha, Feb 18 '19 at 14:37
Is that a one-time thing or do you need to perform multiple operations on the data? Where I want to go: Maybe it would be an option to import those into a DB (for example MongoDB) and use the according API? — Fildor, Feb 18 '19 at 14:40
Yes I need to perform multiple operations on that and so that I need to maintain files instead of db. — Hardik Dhankecha, Feb 18 '19 at 14:42
there are total 10,669 symbols and I need to process on all simultaneously so I am handling files. — Hardik Dhankecha, Feb 18 '19 at 14:43

score 0 · Answer 1 · answered Feb 18 '19 at 15:30

As you need to run multiple operations on the data, the best way would be to read the data into memory once (or again whenever the data changes) and hold them in a way that supports fast querying. In order to find a row by the symbol, you could create a dictionary that holds the data.

The following samples use the widely used JSON.NET library for parsing the JSON.

First, you need to define a class that ressembles the schema of your JSON objects, e.g.:

class Row
{
    [JsonProperty("sym")]
    public string Symbol { get; set; }
    public decimal vw { get; set; }
    // ...
}

Please note that you can use the JsonProperty attribute in order to be able to assign different names to the properties of the class than in the JSON.

The following sample shows how to read the data from a file and how to convert it to a dictionary:

var input = File.ReadAllText("C:\\Temp\\abc.json");
var rows = JsonConvert.DeserializeObject<IEnumerable<Row>>(input);
var dict = rows.ToDictionary(x => x.Symbol, x => x, StringComparer.OrdinalIgnoreCase);

After preparing your dictionary once, you can get the corresponding row by using the indexer:

var aaplRow = dict["AAPL"];

How to find particular json element from txt file in c# using xpath/jsonpath?

1 Answers1