Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Well howdy partner.
Back in that there big city all your data was nicely structured into tables and columns. Other than the chaos of a million requests your life as the data sheriff was pretty easy. You knew how to wrangle up any stray data with your "Selects" and your "Joins."
Like so many that hopped on a wagon train to the west in the past for a gold rush, your company hopped on board a train to a modern data stack. Now here you are with all your outlaw JSON data coming at you like rattle snake.
- Your drifter data.
- Your snake oil data.
- Your Ghost Town Data.
- Your Gold Rush Data.
- Your embedded unstructured JSON returns from an RDBMS.
- Your unstructured JSON returns from RESTful APIs.
The good news is, that it's easy to read visually. At least it's easy to read when it's prettified.
But your problem is that your company needs you to translate it from this unstructured textual JSON data into structured values that they can gain insights from, and take action on. Just when you think you've figured out a pattern, you discover yet another JSON structure type.
Well partner, let's walk through the various types of JSON structures you will see together. I want to help ensure your JSON Data General Store is fully operational and that you know the patterns to looks for when any new varmints come moseying along.
In this post I will walk you through various examples, provide you a sample Qlik Sense Application that contains all of the types, and then provide a linked article explaining each type in more detail that you can access.
Flat JSON - Field Value Pairs
The most basic type of JSON structure is a flat one that simply contains a set of field and value pairs. The { and } are the textual wrappers that basically signify what you could think of as a single row for an entity. In a RDMS world, you would have column names and then values. In this JSON world you have field and value pairs. In this example image we have 4 fields/columns "outlaw_name", "bounty", "status" and "last_seen_town." Notice that the values for the fields are separated using the ":" character. If the values are textual, then like the field names, they are wrapped in double quote characters, while numeric data isn't.
Nested JSON Objects
A slightly more sophisticated JSON structure is one that includes a nesting if you will of the objects. Rather than simply using field value pairs like above, information surrounds entities. And entities can include other entities. In other words in an RDBMS world this would be multiple tables with primary key and foreign key relationships that would need to be joined.
You can distinguish an entity from a field based on the value. Notice that sheriff doesn't have a single value, it instead contains a block of JSON code. That block has 2 simple field:value pairs for name and badge_number and it also includes an entity called office. Unlike SQL where the office entity would exist on it's own, in our JSON structure it is nested into the office structure. While a minor distinction the point itself is major: You don't have a sheriff entity and an office entity. You have a sheriff entity and a sheriff/office entity nested within the sheriff entity.
Click here to access a post dedicated to helping you parse out Nested JSON Objects.
JSON Arrays (Homogenous Objects)
Like anyone panning for gold would tell you ... the only thing better than a gold nugget is finding more than 1. Any good bandit would tell you the only thing better than a town with 1 saloon, is a town with multiple saloons.
A JSON Array of (homogenous objects) is very much like a table in an RDBMS. Instead of just having the field:value pairs for a single row, the array contains all of the rows. The rows for the entity are wrapped in brackets "[" ... rows ... "]." In this image you can see we have a saloons entity and rather than field:value pairs for a single saloon, we have an array of saloons and each row of the array contains the simple field:value pairs.
Click here to access a post dedicated to helping you parse out JSON Arrays of Homogeneous Objects.
Heterogeneous (Mixed) JSON Objects
Up to this point we have been working with a single JSON object. A single string. You either open a file and voila there is one string. Or you make an API call and it returns one string.
Now we will focus on heterogenous JSON data that may be streaming into you. Rather than opening a file and finding one string you find multiple strings. You call an API and instead of returning 1 result string, it keeps sending you an unknown number of strings. What's worse is that you receive heterogenous objects. The type field seems consistent, but each type seems to present with a different entity: A report, a wanted_poster and a telegraph.
Don't panic data sheriff, I've got a posse of posts targeted at helping you handle these mixed JSON objects. In the first post we will look at handling this data pretty much like we've handled the JSON structures until now. In the second post I show you a much more flexible approach so that you can handle whatever desperados happen to ride into town.
Heterogeneous (Mixed) JSON Arrays
If you look closely the following image is very similar to the image above that I used for the Heterogeneous (Mixed) JSON Objects post. The difference is that instead of being multiple rows, it's a single JSON block which contains multiple rows as an array.
It also looks very close to the image I used for the JSON Arrays (Homogeneous Objects) post above that one. The difference is that in the case of that post, the array was easy to understand as just being multiple rows for the same entity. In this case, each of the "rows" in our array our for different entities.
The good news for you is that you've already done the work to handle both arrays and heterogeneous data. That is assuming you've done the homework in both of those posts. The post I'm writing for handling this type of data will be brief. All I need to do is help you understand how to combine the two algorithms.
Click here to access a post dedicated to helping you parse out Heterogeneous JSON Arrays.
Structured Arrays
Just when you thought you had all those JSON array critters rounded up ... another one appears while you were in the saloon celebrating. While there is no formal name for this JSON Version 2 array, structured, seems to make sense. Notice that is very much like SQL in that the column headers and the data are sent separately. Which reduces the data redundancy when dealing with transmitting lots and lots and lots of rows of data.
Big Picture
This post is the starting post for a series aimed at ensuring you have the tools needed to Tame with Wild West Data Frontier, I mean Tame the Wild JSON Data Frontier you may be facing.
Posts:
- Taming the Wild JSON Data Frontier <--- You are here
- Parsing: Flat JSON - Field Value Pairs
- Parsing: Nested JSON Objects
- Parsing: JSON Array (Homogeneous Objects)
- Parsing: Heterogeneous (Mixed) JSON Objects Fixed Manner
- Parsing: Heterogeneous (Mixed) JSON Objects Flexible Manner
- Parsing: Heterogeneous (Mixed) JSON Arrays
- Parsing: Structured JSON Arrays
