0

I get a Json file from our data provider without any information about the datatype of the data inside. The Json comprises only the data and one entry looks like this (this is survey data and one entry is the data of one person):

{"data":["4482359","12526","2014 Company Y","2","3","1"]}

I deserialized the file and have it within an object now but all entries are strings. I want to import it into a database but each entry should have the datatype that it best fits to it. E.g. 1 is int16, 2014 Company Y is string. Acutally I have only Int16 and String, nothing more. For importing it into the database I acutally need the Sql Server datatype equivalent. Unfortunately I cannot hardcode the datatype into the Table Creation Command since the order of the data is not the same for each query. E.g. First I receive Survey 1 data with the structure shown above. But maybe a few days later I need different data like

{"data":["4482359","2","3","1","12526","2015 Company X"]}

Deserialized the Json Data I have an object (RootObject) with a List(of String) called data. Where the first string is "4482359" the second is "2" the third is "3" and so on.

Public Class RootObject
    Public Property data As List(Of String)
End Class

Public Class RootObjectDatatype
    Private _rootObject As RootObject

    Public Sub New(ByRef rootObject As RootObject)

        For Each str As String In rootObject.data
            If str > 0 And str < 50000 Then
                jsonDatatype.Add("Int")
            Else
                jsonDatatype.Add("Varchar(" & str.Count & ")")
            End If
        Next

    End Sub

    Public Property jsonDatatype As List(Of String)

End Class

But this does not work yet.

ruedi
  • 5,365
  • 15
  • 52
  • 88
  • Sounds like you need a different data provider and or tell them to restructure their JSON file they send out. It seems its inconsistent and missing keys that are indeed important, tell them your case... On another note I was going to suggest getting the schema from your DB table, but you mentioned the data is not the same; at this point you can't do anything because you don't have keys to work off of and the data is all over. If you get the datatype, which you can, how are you going to map it to the correct column in the table without any key? – Trevor Jan 23 '19 at 14:56
  • 2
    What you can do is to build a class that *knows* how to map each string piece to a specific value. When the deserializer has filled all the `List>` with the values, pass each `List` to the specialized class that will return a new class object with the string members converted to the correct type, corresponding to the Database field types. – Jimi Jan 23 '19 at 15:00
  • I wrote a TableCreater that loops over the number of row entries and creates as many columns. During this query I can also determine the Datatype of each column. Like Create Table [MyTable] (Loop the name, Placeholder for the Datatype) – ruedi Jan 23 '19 at 15:00
  • `During this query I can also determine the Datatype of each column` what if the table has more than one datatype? If your records are inconsistent it still will not help, how do you determine if a field you need is not in the results? – Trevor Jan 23 '19 at 15:03
  • On another note, how are you deserializing these records without keys? I know a list or array would work, but how are you separating the values? – Trevor Jan 23 '19 at 15:19
  • https://stackoverflow.com/questions/54213664/how-to-deserialize-json-string-dynamically – ruedi Jan 23 '19 at 15:20
  • Technically you just have a list, array, collection etc of this data, but it's all strung together. Now if the data is not constant and or not in any specific order, how can you map it correctly if its possible that the same type exist more than one time? Of course the types can be determined, but `how` would you map them to per say a property if there's nothing to map it on? – Trevor Jan 23 '19 at 15:24
  • The idea is a class as tim wrote above. If I can assign a datatype to each of the objects element (I just can use the first element, since the sequence is the same for all elements) I can use this to create the datatabel in sql. I will update my question asap – ruedi Jan 23 '19 at 15:28
  • `since the order of the data is not the same for each query`, that doesn't sound like its the same sequence... – Trevor Jan 23 '19 at 15:30
  • It can be automated if the values: `"4482359","2","3","1","12526"` are always provided in defined ranges and sequence. For example: `"12526"` represents a value which is between, say, `1000` and `99999`, `"4482359"` is always > `99999` and `"2","3","1"` always represent the same data type/field in sequence, no matter in what position they are. – Jimi Jan 23 '19 at 16:36
  • @Jimi I agree, but the OP statement `since the order of the data is not the same for each query` it will not work as already mentioned. His example above clearly shows the data is not in sequence at times. – Trevor Jan 23 '19 at 16:47
  • @Çöđěxěŕ Yes, but it doesn't really matter if every data piece is in order, if you can identify one datum by its converted value: e.g., if a converted value is always beyond a certain *number*, you can say that this value belongs to a certain Database Field. If the string is actually a string, it can only map to another field and so on. The `"2","3","1"` are a special case: they can be mappen only if they are always in the same sequence (represent the same Fields, in a defined sequence). – Jimi Jan 23 '19 at 16:51

0 Answers0