Extract values from a JSON string in Visual SQL

Say you have a JSON string from which you’d like to extract specific values. This can be accomplished in Chartio’s data pipeline, adhering to SQLite syntax and the JSON_EXTRACT function presented in this article.

• Non-nested JSON string
• Nested JSON string
• Another nested JSON string

A non-nested JSON string extraction example

We have a Table chart showcasing our JSON string column:

We want to extract our user’s ID—but how do we pull this number out? This is where we turn to the Pipeline to help transform the current output. First, we need to add a Formula Column, select Custom as the formula type, then use the JSON_EXTRACT function:

JSON_EXTRACT("JSON Column", '$.id')

The function takes in two arguments separated by a comma:

1. The first argument is the column containing the JSON string. Make sure to wrap the column name in double quotes. In our example, our column’s name is JSON Data.

2. The second argument is the path to the values we’re trying to extract, wrapped in single quotes. The path starts with $ (which gives us access into the JSON row) followed by a period and the key of the value we want to pull (i.e., the label to the left of the value in the JSON string). Because we chose id, we extracted the value to the right of "id": in the string, giving us the output below:

   

From here, you can continue extracting other values you’d want. In our example, if we wanted to extract email addresses, we’d follow the same logic:

JSON_EXTRACT("JSON Column", '$.email')

Here’s our final Table chart with the two extracted JSON values:

A nested JSON string extraction walkthrough

Our JSON string wasn’t nested in the previous example. If the values were nested, we’d have to change our approach a bit. Let’s say we want to extract the same email values as our last example, but now our JSON string is nested within an array (indicated by the square brackets):

To extract the email now, we’d use the following function:

JSON_EXTRACT("JSON Column", '$.user[0].email')

If we were to only use…

JSON_EXTRACT("JSON Column", '$.user')

…this would return the entire array (i.e., everything to the right of "user").

By appending [0] after user in our function’s path argument, we can pull the values from the first item in the user’s array, which is where our email value resides.

The full path argument '$.user[0].email' outputs the email information in each row:

Another nested JSON string extraction walkthrough

Now, let’s say our JSON string is nested even further. It may look something like this:

{
	"user": [
		{
			"id": 1,
			"gender": "Female",
			"first_name": "Susan",
			"last_name": "Huetson",
			"email": "shuetson0@amazon.de",
			"ip_address": "47.47.39.223",
			"friend": [
				{
				 "first_name": "Querida",
				 "last_name": "Clark"
				}
			]
		}
	],
	"comment_id": 11111,
	"comment": "This is my first comment!",
	"post_id": 99999
}

If we want to extract the first name of the user’s friend, we’d once again change the path argument in our Formula Column:

JSON_EXTRACT("JSON Column", '$.user[0].friends[0].first_name')

Let’s breakdown each part of the path argument:

• The first part of the path, $.user[0], brings in:

    {"id": 1,"gender": "Female", "first_name": "Susan", "last_name": "Huetson", "email": "shuetson0@amazon.de", "ip_address": "47.47.39.223", "friend": [{"first_name": "Querida", "last_name": "Clark"}]}
  

• We still need to move past the other bracket to the right of "friend":, which is the next part of our function’s path. $.user[0].friend[0] brings in:

    {"first_name": "Querida", "last_name": "Clark"}
  

• Removing the brackets has given us access to easily extract the first name value. The final part of our path is calling on the key that contains this value (i.e., first_name), which would be:

    $.user[0].friend[0].first_name
  

The output of our function would be Querida.