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:

{
  "nodes": [
    {
      "parameters": {
        "functionCode": "items[0].json = {\n    \"schema\": {\n        \"fields\": [\n          {\n            \"name\": \"Name\",\n            \"type\": \"STRING\",\n            \"mode\": \"NULLABLE\"\n          },\n          {\n            \"name\": \"Address\",\n            \"type\": \"RECORD\",\n            \"mode\": \"REPEATED\",\n            \"fields\": [\n              {\n                \"name\": \"street\",\n                \"type\": \"STRING\",\n                \"mode\": \"NULLABLE\"\n              },\n              {\n                \"name\": \"city\",\n                \"type\": \"STRING\",\n                \"mode\": \"NULLABLE\"\n              }\n            ]\n          }\n        ]\n      },\n      \"rows\": [\n        {\n          \"f\": [\n            {\n              \"v\": \"Peter\"\n            },\n            {\n              \"v\": [\n                {\n                  \"v\": {\n                    \"f\": [\n                      {\n                        \"v\": \"street1\"\n                      },\n                      {\n                        \"v\": \"city1\"\n                      }\n                    ]\n                  }\n                },\n                {\n                  \"v\": {\n                    \"f\": [\n                      {\n                        \"v\": \"street2\"\n                      },\n                      {\n                        \"v\": \"city2\"\n                      }\n                    ]\n                  }\n                }\n              ]\n            }\n          ]\n        }\n      ]\n};\nreturn items;"
      },
      "name": "Mock-Data",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        450,
        300
      ]
    },
    {
      "parameters": {
        "functionCode": "function convertBQToMySQLResults(schema, rows) {\n\n    var resultRows = []\n    \n    function recurse (schemaCur, rowsCur, colName) {\n\n        if (Array.isArray(schemaCur) && !Array.isArray(result[colName])) {\n            for(var i=0, l=schemaCur.length; i<l; i++) {\n                if (colName === \"\")\n                    recurse(schemaCur[i], rowsCur.f[i], colName + schemaCur[i].name)\n                else\n                    recurse(schemaCur[i], rowsCur.f[i], colName + \".\" + schemaCur[i].name)\n            }    \n        }\n\n        if (schemaCur.type && schemaCur.type === \"RECORD\") {\n            if (schemaCur.mode !== \"REPEATED\") {\n                var valIndex = 0\n                for (var p in schemaCur.fields) {\n                    if (rowsCur.v === null) {\n                        recurse(schemaCur.fields[p], rowsCur, colName + \".\" + schemaCur.fields[p].name)\n                    } else {\n                        recurse(schemaCur.fields[p], rowsCur.v.f[valIndex], colName + \".\" + schemaCur.fields[p].name)\n                    }\n                    \n                    valIndex++\n                }\n            } \n            \n            if (schemaCur.mode === \"REPEATED\") {   \n                result[colName] = [] \n                for (var x in rowsCur.v) {\n                    recurse(schemaCur.fields, rowsCur.v[x], colName)\n                }\n            }\n        } else {\n            if (schemaCur.mode === \"REPEATED\") {\n                if (rowsCur.v !== null) {\n                    result[colName] = rowsCur.v.map( (value, index) => { return value.v })\n                } else {\n                    result[colName] = [ null ]\n                }\n                \n            } else if (Array.isArray(result[colName])) {\n                let nextRow = {} \n                for (var j in schemaCur) {\n                    nextRow[colName + \".\" + schemaCur[j].name] = Array.isArray(rowsCur.v.f[j].v) ? rowsCur.v.f[j].v.map( (value, index) => { return value.v }) : rowsCur.v.f[j].v \n                }\n                result[colName].push(nextRow)\n            } else {\n                if (colName !== \"\")\n                    result[colName] = rowsCur.v\n            }\n        }\n    }\n\n    for (var r=0, rowsCount=rows.length; r<rowsCount; r++) {\n        var result = {};\n        recurse(schema, rows[r], \"\")\n        resultRows.push(result)\n    }\n\n    return resultRows\n}\n\nconst rows = convertBQToMySQLResults(items[0].json.schema.fields, items[0].json.rows);\nreturn rows.map(row => ({json: row}));"
      },
      "name": "Convert",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        650,
        300
      ]
    }
  ],
  "connections": {
    "Mock-Data": {
      "main": [
        [
          {
            "node": "Convert",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}

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!