Querying JSON data in PAD

Some of the data that syncs in PAD contain JSON data, which is a distinct type of data format. This means that when you view your data, you may come across array-based columns with {} symbols, which will list out various pieces of data. The actual data type may be displayed as a STRING or something else instead of JSON. A lot of data sources use JSON format, and CTA generally receives the data for our syncs in the format that the provider delivers it, which is why you may see it in your syncs. 

There are various ways to query this data in PAD, and you can refer to the examples below or get in touch with help@techallies.org if you have any questions.

Reading JSON Data Structure Without Manipulation: 

Here is an example of JSON data in PAD. The JSON data is in the column titled ‘json_column’, and it looks different than the non-JSON data in the next column over. 

To read the JSON column, you need to look at the matched pairs in the data. Each matched pair represents one piece of information. For instance, "id" is matched to "test1". We can tell that they are matched because they are in their own pair, separated by a colon and a comma separates any other value pairs. Similarly, "company" is matched to "CTA", and so on. The first part of the pair gives you the value name, and the second part tells you the actual value. For instance, in our first pair, the value name is "ID", and "test1" is the actual ID for that row. This tells us that for this row of PAD data, the ID value is "test1", and the company value is "CTA".

In JSON data, it is possible to have multiple layers of information. For instance, in the given example, you can see a second layer of 'names'. This layer corresponds to the first name, middle name, and last name value pairs. This happens when there are multiple value pairs for a type of information. We can read this data by finding matching pairs. For example, "firstname" is separated from "Jane" by a colon. This indicates that the value name is "firstname," and the actual value is "Jane." Similarly, "middlename" is another value name, and "A" is the actual value, and so on.

If your query doesn’t require this kind of information to be split apart into the individual values, and this format is okay for your end product, you don’t need to do any manipulation; you just need to know how to read the data, and you include the JSON column in queries as you normally would. 

Pulling Out JSON Values:

If you want to view only a specific JSON value from a column, you will need to use the JSON_VALUE function. This function enables you to extract a single value from the JSON column instead of the entire column. It is important to note that when using JSON_VALUE, the results will be returned as a string data type. The structure of queries using JSON_VALUE follow this pattern:

SELECT JSON_VALUE(<column name>, '$.<value name>') FROM <tablename>

Looking at our example data below:

If you want to return just the ID value, you would run: 

SELECT JSON_VALUE(json_column, '$.id') FROM <tablename>

The following code will only return the ID value from your JSON data. However, if your data has multiple layers, like the example, you can modify the code to extract other values. For instance, if you want to return the firstname value from our example data, you need to adjust the code accordingly.

SELECT JSON_VALUE(json_column, '$.names.firstname') FROM <tablename>

You can do this multiple times in your query for different parts of the JSON column in order to pull in each unique value. For example, if we wanted to split out all of our example JSON data into individual columns, you would query it as:

SELECT

JSON_VALUE(json_column, '$.id') as id,

JSON_VALUE(json_column, '$.company') as company,

JSON_VALUE(json_column, '$.names.firstname') as firstname,

JSON_VALUE(json_column, '$.names.middlename') as middlename,

JSON_VALUE(json_column, '$.names.lastname') as lastname 

FROM <tablename>

Depending on how your data is structured, there are some more advanced functions that may be helpful to streamline pulling out each column individually; see the other functions note below! 

Other Functions:

Google has a number of other JSON functions that can be helpful in working with JSON data or in converting data to a JSON format to work with. You can read more about them in Google’s documentation here.

Have questions? Email us at help@techallies.org!