extract some data from JSON string to an array or a table

[Erzo66]

I have data in a JSON string with the following organization (To give you an example, I have formatted to usual JSON format but I get it formatted as a single line of string)

[
{
"id": 60237,
"search_keywords": [ "keyword1", "keyword2", "keyword3" ],
"categories": [
{ "name": "name1" },
{ "name": "name2" }
],
"name": "name1",
"price": 0,
"flag": "FR",
"country": "France",
"location": {
"lat": 00.000000,
"long": 0.000000
}
}
]

I would like to extract, from the JSON string, data from all fields but only some rows depending on following conditions :
- Rows from a countries list (list of values from fields "flag" and "country").
- Rows until a defined price (field "price") and eventually, if possible, only the 10 lowest prices
- Rows from a list of categories (list of values from field "categories")
- Rows from a keyword lists (list of values from field "search_keywords")

The table result must be sorted by prices from lowest to highest.

But, I am beginner in data management. I have already extracted data from mdb file with SQL command to generate ADO recordset. But here, the data are in JSON string which is the result from a httprequest on a website and they have to be converted into an array or a table. I have make a lot of search on Internet but I have not found the solution…

I think that SQL command would be the solution using the OPENJSON command but I don’t know how extract data from a string instead of a file( https://docs.microsoft.com/en-us/sql...n-transact-sql). The result could be under the form of an array or into a table recordset ADO recordset. I would like to avoid to save data into a file for fast process.

Could you help me ? I am developing with VB NET into Visual Studio 2017 community version.

Thank you for your help

[PlausiblyDamp]

If you are getting this data from a call to a website do they have any documentation of the format the data is returned in? If it is a published API they might provide a swagger definition that could be a big help.

If you know the format then you could define a class (or classes) to hold the data and use Json.net to parse the json directly into these classes, that would then allow you to use Linq to query, sort and generally manipulate it without you having to deal with it in the raw format.

[Sitten Spynne]

Hmm. I'm not sure how close I can get you, if you have an exceptionally large data set you should import this JSON into some form of database for further manipulation. But if it's smallish, you can probably fit the whole thing in memory and maybe even tolerate doing searching/filtering on that in-memory structure.

I strongly suggest using the NuGet package Netwonsoft.Json. It has facilities to parse JSON into .NET types, and is widely used in the professional world. Microsoft has a JSON parser in the bowels of .NET, but when it released it had several strange behaviors and as a result most people don't trust it anymore. If you're not sure how to add a NuGet package, you can ask about it.

The reason it's nice to use that is because you can define a class hierarchy that looks like your JSON, then parse it all in one go. For some reason "make a class" intimidates newer programmers, but it's no big deal. For some reason some experts like to tell newbies making classes is "advanced". I think that's like telling kids vegetables taste bad: it's rubbish, opinionated, and counterproductive.

JSON is pretty easy to re-visualize as a class. The rules are simple:

• Any JSON object type has to correspond to some .NET object type.
• A JSON array can be parsed to either a .NET array or a List(Of T), but the array must contain the same type of object. (In JavaScript, you can technically put multiple types in one array but .NET doesn't support it.)
• A JSON string property is a .NET String property.
• A JSON number property can be a .NET number property, meaning Integer or Double usually.
• A JSON object property should be of the appropriate .NET type.
• A JSON array property should be of the appropriate array type.

So the JSON you posted is an array of something. Let's figure out what that something is.

The something has several properties. "id" is a number. "search_keywords" is an array of Strings. "categories" is an array of some other object, each with a "name" property that is a String". And so on. We end up with something like this:

Public Class Entry
    Public Property id As Integer
    Public Property search_keywords() As String
    Public Property categories As Category()
    Public Property name As String
    Public Property price As Double
    Public Property flag As String
    Public Property location As Coordinate
End Class

Public Class Category
    Public Property name As String
End Class

Public Class Coordinate
    Public Property lat As Double
    Public Property long As Double
End Class

Given that, parsing out the full array with Newtonsoft is easy. Assuming you already have the string in a variable named 'json':

Dim entries() As Entry = JsonConvert.DeserializeObject(Of Entry())(json)

Once you do that, you'll have the JSON converted to these objects in an array in memory. If it's only 10-15MB of data, that's fine. If it's 100MB or more of data, this probably isn't the best way to go about that and you should consider importing it to a database via similar code.

The nice thing about objects is we have LINQ to query them, and years of experience on the internet at sorting and filtering them. We could convert it to a DataTable/DataSet, but people tend to do that because "it's what I know", not because it has an intrinsic advantage. Most of the places where you say "rows" you should be saying some kind of object name. VB is an object-oriented programming language, not a row-oriented programming language.

So for each of your requests, you might accomplish it several ways, but here's examples.

'flag' and 'country' values from a country list

Dim flagsAndCountries = From entry In entries
                        Select New With {.Flag = entry.flag, .Country = entry.country}

For Each item In flagsAndCountries
    Console.WriteLine("{0}: {1}", item.Flag, item.Country)
Next

Entries until a defined price, and 10 lowest
(I'm assuming you mean the 10 lowest before reaching the threshold.)

Dim desiredPrice As Double = 10
Dim entriesUntilPrice = entries.TakeWhile(Function(e) e.price <= desiredPrice)
Dim tenLowest = From entry In entriesUntilPrice
                Order By entry.price Ascending
                Take 10

Entries with certain categories

Dim desiredCategories = {"name1", "name3"}
Dim matchingEntries = entries.Where(Function(e)
                                        For Each category In e.categories
                                            If desiredCategories.Contains(category.name) Then
                                                Return True
                                            End If
                                        Next
                                        Return False
                                    End Function)

This one's a little tricky, and I might spend an hour or two trying more clever solutions. What this says is, "Find me the entries where this function returns true." In this context, the function says, "I will return true if any of this item's category names are in the list of desired categories."

A more clever approach would involve making a lookup table of sorts, but for a one-shot query there's not a lot of reason to do all that work. I don't think there's an easy LINQ way, so it'd end up looking a little scary despite being really trivial. I think the LINQ Join statement might help here but I'm not super good with it.

Rows from a keyword list
This is basically the same as the last example, just slightly less complex because it's comparing "an array of strings" to "an array of strings". I might try:

Dim desiredKeywords = {"keyword1", "keyword2"}
Dim matchingEntries = entries.Where(Function(e) e.search_keywords.Intersect(desiredKeywords).Any())

This says, "Find me the entries where, if I make a new array with just the items the desired keywords and this entry's keywords have in common, that array has at least one item." That is the same thing as "Find me an entry with at least one of these keywords".

In fact, armed with that, I could revisit the last problem:

Dim desiredCategories = {"name1", "name3"}
Dim getCategories = Function(e As Entry)
                        Return e.categories.Select(Function(c) c.name)
                    End Function

Dim matchingEntries = entries.Where(Function(e) getCategories(e).Intersect(desiredCategories).Any())

The getCategories variable represents a function that, given an Entry, will return the list of category names. So then we can say, "Find me the entries where at least one category name matches these category names."

These proposed solutions aren't the only way to accomplish the task, and in some ways won't be the best.

Now, you said you were a beginner, so maybe you don't want to dive into LINQ.

If you have an object model like the above, getting that into a database is super easy, so long as you understand there's more to databases and .NET than TableAdapters. Many packages exist to go straight from objects to tables, but it's also trivial to write that code yourself.

Ask questions!

[Shaggy Hiker]

I think that Sitten has given his typically thorough answer, but I'll just add that there is a somewhat smaller way to do this with a bit of work. You can always bring JSON into a Dictionary(of String, Object). This can be done with just a line or two. The advantage is that dictionaries are simple, and no third party libraries are needed. The drawback is that Object part. A JSON object can be all kinds of things. You have nodes containing values and nodes containing arrays of strings, but you could also have nodes containing JSON objects or arrays of JSON objects. All of this can be handled, if needed, but it quickly becomes too problematic. As long as your JSON object is simple, then this kind of parsing is quick and easy. Objects containing just key value pairs, or objects with values that are simple arrays are both quite easy. Once your JSON starts containing JSON objects, you are generally going to find it a bit easier to do what Sitten described. You may well find it easier to do that right from the start, too, because it's a good way to go. I only add this because you probably should know that there are at least three different alternatives. Sitten has shown one, parsing into Dictionary(of String, Object) is a second, and the third is too ugly to even warrant consideration, but for the sake of completeness, it's just the fact that you are seeing a string and a string can be parsed using string manipulation.

[Erzo66]

I would like to thank you very much for your help and the time you have spent to explain me what were the different ways to solve my problem (and a lot for Sitten Spynne details). It have help me to start. I finally choose the classes way, even if as Spynne has said, as a lot of beginners, I was before afraid about it.