In this article, you will learn about Tableau, JSON file format, how to import JSON data into Tableau, select preferred schema from JSON data, and visualize them in the Tableau workspace. 

Tableau is also compatible with almost all popular file extensions, allowing users to analyze and visualize to get insights out of any complex file type effectively. One such file extension that Tableau supports is JSON, which contains data in the form of key-value pairs inside nested arrays. 

What is JSON File Format?

  • JSON (JavaScript Object Notation) is a text-based file format popularly used to implement data interchange or transport between multiple machines and programs.
  • It is a lightweight file format that stores data in the form of key-value pairs, allowing users to read and write data into JSON objects as well as machines to parse and generate data.
  • The syntax of the JSON file highly resembles the Javascript object, which stores data as a key-value pair inside nested arrays.
  • Furthermore, JSON file format is widely being used by web servers and applications for serializing and transmitting data through API calls or requests.
Tableau JSON | JSON logo
Image Source

How to use Tableau JSON?

  • Initially, you have to get your appropriate JSON data, which you are about to visualize using the Tableau Desktop. For this tutorial, you will use the JSON data named “Fuel_Station” downloaded from the National Renewable Energy Laboratory (NREL) website. 
  • The NREL also offers an API service through which you can download your preferred JSON data into your local machine. 
  • After downloading the “Fuel_Station” JSON file, make a note of the file location for future use.
  • The “Fuel_Station” data mainly consists of four levels of schema, such as Fuel Station, Cards Accepted, Address, and PSI.
    • Fuel Station: This parameter describes the information about the fuel station like open date, fuel type code, status code, access days time.
    • Cards Accepted: This parameter lists the types of cards accepted by the fuel station.
    • Address: The address parameter describes the appropriate location information of the fuel station along with the latitude, longitude, city, and state details.
    • PSI: This parameter lists the available pressures (PSI) from the station when compressed natural gas is available.
Tableau JSON | How to use Tableau JSON?
Image Source
  • These four levels of schema have a unique set of key and value pairs, as shown in the above image. 
  • Now, you are all set to load and read the below-given JSON data in the Tableau workspace.
Tableau JSON | How to use Tableau JSON?
Image Source
  • Open your Tableau Desktop and click on Connect menu. Then, select JSON from the left side pane, as shown in the image given above.
Tableau JSON | How to use Tableau JSON?
Image Source
  • Now, you will be prompted to upload the JSON file from your local machine. Choose the appropriate JSON file, i.e., “Fuel station,” and click on the “Open” button. After uploading the JSON file, Tableau reviews the file and infers the schemas present in the JSON data.
  • In the next step, you will get a “Select Schema Levels” dialogue box that prompts you to choose the schema levels you want to analyze and visualize using Tableau JSON. In this case, you will select the fuel station list, address, and cards accepted as the required schemas. 
  • After selecting the appropriate schemas, click on the OK button.
  • Now, the selected schemas are loaded into the Tableau workspace, which you can find in the left side pane. Tableau JSON automatically splits the JSON data and its schema into Dimensions and Measures. The categorical data like city, state, and Zip are organized into the dimension segments, while numerical data like latitude, longitude and the number of records per fuel station are organized in the measures segment.
Tableau JSON | How to use Tableau JSON?
Image Source
  • On the data tab, the dimensions and measures are organized at the same level as present in the Tableau JSON file. 
  • Since the dimensions and measures are organized according to the same schema level as per the JSON document, you can easily aggregate the metrics at the appropriate level of detail.
  • Since Tableau itself parses the key-value information out of the Tableau JSON data, you can easily visualize the dimensions and measures by just dragging and dropping them into the workspace.
Tableau JSON | How to use Tableau JSON?
Image Source
  • According to the imported JSON data, Tableau created two-measure parameters such as “Number of Records Per Fuel Station List” and “Number of Records Per Cards Accepted.” 
  • The “Number of Records Per Fuel Station List” measure counts the number of fuel stations that are unique, while the “Number of Records Per Cards Accepted” counts the number of cards accepted by each station.
  • With these two pieces of information, you can create a horizontal bar chart to visualize how many fuel stations accept more than one type of credit card.
Tableau JSON | How to use Tableau JSON?
Image Source
  • After creating a horizontal bar chart using the “Number of Records Per Fuel Station List” and the “Number of Records Per Cards Accepted” field, you will get the visualization, as shown above. 
  • Tableau JSON perfectly visualizes JSON data by creating special index fields for each level of the hierarchy. This is done at the initial stage, while Tableau flattens the JSON data according to your preferred schema selection. Tableau then creates the level-of-detail measures for you, allowing you to easily aggregate to the appropriate level, just as the JSON data specifies.
Tableau JSON | How to use Tableau JSON?
Image Source
  • You can now create a new visualization based on each fuel station’s unique index and the appropriate Station Name, along with the Number of Records Per Fuel Station List and Number of Records Per Cards Accepted counts. 
  • As given in the above image, since we are aggregating by station name dimension, the total number of stations is always one, whereas the number of cards accepted per station can range from one to eleven.
Tableau JSON | How to use Tableau JSON?
Image Source
  • Then, you can even visualize the growth of electric charging stations since 2011. Along with that, you can add a filter for the “Cards Accepted” parameter to sort the stations based on how they accept payment. 

On following the above-given steps, you successfully imported and visualized JSON data using Tableau. 

Limitations of using Tableau JSON

  • To effectively import and visualize JSON data using Tableau JSON, the nested arrays present in the given JSON file should not exceed the limit of 10×10. If there are a lot of nested arrays in JSON data, it will reduce the processing speed of Tableau. 
  • For example, 10×10 nested arrays themselves will result in 10 billion rows. If you exceed the given limit of nested arrays, Tableau JSON cannot handle such colossal amounts of data. Consequently, an error message appears when you exceed the number of rows that Tableau can load into memory. To eradicate this complication, Tableau JSON always asks you to select the preferred schema levels, thereby reducing the number of nested arrays.
  • A single Tableau JSON object present in the JSON file is only limited to 128 MB in size and cannot exceed that limit. If the single JSON object exceeds the size of 128 MB, you have to transform it into a file with JSON objects defined as one per line. 
  • Tableau can take a long time to load a data source with more than 100 levels of Tableau JSON objects. When there are a huge number of levels in the given JSON data, there is a wide range of columns to process, which can consequently take a long time. For example, Loading just 100 levels of JSON data into Tableau will take more than two minutes. To minimize the processing time, it is always recommended only to select the schemas that are required for analysis and visualization.
  • The Pivot table feature is not supported when working with Tableau JSON data.

Conclusion

  1. This article only focused on importing and visualizing JSON data using Tableau.
  2. However, you can also import other data files like CSV, XLS, XLSX, TXT, and PDF files into the Tableau workspace for implementing effective visualizations using a rich set of tools and widgets.
  3. The process of importing other data sources is similar to the process described in this article. You just have to select your preferred data source from the “Connect” menu, and the rest of the importing process is the same regardless of the data source.

Join thousands using Hevo for leveraging data effortlessly. Sign Up for a 14-day free trial now. You can also have a look at our unbeatable pricing that will help you choose the right plan for your business needs!

mm
Freelance Technical Content Writer, Hevo Data

Ishwarya has experience working with B2B SaaS companies in the data industry and her passion for data science drives her to product informative content to aid individuals in comprehending the intricacies of data integration and analysis.

No-code Data Pipeline for Tableau