Extract values from a JSON string in Data Explorer

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.

A non-nested JSON string extraction example

We have a Table chart showcasing our JSON string column:

We want to extract our device ID—but how do we pull this number out? This is where we turn to the Data Pipeline to help transform the current output. First, we have to use an Add Column step, name it (in this case Device Id), choose Custom formula, then use the JSON_EXTRACT function:

JSON_EXTRACT("JSON Column", '$.device_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 simply JSON Column.
2. The second argument is the path to the values we’re trying to extract, wrapped in single quotes. The path includes a $ sign (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 device_id, we extracted the values to the right of "device_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 countries, we’d follow the same logic:

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

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 country values as our last example, but now our JSON string is nested, indicated by the square brackets (Note: We’ve changed the name of the column that has our JSON string to Device2):

Our function would now be:

JSON_EXTRACT("Device2", '$.deviceInformation[0].cn')

If we were to use the previous formula, this would pull all the information between the brackets to the right of "deviceInformation": and our new column would yield:

{"device_id": 0, "device_type": "sensor-ipad", "ip": "68.161.225.1", "cca3": "USA", "cn": "United States"}

The 0 inside the bracket for our function’s path lets us pull values inside the first bracket of “deviceInformation”, where cn has the country value to the right of its colon.

The function path JSON_EXTRACT("JSON Column", '$.deviceInformation[0].cn') outputs the country 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:

{
	"deviceInformation":
	[
		{
			"device_id": 0,
			"device_type": "sensor-ipad",
			"ip": "68.161.225.1",
			"countryInformation":
			[
				{
					"cca3": "USA",
					"cn": "United States"
				}
			]
		}
	],
	"temp": 25,
	"signal": 23,
	"battery_level": 8,
	"c02_level": 917,
	"timestamp" :1475600496
}

To extract the country values again, we would once again change our path in the add column step:

JSON_EXTRACT("JSON Column", '$.deviceInformation[0].countryInformation[0].cn')

The first part of the function, $.deviceInformation[0], brings in:

{"device_id": 0, "device_type": "sensor-ipad", "ip": "68.161.225.1", "countryInformation":[{"cca3": "USA", "cn": "United States"}]}

We still need to move past the other bracket to the right of countryInformation, which is the next part of our function’s path:

$.deviceInformation[0].countryInformation[0] brings in:

{"cca3": "USA", "cn": "United States"}

Removing the brackets has given us access to easily extract the country values. The final step of our path is calling on the name that contains these values, cn, which would be:

$.deviceInformation[0].countryInformation[0].cn

The following function path would give an output of United States:

JSON_EXTRACT("JSON Column", '$.deviceInformation[0].countryInformation[0].cn')