How To - Parse Complex JSON into multiple tables
- Ann Crawford (Unlicensed)
Using an API as a data source will generally return JSON data. JSON can be parsed into a wide de-normalized table or a series of small tables. This article discusses how to break the JSON into multiple tables. This example uses the api https://test.api.ucsb.edu/administration/financial/accounts/departments/v1/VCST
which returns a list of departments. Each department has three arrays of cost center, project code and cost type. Each of the arrays will be modeled as a separate table.
Instructions
Select web as the data source and enter the url and in the case of UCSB APIs click advanced and enter ucsb-api-key in the header and the value of the key
2. This will return a complex JSON - click To table. Click o.k. when promoted on delimiters.
3. After to table is applied, click the double arrows to add columns . If you do not have column name conflicts, uncheck use original column in name. Select the columns that you need.
4. After expanding the columns, you see that the JSON arrays show as lists. Right click on the list column and select Add as new query from the drop down menu. This will create a new table with the same name as the list column, in this example, cost types.
5. Select To Table button , click o.k. on the delimiter prompt.
6. Click the double arrows and expand the columns as needed until colums are displayed.
7. Click remove rows > remove duplicates - this completes the creation of a cost type dimension from a JSON array.
8. Click on step “Expanded column 1” in the navigation and repeat steps 4-7 to create other separate tables from the JSON.