Google Big Query JSON j,v. objects into name/value pairs

Hello n8n.

Google Big Query returns an interesting structure of rows…

{
    "schema": {
        "fields": [
          {
            "name": "Name",
            "type": "STRING",
            "mode": "NULLABLE"
          },
          {
            "name": "Address",
            "type": "RECORD",
            "mode": "REPEATED",
            "fields": [
              {
                "name": "street",
                "type": "STRING",
                "mode": "NULLABLE"
              },
              {
                "name": "city",
                "type": "STRING",
                "mode": "NULLABLE"
              }
            ]
          }
        ]
      },
      "rows": [
        {
          "f": [
            {
              "v": "Peter"
            },
            {
              "v": [
                {
                  "v": {
                    "f": [
                      {
                        "v": "street1"
                      },
                      {
                        "v": "city1"
                      }
                    ]
                  }
                },
                {
                  "v": {
                    "f": [
                      {
                        "v": "street2"
                      },
                      {
                        "v": "city2"
                      }
                    ]
                  }
                }
              ]
            }
          ]
        }
      ]
}

I don’t have access to the js library that can parse this into …

{ 
    "Name": "Peter",
    "Address" : [ 
        { "Address.street": "street1", "Address.city": "city1" },
        { "Address.street": "street2", "Address.city": "city2" } 
    ]
}

Is this possible… Twitter tells me a Function-Node ? I can copy and paste code but that is about it :slight_smile:

I have some node.js here if that makes sense.

Transforming BigQuery JSON API responses recursively | by Eben du Toit | Towards Data Science

Welcome to the community @Nigel_Godfrey!

Thanks a lot for providing link and function. That made it very simple. Here is an example workflow:

The output will then be like this:
Screenshot from 2021-01-14 09-29-19

2 Likes

Thank you so much.

You are welcome. Have fun!