0

I'm using the following code in VB.Net to fetch a set of time and temperature data in JSON format that I will end up charting. I want to convert the JSON data but I only get far as creating the jObject. After that, I get lost. I'll admit, I'm a bit of a newbie!

Imports System.Collections.Generic
Imports System.IO
Imports System.Net
Imports Newtonsoft.Json
Imports Newtonsoft.Json.Linq

Partial Class public_html_JSON
    Inherits System.Web.UI.Page
    Public jResults As JObject
    Public rawresp As String
    Public strStartTime As String = ""
    Public strEndTime As String = ""
    Public rangeMinutes As Long
    Public debugText As String


    Private Sub form1_Load(sender As Object, e As EventArgs) Handles form1.Load

    Dim request As HttpWebRequest
    Dim response As HttpWebResponse = Nothing
    Dim reader As StreamReader
    Dim cstZone As TimeZoneInfo = TimeZoneInfo.FindSystemTimeZoneById("Central Standard Time")
    Dim cstTime As DateTime = TimeZoneInfo.ConvertTimeFromUtc(DateAdd(DateInterval.Day, 0, DateAdd(DateInterval.Hour, 0, Now().ToUniversalTime)), cstZone)request =        

    Dim url As String = "https://[somesite]?method=queryList4Chart&device.id=17002&endTime=" + strEndTime + "&sensorNumber=-1&startTime=" + strStartTime
    request = DirectCast(WebRequest.Create(url), HttpWebRequest)
    response = DirectCast(request.GetResponse(), HttpWebResponse)
    reader = New StreamReader(response.GetResponseStream())
    rawresp = reader.ReadToEnd()
    response = Nothing

    jResults = JObject.Parse(rawresp)

But before I send it to the client-side, I want to:

  1. Create a new JSON string using an "x, y" format with timeArray as "x" and dataArray[0] as "y".
  2. Reduce the number of data points by only keeping times with minutes divisible by 5. (i.e. 5,10,15,etc...)

Here is the data I want to transform:

   "dataArray":[ 
      [ 
         { 
            "value":13.4
         },
         { 
            "value":13.2
         },
         { 
            "value":13.2
         },
         { 
            "value":13.5
         }
      ],
      [ 
         { 
            "value":2.8
         },
         { 
            "value":2.8
         },
         { 
            "value":2.9
         },
         { 
            "value":3.0
         }
      ]
   ],
   "sensorArray":[ 
      "1.TP1(℃)",
      "2.TP2(℃)"
   ],
   "timeArray":[ 
      "2019/11/10 14:00:41",
      "2019/11/10 14:05:40",
      "2019/11/10 14:07:40",
      "2019/11/10 14:10:40"
   ]
}

And I need it in this format:

[ 
   { 
      "x":2019/11/10 14:00:00,
      "y":13.4
   },
   { 
      "x":2019/11/10 14:05:00,
      "y":13.2
   },
   { 
      "x":2019/11/10 14:10:00,
      "y":13.5
   }
]

How would I go about doing that?

2
  • One way to do it is to set up a class structure for the input data, deserialize into that structure, then transform into a class structure corresponding to the output data, then serialize that. Commented Nov 12, 2019 at 13:47
  • 1
    Hi Craig, I did just that! A bit more work than I had planned or thought there would be but it works. It takes about half the time to render the data now. Better than the 30 seconds it took when all the work was done client-side in Javascript. Thanks for pointing me in the right direction. I'll post my working code in my question above. Commented Nov 13, 2019 at 15:08

1 Answer 1

1

Thanks to Craig's suggestion, here's the final, working code!

Imports System.Collections.Generic
Imports System.IO
Imports System.Net
Imports Newtonsoft.Json

