I've been trying to use the javascript google-trends-api from npmjs.com to automate retrieval of search trend data. I'm successful in getting the raw JSON output from my queries, but I can't seem to successfully parse them into a form -- preferably CSV or tab-delimited text -- that I can import into other software (Stata) for analysis. I've tried json2csv to parse the results, but this returns nothing resembling a spreadsheet-style file.
Here's an example of some representative query results (abridged for length):
"{\"default\":{\"geoMapData\":[{\"geoCode\":\"798\",\"geoName\":\"Glendive MT\",\"value\":[100],\"formattedValue\":[\"100\"],\"maxValueIndex\":0,\"hasData\":[true]},{\"geoCode\":\"552\",\"geoName\":\"Presque Isle ME\",\"value\":[49],\"formattedValue\":[\"49\"],\"maxValueIndex\":0,\"hasData\":[true]},{\"geoCode\":\"710\",\"geoName\":\"Hattiesburg-Laurel MS\",\"value\":[11],\"formattedValue\":[\"11\"],\"maxValueIndex\":0,\"hasData\":[true]},{\"geoCode\":\"766\",\"geoName\":\"Helena MT\",\"value\":[0],\"formattedValue\":[\"\"],\"maxValueIndex\":0,\"hasData\":[false]}]}}"
Upon running json2csv -i "results.json" and saving the output using fs.outputJson, I get errors unless I strip the outermost quotes and all backslashes. But even after doing that, this is what I get back:
"default.geoMapData" "[{""geoCode"":""798"",""geoName"":""Glendive MT"",""value"":[100],""formattedValue"":[""100""],""maxValueIndex"":0,""hasData"":[true]},{""geoCode"":""552"",""geoName"":""Presque Isle ME"",""value"":[49],""formattedValue"":[""49""],""maxValueIndex"":0,""hasData"":[true]},{""geoCode"":""710"",""geoName"":""Hattiesburg-Laurel MS"",""value"":[11],""formattedValue"":[""11""],""maxValueIndex"":0,""hasData"":[true]},{""geoCode"":""766"",""geoName"":""Helena MT"",""value"":[0],""formattedValue"":[""""],""maxValueIndex"":0,""hasData"":[false]}]"
I'd appreciate any guidance in how to translate my query output into a sensible CSV (or the like). I should add that I'm a complete beginner at this stuff, and apologize if the answer is obvious!
Eric
To convert JSON data to CSV, you can use jq command line utility.
Provided your sample data, you can do:
echo "{\"default\":{\"geoMapData\":[{\"geoCode\":\"798\",\"geoName\":\"Glendive MT\",\"value\":[100],\"formattedValue\":[\"100\"],\"maxValueIndex\":0,\"hasData\":[true]},{\"geoCode\":\"552\",\"geoName\":\"Presque Isle ME\",\"value\":[49],\"formattedValue\":[\"49\"],\"maxValueIndex\":0,\"hasData\":[true]},{\"geoCode\":\"710\",\"geoName\":\"Hattiesburg-Laurel MS\",\"value\":[11],\"formattedValue\":[\"11\"],\"maxValueIndex\":0,\"hasData\":[true]},{\"geoCode\":\"766\",\"geoName\":\"Helena MT\",\"value\":[0],\"formattedValue\":[\"\"],\"maxValueIndex\":0,\"hasData\":[false]}]}}" | \
jq -r '.default.geoMapData | map([.geoCode,.geoName,(.value[]|tostring),.formattedValue[],(.maxValueIndex|tostring),(.hasData[]|tostring)] | join(",")) | join("\n")'
798,Glendive MT,100,100,0,true
552,Presque Isle ME,49,49,0,true
710,Hattiesburg-Laurel MS,11,11,0,true
766,Helena MT,0,,0,false