How to convert JSON response Of BigQuery to CSV using Node.js (f and v keys)?

Question

I am getting following as a response from bigquery and i want to convert this whole data to CSV.

"rows": [
 {
  "f": [        
    {
      "v": "1"
    },
    {
      "v": "204"
    },
    {
      "v": "1464830471"
    },
    {
      "v": "4"
    },
    {
      "v": "5593693585347748"
    }
  ]
},
{
  "f": [        
    {
      "v": "1"
    },
    {
      "v": "205"
    },
    {
      "v": "1464865550"
    },
    {
      "v": "2"
    },
    {
      "v": "5593693585347748"
    }
  ]
}

]

Now i want this f and v to be converted like this.

data.csv

1,204,1464830471,4,5593693585347748
1,205,1464865550,2,5593693585347748.

Here Bigquery responses consist of f and v key.thats make confusion.

score 1 · Accepted Answer · answered Aug 25 '16 at 11:23

1

Try this if x is your object:

var x = {"rows":[{"f":[{"v":"1"},{"v":"204"},{"v":"1464830471"},{"v":"4"},{"v":"5593693585347748"}]},{"f":[{"v":"1"},{"v":"205"},{"v":"1464865550"},{"v":"2"},{"v":"5593693585347748"}]}]};

var output = "";

x.rows.map(function(e){

    var values = [];
    e.f.map(function(o){
        values.push(o.v);
    }); 
     output += values.join(",") + "\n";
})

console.log(output);

Prints out this:

1,204,1464830471,4,5593693585347748
1,205,1464865550,2,5593693585347748

answered Aug 25 '16 at 11:23

Frank Roth

6,191
3
25
33

its working,but i am going to process 100GB of data is this method is feasible for processing the JSON. – arjun kori Aug 25 '16 at 11:29
Oh ok that is too big to load it to memory. You have to use streams to continuously read json objects. Wrap my code into this json stream based reader. https://www.npmjs.com/package/stream-json. The "endObject" event should help you. – Frank Roth Aug 25 '16 at 11:37

score 1 · Answer 2 · edited May 23 '17 at 12:16

1

A couple of ideas:

Change the query to use GROUP_CONCAT (legacy SQL) or STRING_AGG (standard SQL) in order to generate a comma-separated string for each row.
Don't modify the query, and use a utility such as json2csv to convert to CSV within your application. Possibly relevant question: How to parse JSON object to CSV file using json2csv nodejs module

edited May 23 '17 at 12:16

Community

1
1

answered Aug 25 '16 at 11:27

Elliott Brossard

32,095
2
67
99

Yes, here is a link to the documentation: https://cloud.google.com/bigquery/query-reference#group_concat – Elliott Brossard Aug 25 '16 at 11:33

How to convert JSON response Of BigQuery to CSV using Node.js (f and v keys)?

2 Answers2