This project has moved and is read-only. For the latest updates, please go here.

Deserialization of Json data

Topics: Bug / Problems
Jun 10, 2016 at 3:20 PM
Edited Jun 10, 2016 at 3:31 PM
Hi,

I have tested this solution in SSIS, momentarely only for deserializing Json.
The Json format that it can handle unfortunately is only basic. It has one output stream.

A stream like this reads wonderful:
{  
   "data":[  
      {  
         "imp":123,
         "requests":8,
         "clicks":1,
         "fillRate":99.5
      },
      {  
         "imp":123,
         "requests":5,
         "clicks":90,
         "fillRate":8.5
      }
   ]
}
However, a json string like this:
{  
   "meta":{  
      "paging":{  
         "page":1,
         "pageSize":5,
         "pageCount":1,
         "total":1
      }
   },
   "transactions":[  
      {  
         "id":7,
         "touches":[  
            {  
               "sourceId":63
            }
         ],
         "TranDate":"2010-04-12T00:45:07.000Z",
         "items":[  
            {  
               "id":"sl"
            }
         ],
         "itemGroups":[  
            {  
               "id":99,
               "name":"Testlisting"
            }
         ]
      }
   ]
}
It will not yield to correct results. The transactions and meta can be retrieved. any other column tables, sub tables to for examples transactions, like touches are displayed, but the content cannot be deserialized. Using Jsonfilter, which should for example deserialize touches did not help. Again also Items and itemGroups are not deserialized.

Also it would be beneficial if the component could automatically retry a data connection if it failed, for example due to time out issues.
Jun 11, 2016 at 11:46 AM
Edited Jun 11, 2016 at 11:51 AM
Hi,

Your case is handled pretty well by the current tools in my last release. Json Filter and JsonSource collaborate very well together. The problem is the lack of time in writing accurate documentation and test cases to let you understand how to use them.

First of all, please make sure you are using the very last version of this software. Please download and reinstall the installer, just to make sure everything is OK. In the latest build I've also introduced alpha support for SQL 2016.

Then, look at the following:
Image

I'll try to summarize what you need to do when dealing with complex data structure like the one you provided.

1. Higher Level.
Use JSON Source to retrieve the TOP-MOST data layer that contains nested json structures. Parse an ID that you'll use during all the following process to aggregate back information. In this case I've used the Transaction ID. Every sub table has to be parsed as RAW-JSON, so the component will just provide it as RAW json to the following component in the pipeline.
Connect the output of JSONSource to a Multicast component.

Image

2. Middle level.
Using the multicast outputs, allocate one JsonFilter for each inner table. In the configuration of the filter, specify the correct input column you want to parse.
Image
Image
Image

3. Aggregation level.
Using the same ID that has been replicated within the pipelines, aggregate back the data by sort-mergin.

Image
Image

For sake of simplicity and brevity, I'll omit full configuration of the data flow, but I guess you eventually understood how to deal with this case.

I'll now write a tutorial for other people to let them handle this scenario, dealing with complex JSON data sources.
Marked as answer by webking on 6/11/2016 at 3:52 AM
Jun 11, 2016 at 12:03 PM
About the data connection attempts, you might open a issue, so people can vote for it. At the moment my time is limited so I have to decide wich feature to implement by users' votes.
Jun 13, 2016 at 9:44 AM
Hi webking,

That really looks awesome. Thanks!!
Jun 13, 2016 at 12:59 PM
Hi eric6450,

no problem. If you are willing to feed Karma, please review this release! It is crucial for me to provide some real user's opinion to the crowd.

Regards,

Alberto.
Aug 30, 2016 at 5:28 PM
the image links are broken as of 2016/08/30. It seems important to your effort to document the addin, and to get people to use the addin, for those links to be seen.
Sep 1, 2016 at 11:35 PM
I, too, am seeing broken image links.
Feb 1 at 12:22 AM
Slightly odd that a lot of effort appears to have gone into this, but lack of documentation reduces its value. For example, those missing images above leave me guessing about the parsing of nested objects.
Mar 20 at 4:24 PM
Hi Guys,

Does have anybody got that case working? I actually testing on that. My Problem is to reintegrate that data back into one stream that is that later a complete table.


Regards
Timo