Partial Class KFPTempsV2
    Inherits System.Web.UI.Page
    Public strStartTime As String = ""
    Public strEndTime As String = ""
    Public rangeMinutes As Long
    Public debugText As String
    Public JSONxy As ArrayList = New ArrayList 'List of JSON strings

    Private Sub form1_Load(sender As Object, e As EventArgs) Handles form1.Load

        Dim request As HttpWebRequest
        Dim response As HttpWebResponse = Nothing
        Dim reader As StreamReader
        Dim cstZone As TimeZoneInfo = TimeZoneInfo.FindSystemTimeZoneById("Central Standard Time")
        Dim cstTime As DateTime = TimeZoneInfo.ConvertTimeFromUtc(DateAdd(DateInterval.Day, 0, DateAdd(DateInterval.Hour, 0, Now().ToUniversalTime)), cstZone)
        Dim DeviceList As IEnumerable(Of TemperatureDevice) = GetTemperatureDevices()
        Dim rawResp As String
        Dim url As String = ""
        Dim objXY As New List(Of List(Of XY))

        rangeMinutes = 1440
        strStartTime = DateAdd(DateInterval.Minute, -rangeMinutes, cstTime).ToString("MM/dd/yyyy\%20HH:mm:00")
        strEndTime = cstTime.ToString("MM/dd/yyyy\%20HH:mm:ss")

        'Get data from each device
        For Each dv As TemperatureDevice In DeviceList
            url = "https://www.[somesite].com/deviceDataAction.do?method=queryList4Chart&device.id=" & dv.ID & "&endTime=" + strEndTime + "&sensorNumber=-1&startTime=" + strStartTime
            request = DirectCast(WebRequest.Create(url), HttpWebRequest)
            response = DirectCast(request.GetResponse(), HttpWebResponse)
            reader = New StreamReader(response.GetResponseStream())
            rawResp = reader.ReadToEnd()
            response = Nothing

            Dim XYList As List(Of XY) = ConvertToXY(JsonConvert.DeserializeObject(Of JSONData)(rawResp))

            JSONxy.Add(JsonConvert.SerializeObject(XYList))
            objXY.Add(XYList) 'Store XYList for later use
        Next

        'Populate Current/H/L temperatures
        Current1.InnerHtml = objXY(0)(objXY(0).Count - 1).y
        High1.InnerHtml = MaxValue(objXY(0)).ToString
        Low1.InnerHtml = MinValue(objXY(0)).ToString

        Current2.InnerHtml = objXY(1)(objXY(1).Count - 1).y
        High2.InnerHtml = MaxValue(objXY(1)).ToString
        Low2.InnerHtml = MinValue(objXY(1)).ToString

        Current3.InnerHtml = objXY(2)(objXY(2).Count - 1).y
        High3.InnerHtml = MaxValue(objXY(2)).ToString
        Low3.InnerHtml = MinValue(objXY(2)).ToString

        Current4.InnerHtml = objXY(3)(objXY(3).Count - 1).y
        High4.InnerHtml = MaxValue(objXY(2)).ToString
        Low4.InnerHtml = MinValue(objXY(3)).ToString

    End Sub

    Function ConvertToXY(obj As JSONData) As List(Of XY)

        Dim NewObj As List(Of XY) = New List(Of XY)

        For i As Int16 = 0 To obj.DataArray(1).Length - 1
            Dim oDate As DateTime = Convert.ToDateTime(obj.TimeArray(i))
            Dim oNewDate As DateTime = New DateTime(oDate.Year, oDate.Month, oDate.Day, oDate.Hour, oDate.Minute, 0).AddMinutes(Math.Round(oDate.Second / 60))
            If oNewDate.Minute Mod 5 = 0 Then
                Dim objXY As XY = New XY
                objXY.x = oNewDate.ToString("yyyy/MM/dd HH:mm:ss")
                objXY.y = obj.DataArray(1)(i).Value
                NewObj.Add(objXY)
            End If
        Next

        Return NewObj

    End Function

    Function MinValue(oList As List(Of XY)) As String

        Dim sglMin As Single = 200

        For Each row As XY In oList
            Try
                If CSng(row.y) < sglMin Then
                    sglMin = CSng(row.y)
                End If
            Catch ex As Exception

            End Try
        Next

        Return sglMin.ToString("F1")

    End Function

    Function MaxValue(oList As List(Of XY)) As String

        Dim sglMax As Single = -200

        For Each row As XY In oList
            Try
                If CSng(row.y) > sglMax Then
                    sglMax = CSng(row.y)
                End If
            Catch ex As Exception

            End Try
        Next

        Return sglMax.ToString("F1")

    End Function

    Public Class JSONData

        Public Property DataArray As DataArray()()
        Public Property SensorArray As String()
        Public Property TimeArray As String()

    End Class

    Public Class DataArray

        Public Property Value As String

    End Class

    Class XY 'Important that xy field names be lowercase

        Property x As String
        Property y As String

    End Class

    Public Class TemperatureDevice

        Public Name As String
        Public ID As String
        Public DatasetNum As Int16

        Public Sub New()
        End Sub

        Public Sub New(ByVal _name As String,
                       ByVal _id As String,
                       ByVal _datasetNum As Int16
                       )
            Name = _name
            ID = _id
            DatasetNum = _datasetNum
        End Sub

    End Class

    Private Function GetTemperatureDevices() As IEnumerable(Of TemperatureDevice)

        'Dataset choices TP1=0 Or TP2=1
        Return New List(Of TemperatureDevice) From
            {
             New TemperatureDevice("Pump House", "17002", 1),
             New TemperatureDevice("Planer", "7199", 1),
             New TemperatureDevice("Sawmill", "7123", 1),
             New TemperatureDevice("Wellons", "13293", 1)
             }

    End Function

End Class
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.