Search code examples
jsonregexlooker-studio

Extract json string field in Google Data Studio

I'm trying to analyze my Firestore data in Google Data Studio. I have successfully imported my data from BigQuery to Google Data Studio, but I'm not unable to use it.

Here is my JSON format in GDS :

{
    "document_name": "projects/path",
    "document_id": "01ASsoYefmYy4h8mGTPWzt5d5Xw1",
    "timestamp": "1970-01-01 00:00:00 UTC",
    "event_id": "",
    "operation": "IMPORT",
    "data": "{\"mail\":\"[email protected]\",\"creationDate\":1602011942852,\"initialized\":true,\"languageCode\":\"en\"}"
}

I'm trying to use the creationDate or the mail as field but I'm unable to create a field with this because this is a stringified JSON. I tried to find a solution with formula, without success.

How to exploit field in stringified JSON in Google Data Studio ?

Thanks

Solution

  • I'v finally found a solution : apply a schema view to my data field.

    Here is the documentation directly from Firebase : https://github.com/firebase/extensions/blob/9f8d7fd6048bcaa7b5bc505cebe2e90494359e33/firestore-bigquery-export/guides/GENERATE_SCHEMA_VIEWS.md