none
How to parse Json data with many variables? RRS feed

  • Question

  • How can I parse Json code with differents variable but with the same id. I'll explain, Im new using Json, so I want to parse :

    "title": {
       "runs": [
         {
       "text": "Peter Tosh - Legalize It"
         }
       ],
       "accessibility": {
         "accessibilityData": {
       "label": "Peter Tosh - Legalize It by Bondade é Nosso Hábito 8 years ago 4 minutes, 46 seconds"
         }
       }
     },
     "index": {
       "simpleText": "1"
     },
     "shortBylineText": {
       "runs": [
         {
       "text": "Bondade é Nosso Hábito",
       "navigationEndpoint": {
         "clickTrackingParams": "CD0QxjQYACITCMn2heSTz-wCFUmw1Qod1hMDdw==",
         "commandMetadata": {
           "webCommandMetadata": {
             "url": "/user/Bruno12170",
             "webPageType": "WEB_PAGE_TYPE_CHANNEL",
             "rootVe": 3611
           }
         },
         "browseEndpoint": {
           "browseId": "UCY1IJY2IYNVfD7R-JbgQGbQ",
           "canonicalBaseUrl": "/user/Bruno12170"
         }
       }
         }
       ]
     },
     "lengthText": {
       "accessibility": {
         "accessibilityData": {
       "label": "4 minutes, 46 seconds"
         }
       },
       "simpleText": "4:46"
     },
     "navigationEndpoint": {
       "clickTrackingParams": "CD0QxjQYACITCMn2heSTz-wCFUmw1Qod1hMDdzIKcGxwcF92aWRlb1okVkxQTDRfRHg4OGRwdTdlcGZINnlid3FKcGY5dUwydEFsMzY4mgEDEPos",
       "commandMetadata": {
         "webCommandMetadata": {
       "url": "/watch?v=j6QkVTx2d88&list=PL4_Dx88dpu7epfH6ybwqJpf9uL2tAl368&index=1",
       "webPageType": "WEB_PAGE_TYPE_WATCH",
       "rootVe": 3832
         }

    What I want to parse from this is:

    "text": "Peter Tosh - Legalize It"

    "url": "/watch?v=j6QkVTx2d88&list=PL4_Dx88dpu7epfH6ybwqJpf9uL2tAl368&index=1

    How can I parse them in a page where there are many elements(songs)? How can I use For each to do so? Thanks

    Sunday, October 25, 2020 4:26 PM

Answers

  • The first code should not error out. I Mr. Obvious did not re-post your required first part (make some better efforts here).

    So the code to list of the next page token, and the titles would look like this:

           Dim wc As New Net.WebClient
            Dim incstr As IO.Stream = Nothing
            wc.Encoding = System.Text.Encoding.UTF8
            incstr = wc.OpenRead("https://www.googleapis.com/youtube/v3/playlistItems?part=contentDetails%2Csnippet&maxResults=50&playlistId=PL4_Dx88dpu7cEY_cBjTZFFM1tVKF5Plsx&key=AIzaSyAJLLCFuQLEE6LFjwwPCxvo9T6DC3nLNPs")
    
            Dim strbuf As String = ""
            Using rd As New IO.StreamReader(incstr)
                strbuf = rd.ReadToEnd
            End Using
    
            Dim myJSON As New Json.Linq.JObject
            myJSON = Json.Linq.JObject.Parse(strbuf)
    
            Debug.Print("next page token = " & myJSON.SelectToken("nextPageToken").ToString)
    
            ' list out title
            For Each myitem As Json.Linq.JToken In myJSON.SelectToken("items")
                Debug.Print("Title  = " & myitem.SelectToken("snippet.title").ToString)
            Next

    Output:

    next page token = CDIQAA Title = Agnes - Release Me (Official Music Video) Title = Atlus - You're a F**king B*tch Hope You Know That Sh*t (Official Music Video) Title = Machine Gun Kelly - Glass House (feat. Naomi Wild) [Official Music Video] Title = Ligabue Quella che non sei Title = Aaron Smith - Dancin (KRONO Remix) Title = Machine Gun Kelly ft. blackbear - my ex's best friend (Official Music Video) Title = Jack Trades - Kill Me Slowly (Lyrics) ft. Heather Janssen Title = Mac Miller - Nikes On My Feet Title = dj poolboi - I Had It All Title = Generale

    etc. etc. etc. etc.

    So next page token no doubt allows you to get the next 50. But you have to go check/read up/look at/see the docs for the API/make a effort here for that information. 

    Regards,

    Albert D. Kallal (Access MVP 2003-2017)

    Edmonton, Alberta Canada

    • Marked as answer by Mattia Fanti Tuesday, October 27, 2020 12:01 AM
    Monday, October 26, 2020 11:58 PM

All replies

  • Did you already choose and include some JSON library or classes to your code? The solution depends on used JSON engine.

    Sunday, October 25, 2020 7:13 PM
  • Hi Viorel, I'm  open to any kind of idea :)
    I'm trying to download the source of this and parse it
    Link here: Link where there is the list of all the songs I want to extract title and url.. Thanks

    Since it's the first time I am using Json, the first thing I downloaded is newtonsoft.json, but I repeat it, Any kind of library or class that is working is okay to me .

    Sunday, October 25, 2020 7:16 PM
  • The solution also depends on kind of your program: is it a Forms using .NET Framework, or .NET Core, etc.?

    Sunday, October 25, 2020 7:19 PM
  • I am using Windows Form with .Net Framework, thanks
    Sunday, October 25, 2020 7:39 PM
  • Dear  Viorel, I was thinking to use youtube Api, but now I m realizing I cant display more than 50 elements in the page, so this is creating a problem for bigger playlists. Also if youtube api got nextpageToken, I think it is a bit of mess, atleast to me.

    So the link where I am going to download html, extract json and parse Json is simply the playlist itself Playlist

    Sunday, October 25, 2020 7:47 PM
  • It seems like you must first solve a different problem: downloading JSON or another format. Then you can return to the problem of parsing.

    Sunday, October 25, 2020 7:58 PM
  • You are endeed right, but trying to look online, it seems that all examples are already downloading a url.json.

    In my case I don't have a link like that, but a simple playlist. 

    I was thinking with something like this: 

     Dim wc As New Net.WebClient
            Dim incstr As IO.Stream = Nothing
    
            wc.Encoding = Encoding.UTF8
            incstr = wc.OpenRead("https://www.youtube.com/playlist?list=PL4_Dx88dpu7epfH6ybwqJpf9uL2tAl368")
    
            Using rd As New IO.StreamReader(incstr)
                Dim htmlText As String = rd.ReadToEnd
                Dim jsonPtn As String = "\{(?:[^\{\}]|(?<o>\{)|(?<-o>\}))+(?(o)(?!))\}"
            Dim input As String = htmlText.Substring(htmlText.IndexOf("text"))
            Dim match As Match = Regex.Matches(input, jsonPtn, RegexOptions.Multiline Or RegexOptions.IgnoreCase)(0)
            Dim jsonText As String = match.Groups(0).Value
                Dim jsonObj = JObject.Parse(jsonText)
                RichTextBox1.Text = jsonObj

    But something is missing.. Would you like to suggest me some example? I scraped all google first page result, but as I said, all the example are not extracting json format from html.


    edit1: If the situation gets complicated, I was trying to scrap html and get value with:
    Dim textafter As String = """,""webPageType"        
    Dim textbefore As String = """url"":""/watch?v="
            Dim startPosition As Integer = RichTextBox1.Text.IndexOf(textbefore)
            startPosition += textbefore.Length
            Dim endPosition As Integer = RichTextBox1.Text.IndexOf(textafter, startPosition)
            Dim textFound As String = RichTextBox1.Text.Substring(startPosition, endPosition - startPosition)
            RichTextBox1.AppendText(textFound)

    it will return the first song link.
    I can maybe pass it through a for each(?) and get all of them?
    Thanks
    Sunday, October 25, 2020 8:17 PM
  • Well, the first problem is that json is ill-formed. I am hoping that your data is wrong, or messed up.

    There are some brackets etc. missing.

    if you paste that json into any on-line jason parser - it fails.

    Assuming the json is like this:

    {
    	"title": {
    		"runs": [{
    			"text": "Peter Tosh - Legalize It"
    		}],
    		"accessibility": {
    			"accessibilityData": {
    				"label": "Peter Tosh - Legalize It by Bondade é Nosso Hábito 8 years ago 4 minutes, 46 seconds"
    			}
    		}
    	},
    	"index": {
    		"simpleText": "1"
    	},
    	"shortBylineText": {
    		"runs": [{
    			"text": "Bondade é Nosso Hábito",
    			"navigationEndpoint": {
    				"clickTrackingParams": "CD0QxjQYACITCMn2heSTz-wCFUmw1Qod1hMDdw==",
    				"commandMetadata": {
    					"webCommandMetadata": {
    						"url": "/user/Bruno12170",
    						"webPageType": "WEB_PAGE_TYPE_CHANNEL",
    						"rootVe": 3611
    					}
    				},
    				"browseEndpoint": {
    					"browseId": "UCY1IJY2IYNVfD7R-JbgQGbQ",
    					"canonicalBaseUrl": "/user/Bruno12170"
    				}
    			}
    		}]
    	},
    	"lengthText": {
    		"accessibility": {
    			"accessibilityData": {
    				"label": "4 minutes, 46 seconds"
    			}
    		},
    		"simpleText": "4:46"
    	},
    	"navigationEndpoint": {
    		"clickTrackingParams": "CD0QxjQYACITCMn2heSTz-wCFUmw1Qod1hMDdzIKcGxwcF92aWRlb1okVkxQTDRfRHg4OGRwdTdlcGZINnlid3FKcGY5dUwydEFsMzY4mgEDEPos",
    		"commandMetadata": {
    			"webCommandMetadata": {
    				"url": "/watch?v=j6QkVTx2d88&list=PL4_Dx88dpu7epfH6ybwqJpf9uL2tAl368&index=1",
    				"webPageType": "WEB_PAGE_TYPE_WATCH",
    				"rootVe": 3832
    			}
    		}
    	}
    }

    So there ARE some brackets missing. Withi above json? then this works:

    (do a nuget for the newtonsoft json library. You can do this:

    Dim strBuf As String = File.ReadAllText("c:\test5\json.txt") Dim myjobj As New Json.Linq.JObject myjobj = Json.Linq.JObject.Parse(strBuf)

      Debug.Print("text = " & myjobj.SelectToken("title.runs(0).text").ToString)
            Debug.Print("label  = " & myjobj.SelectToken("title.accessibility.accessibilityData.label").ToString)
            Debug.Print("URL  = " & myjobj.SelectToken("navigationEndpoint.commandMetadata.webCommandMetadata.url").ToString)

    Output:

    text = Peter Tosh - Legalize It
    label  = Peter Tosh - Legalize It by Bondade é Nosso Hábito 8 years ago 4 minutes, 46 seconds
    URL  = /watch?v=j6QkVTx2d88&list=PL4_Dx88dpu7epfH6ybwqJpf9uL2tAl368&index=1


    So, the above will work - but fingers crossed that the bad json data you posted was a cut + paste error.

    So if you have well formed json, then parsing it as per above is easy, but the json you posted is messed up, and is missing some closing } in that text.

    Regards,

    Albert D. Kallal (Access MVP 2003-2017)
    Edmonton, Alberta Canada


    Monday, October 26, 2020 1:45 AM
  • Hi Albert, the bad Json was my mistake copying it from the source code of youtube playlist.

    However, I choose to use YouTube Api in order to get them because It will give me back the json straight. 

    Unfortunately, the limiti is 50 elements for a page, so setting the maxResults to a value > 50, then add pageToken=[Next Page Token String] to the next query if one is provided in the JSON. Repeat while the objects retrieved is < totalResults). 

    The problem is that I can't see and I don't know either how to take the Next Page Token String, as Json doesn't show anything, The Api is ready with the playlist ID, maxresult=50 and my Api token. 

    https://www.googleapis.com/youtube/v3/playlistItems?part=contentDetails%2Csnippet&maxResults=50&playlistId=PL4_Dx88dpu7cEY_cBjTZFFM1tVKF5Plsx&key=AIzaSyAJLLCFuQLEE6LFjwwPCxvo9T6DC3nLNPs
    As you can see is giving back max 50 songs, and there is no Next Page Token I can use to take back all the others songs.. The playlist contains obviously more than 50 elements. 
    And more than this, if I download Json with:
     Dim wc As New Net.WebClient
            Dim incstr As IO.Stream = Nothing
            wc.Encoding = Encoding.UTF8
            incstr = wc.OpenRead("https://www.googleapis.com/youtube/v3/playlistItems?part=contentDetails%2Csnippet&maxResults=50&playlistId=PL4_Dx88dpu7cEY_cBjTZFFM1tVKF5Plsx&key=AIzaSyAJLLCFuQLEE6LFjwwPCxvo9T6DC3nLNPs")
    
            Using rd As New IO.StreamReader(incstr)
                RichTextBox1.Text = rd.ReadToEnd
            End Using
             Dim strBuf As String = (richtextbox1.text)
    
    
            Dim myjobj As New Json.Linq.JObject
            myjobj = Json.Linq.JObject.Parse(strBuf)
    
            dim titleText = JObject.Parse(json)("title")("runs")(0)("text")


    it will give me back an error on  tje last line as :  "System.ArgumentException: 'Can not convert Object to String.'" 

    I tried using Option Strict on, but it shows no error at design. 

    it would be nice anyway, to use a loop to retrieve all the songs in the playlist 50 at the time.

    Thanks

    Monday, October 26, 2020 5:39 PM
  • You not following the hiearchery that the data has.

    You can try this:

            Dim strbuf As String = ""
            Using rd As New IO.StreamReader(incstr)
                strbuf = rd.ReadToEnd
            End Using
    
            Dim myJSON As New Json.Linq.JObject
            myJSON = Json.Linq.JObject.Parse(strbuf)
    
            ' DataGridView1.DataSource = myJSON.SelectToken("items")
    
            TextBox1.Text = strbuf
    
            ' list out the songs.
            For Each myitem As Json.Linq.JToken In myJSON.SelectToken("items")
                ' Debug.Print(myitem.ToString)
                Debug.Print("video id = " & myitem.SelectToken("contentDetails.videoId").ToString)
                Debug.Print("Publised at = " & myitem.SelectToken("contentDetails.videoPublishedAt").ToString)
                Debug.Print("Descripiton = " & myitem.SelectToken("snippet.description").ToString)
            Next
    

    Note how as you "traverse" down the table information.

    the above produces this:

    video id = keYXzDh5JEQ
    Publised at = 2009-06-30 2:00:34 PM
    Descripiton = Taken from the album "Dan
    ------------
    video id = vQ5Q0h43M4I
    Publised at = 2020-09-30 2:21:25 PM
    Descripiton = Atlus - You're a F**king 
    ------------
    video id = RRcXtqamWWM
    Publised at = 2019-10-30 5:00:04 PM
    Descripiton = Machine Gun Kelly - Glass
    ------------

    And I do note in json, there is a Next token. (so that would allow you to get the next 50).

    The only real tip I can offer here? If you having to parse the json manually, then that is likely the wrong approach.

    You just have to get a bit more comfortable with JSON, and once you do, then this becomes quite easy.

    Even without each row parsing, you can drop a grid onto your form, and with ONE line of code do this:

            Dim myJSON As New Json.Linq.JObject
            myJSON = Json.Linq.JObject.Parse(strbuf)
    
            DataGridView1.DataSource = myJSON.SelectToken("items")
    

    And you get this:

    Gee - one line of code and I have a grid display! To be fair, I would do some row processing since some of the columns do need a bit more love and care. But all in all the above concepts and use of the Newtonsoft JSON library should allow you to slice and dice and get and use the data returned quite much just about anyway you desire.

    Regards,

    Albert D. Kallal (Access MVP 2003-2017)
    Edmonton, Alberta Canada

    Monday, October 26, 2020 9:19 PM
  • Hi Alberto, Thanks a lot for your help!
    Your first code gives back the error "Newtonsoft.Json.JsonReaderException: 'Error reading JObject from JsonReader. Path '', line 0, position 0"

    The second code rocks on, that's amazing! 
    But there is plenty of column that I am not going to need.
    In reality I just need the 

    snippet.title
    as the others are quite irrelevant to me..
    Would it be possible to extract just them with your second code?
    Also, How can I grab also 

    "nextPageToken": "CAUQAA",

    that is changing every time? WOuld it be possible to make a loop to extract all of them at once scrapping all the pages of the playlist?
    Thanks a lot,

    Mattia

    Monday, October 26, 2020 10:20 PM
  • The first code should not error out. I Mr. Obvious did not re-post your required first part (make some better efforts here).

    So the code to list of the next page token, and the titles would look like this:

           Dim wc As New Net.WebClient
            Dim incstr As IO.Stream = Nothing
            wc.Encoding = System.Text.Encoding.UTF8
            incstr = wc.OpenRead("https://www.googleapis.com/youtube/v3/playlistItems?part=contentDetails%2Csnippet&maxResults=50&playlistId=PL4_Dx88dpu7cEY_cBjTZFFM1tVKF5Plsx&key=AIzaSyAJLLCFuQLEE6LFjwwPCxvo9T6DC3nLNPs")
    
            Dim strbuf As String = ""
            Using rd As New IO.StreamReader(incstr)
                strbuf = rd.ReadToEnd
            End Using
    
            Dim myJSON As New Json.Linq.JObject
            myJSON = Json.Linq.JObject.Parse(strbuf)
    
            Debug.Print("next page token = " & myJSON.SelectToken("nextPageToken").ToString)
    
            ' list out title
            For Each myitem As Json.Linq.JToken In myJSON.SelectToken("items")
                Debug.Print("Title  = " & myitem.SelectToken("snippet.title").ToString)
            Next

    Output:

    next page token = CDIQAA Title = Agnes - Release Me (Official Music Video) Title = Atlus - You're a F**king B*tch Hope You Know That Sh*t (Official Music Video) Title = Machine Gun Kelly - Glass House (feat. Naomi Wild) [Official Music Video] Title = Ligabue Quella che non sei Title = Aaron Smith - Dancin (KRONO Remix) Title = Machine Gun Kelly ft. blackbear - my ex's best friend (Official Music Video) Title = Jack Trades - Kill Me Slowly (Lyrics) ft. Heather Janssen Title = Mac Miller - Nikes On My Feet Title = dj poolboi - I Had It All Title = Generale

    etc. etc. etc. etc.

    So next page token no doubt allows you to get the next 50. But you have to go check/read up/look at/see the docs for the API/make a effort here for that information. 

    Regards,

    Albert D. Kallal (Access MVP 2003-2017)

    Edmonton, Alberta Canada

    • Marked as answer by Mattia Fanti Tuesday, October 27, 2020 12:01 AM
    Monday, October 26, 2020 11:58 PM