Follow

Connect to JSON with a CData data source

Before you attempt this you need to add the JSON provider to your list of available sources.

Go through this guide before you start.

Add a CData data source

Contents

JSON Information

It should be mentioned that this provider can connect to JSONP files as well. Here is a link to the file I use in this example

http://mysafeinfo.com/api/data?list=top100rocksongs_billboard_2013&format=json&abbreviate=false&case=default

It contains the Top 100 Rock Songs (Billboard, 2013). The setup is as follows.

[
 { "ID": 1, "Sequence": 1, "Song": "STAIRWAY TO HEAVEN", "Performer": "Led Zeppelin" }, { "ID": 2, "Sequence": 2, "Song": "BOHEMIAN RHAPSODY", "Performer": "QUEEN" }, { "ID": 3, "Sequence": 3, "Song": "FREE BIRD", "Performer": "LYNYRD SKYNYRD" }, { "ID": 4, "Sequence": 4, "Song": "SMOKE ON THE WATER", "Performer": "DEEP PURPLE" }, { "ID": 5, "Sequence": 5, "Song": "COMFORTABLY NUMB", "Performer": "Pink Floyd" }
]

CData Setup

Add a CData data source and point to the JSON provider.

The first step in setting up a CData connector should always be to click the ? in the top right corner.

Sp_rgsm_lstegn.PNG

JSON guide from CData

You have two methods of connecting to the data.

Online resource

If it is on a online resource location you need to fill out the following fields.

URI: http://mysafeinfo.com/api/data?list=top100rocksongs_billboard_2013&format=json&abbreviate=false&case=default

You need to paste in the link to the JSON resource location.

JSON Path: <empty>

This is the the root object/element. If you want to be more specific there is some good examples in this link. JsonPath. Otherwise, just keep it empty, the JSON provider can work out the field automatically.

Local resource

If you have it saved as a .JSON file you need to fill out the following fields. I went to this page RAW data and copied the raw data and saved it in a file called Service.json.

URI: <path to file>

Add the path to the file in the data source field.

JSON Path:<empty>

The same as before.

Getting the data

Synchronize to get the available fields.

After that you can set up incremental load, make data type overwrites, data selection rules and use the Query Table tool.

You can also use additional data sources. You just need the additional sources to have the same metadata.

Advanced features

Automatically getting the correct data types

As standard CData will scan the first 100 rows and determine what data types these fields are. Sometimes this gives an error because the 101 row contained a longer string than was present in the previous rows. The solution simply is to change the Row Scan Depth field to a higher number. Also in regards to JSON/XML files, this field doesn't only solve the issue of data type, but also what tables are available. If the file contains more than 100 rows in total these will not be added. So a good idea is to work out what the maximum amount of rows that are contained in the file and then add that.

Parsing Hierarchical Data

If your file is containing nested objects, you now have the opportunity of seeing this. I used the Service.json file I downloaded earlier. It has this structure.

{
  "people": [
    {
      "personal": {
        "age": 20,
        "gender": "M",
        "name": {
          "first": "John",
          "last": "Doe"
        }
      },
      "vehicles": [
        {
          "type": "car",
          "model": "Honda Civic",
          "insurance": {
            "company": "ABC Insurance",
            "policy_num": "12345"
          },
          "maintenance": [
            {
              "date": "07-17-2017",
              "desc": "oil change"
            },
            {
              "date": "01-03-2018",
              "desc": "new tires"
            }
          ]
        },
        {
          "type": "truck",
          "model": "Dodge Ram",
          "insurance": {
            "company": "ABC Insurance",
            "policy_num": "12345"
          },
          "maintenance": [
            {
              "date": "08-27-2017",
              "desc": "new tires"
            },
            {
              "date": "01-08-2018",
              "desc": "oil change"
            }
          ]
        }
      ],
      "source": "internet"
    },
When I pull it in like this and use no other features, as shown previously. It gives me one table called people and when I preview it, each nested part is put into one field.

What you can do about this is to use the Data Model feature. As standard it is not used and it has two options besides that. Flatten Documents and Relational.

Relational creates a table for each nested part and creates ids to relate them to each other.

Like this.

People now looks like this.

FlattenDocument makes all these previous tables one large one.

Like this.

 

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.