How To Extract Data From Txt To Json

In some cases it happens that you demand to collect several data points into i single field. In this article I'll share with you how to extract such data from a JSON cord with Google Information Studio. Central benefit is that you have maximum flexibility to admission all information points contained in such a string. To achieve this, In the following how to, I'll prove how to extract data with the Google Data Studio regular expression function called „REGEXP_EXTRACT".

Problem & challenge
Solution & consequence
How to extract data from a JSON string with regexp_extract
Some last words

Trouble & challenge

For i of my clients, nosotros are collecting a huge amount of data within a unmarried event in Google Universal Analytics. Information technology's a results data prepare of a configurator tool. For several reasons, we decided to send a consummate JSON string with all configurator results into the Universal Analytics effect action data point.

Example of a JSON string from which single values will be extracted for use in Google Data Studio

Example of a JSON string from which single values volition be extracted for use in Google Data Studio

As a next pace, we had the claiming to unravel that data from that JSON string again.
From this, we came upwards with the solution introduced below.

Hither's how we did information technology before developing this new approach. Nosotros worked with the function „CONTAINS_TEXT" and the following data studio resource custom field construct:

Data source field with CONTAINS_TEXT function

Data source field with CONTAINS_TEXT part

But in that location are some constraints coming with this solution:

Only a few parameter values can be added the manual way. In case of above shown example of cities' names, one can imagine at that place are way more than cities that users can come up from.
Use the data as dimension, not equally metric. Thus, this is not so flexible.
Couldn't extract values with many different values. (Bear witness case of 0.12345 value)

Solution & consequence

A swell way to accomplish this is Google Data Studios Regular Expressions function: REGEXP_EXTRACT.
You can find Google'due south documentation of the function here.

The post-obit outcomes come with that approach:

More flexibility
collect *all* values contained in the JSON string
Practice calculations with numbers coming from a JSON string

Two more annotations before nosotros start:

- This likewise works with any other format in which information is packed into a single field. For case comma separated values (CSV).
- This article is not about how to connect a JSON data source to Google Data Studio. To this topic others accept already delivered solutions similar Garrett Vorce: „Google Data Studio Tutorial with JSON" on Medium.

And so, let'due south now have a await into the details, how to achieve this:

How to extract data from a JSON string with the Regular Expression office „REGEXP_EXTRACT"

1. Pre-Requesites: Setup a Google Data Studio dashboard and connect your information source

You can read at google.com how to reach that: Click here.

two. Add a new data source field

In Google Data Studio, click in the menu on „Resource"
And then click on „Manage added data sources"
Notice your information source
Click on „Edit"
Click on „(+) Add a field"

three. Employ regexp_extract to extract the data out of the JSON string

3.ane Extract a text cord from JSON – with a Regular Expression

Now, in the „Add a field" dialogue, outset give your new field a proper name.
In our example, we want to extract the data „city" from the JSON cord. So we can call the field i.e. „Urban center (JSON)".
So it's time to enter the formula:REGEXP_EXTRACT(Ereignisaktion,'"city":"((\\"|[^"!,])*)"')

Data source field with REGEXP_EXTRACT function
And so now, what does this formula do in item? Nosotros'll have a await:
1. WithREGEXP_EXTRACT the function gets chosen
2. Ereignisaktion is the (german) proper name of the Universal Analytics event tracking data field, in which the JSON cord is stored. (In english it would be Result action.)
3. The key role now is theregular expression (also chosen „regex" or „regexp"): '"metropolis":"((\\"|[^"!,])*)"'. It does the following:
  - The two ' signs mark beginning and end of the „regex".
  - "urban center":" are the signs that are directly before the searched cord (here the cities' names) that nosotros want to extract.
  - With the office ((\\"|[^"!,])*)" the searched string (the city name) gets collected into our newly added field.

At that place are different kinds of information that could exist extracted:

3.2 Excerpt numbers from JSON

You want to extract numbers from JSON? This is likewise possible.

Y'all can do this with following regular expression (regex) formula. Example hither is a Longitude definition named lon:
REGEXP_EXTRACT(Ereignisaktion,'"lon":(([^"!,])*)')
With this regex formula we practise not search for a string in brackets, only for a string without brackets. This is like numbers are defined in JSON.
To be able to summate with this new metric also, you will need to change the data fields blazon to „number":

Change data source field to type „number"

4. Add a new chart to your Data Studio Dashboard with the new information source field

Now yous can add the new data to your Google Data Studio dashboard:

Data visualization with JSON extracted data via REGEXP_EXTRACT function

Information visualization with JSON extracted data via REGEXP_EXTRACT function

Some final words

In case yous take any questions or annotations, please get out a annotate.

About me: My name is Thomas Gerstmann. I aid organizations to get their information driven marketing off ground. My focus is more on data-driven strategy and analytics & research besides as Conversion Optimization. But I likewise work sometimes – similar documented here in this article – on technical questions.