How to read Json in Google Data Studio, Big Query, Json

Question

I am using Big Query for as a cloud data warehouse and DataStudio for vizualisation.

In Big Query I have a table with a column named data written in JSON. I only want to extract what is inside the field "city".

This formula below that someone gave me worked to extract what is inside the field "title". I used it to create a field in DataStudio.

REPLACE(REGEXP_EXTRACT(data, '"title":(.+)","ur'), "\"", "")

So, I tried in multiple ways to reuse this formula for the "city" field, but it hasn't worked. I don't understand this code.

What's inside my column data:

 {
    "address":{
        "city":"This is what i want",
        "country":"blablabla",
        "lineAdresse":"blablabla",
        "region":"blablabla",
        "zipCode":"blablabla"
    },
    "contract":"blablabla",
    "dataType":"blablabla",
    "description":"blablabla",
    "endDate":{
        "_seconds":1625841747,
        "_nanoseconds":690000000
    },
    "entreprise":{
        "denomination":"blabla",
        "description":"1",
        "logo":"blablabla",
        "blabla":"blablabla",
        "verified":"false"
    },
    "id":"16256R8TOUHJG",
    "idEntreprise":"blablabla",
    "jobType":"blablabla",
    "listInfosRh":null,
    "listeCandidats":[
        
    ],
    "field":0,
    "field":0,
    "field":14,
    "field":"1625834547690",
    "field":true,
    "field":"",
    "field":"ref1625834547690",
    "skills":[
        "field",
        "field",
        "field"
    ],
    "startDate":{
        "_seconds":1625841747,
        "_nanoseconds":690000000
    },
    "status":true,
    "title":"this I can extract",
    "urlRedirection":"blablabla",
    "validated":true
}

If anyone knows the formula to put in Data Studio to extract what's inside city and can explain it to me, this would help a lot.

Here's the formula I tried but where I got "null" result:

REPLACE(REGEXP_EXTRACT(data,'"city":/{([^}]*)}/'),"\"","") >>null

I tried this one but it wouldn't stop at the city. I got the address, the region, zipcode and all the rest after:

REPLACE(REGEXP_EXTRACT(data, '"city":(.+)","ur'), "\"", "")

user_16529576 · Answer

It is possible to parse a JSON in a text field by ignoring any hierarchy and only looking for a specific field. In your case the field names were title and city . Please be aware that this approach is not save for user entered data: By setting the value of the "city":"\" hide \"" the script cannot extract the city.

select *,
REGEXP_EXTRACT(data, r'"title":\s*"([^"]+)') as title,
REGEXP_EXTRACT(data, r'"city":\s*"([^"]+)') as city

from(
Select ' {     "address":{         "city":"This is what i want",         "country":"blablabla",         "lineAdresse":"blablabla",         "region":"blablabla",         "zipCode":"blablabla"     },     "contract":"blablabla",     "dataType":"blablabla",     "description":"blablabla",     "endDate":{         "_seconds":1625841747,         "_nanoseconds":690000000     },     "entreprise":{         "denomination":"blabla",         "description":"1",         "logo":"blablabla",         "blabla":"blablabla",         "verified":"false"     },     "id":"16256R8TOUHJG",     "idEntreprise":"blablabla",     "jobType":"blablabla",     "listInfosRh":null,     "listeCandidats":[              ],     "field":0,     "field":0,     "field":14,     "field":"1625834547690",     "field":true,     "field":"",     "field":"ref1625834547690",     "skills":[         "field",         "field",         "field"     ],     "startDate":{         "_seconds":1625841747,         "_nanoseconds":690000000     },     "status":true,     "title":"this I can extract",     "urlRedirection":"blablabla",     "validated":true }' as data
)

How to read Json in Google Data Studio, Big Query, Json

1 Answers