How to parse a newline delimited JSON

user_14674167 0
Viewed 993

I’m working with App Scripts to pull a JSON file from a GCS bucket. The data is stored as a newline delimited JSON. It arrives as a large object, which makes programmatic access to the internal data impossible.

My aim is to separate each element by merchant_id. I’ve attempted to split the data using \n as the parameter. But I’m having difficulty with converting the object to a string and back again. Calling JSON.parse on the response gives me the error Unexpected token { in JSON at position 1766. While this does separate each of the elements within the JSON, calling JSON.parse on each individually gives me an Unexpected end of JSON input error.

Function

function myFunction() {

let url = 'url_address';
let storedData = [];
let response = UrlFetchApp.fetch(url, {'muteHttpExceptions': true});
let str = response.toString();
let storedResponse = str.split("\n");

storedResponse.forEach((data) => {
   storedData.push(JSON.parse(data));
});
}

Example of data in the forEach

{"product_data_timestamp":"2021-05-06 UTC","product_id":"product_id_here","merchant_id":"merchant_id_here","aggregator_id":"agg_id_here","offer_id":"offer_id_here","title":"furniture","description":"description","link":"link","additional_image_links":[],"content_language":"en","target_country":"GB","channel":"online","google_expiration_date":"2021-05-31 22:00:27.517 UTC","availability":"in stock","brand":"brand","color":"black","condition":"new","custom_labels":{"label_0":"GBP"},"item_group_id":"id","mpn":"mpn_id","price":{"value":"129.99","currency":"GBP"},"google_product_category_ids":[],"product_type":"headboards","additional_product_types":[],"destinations":[{"name":"Shopping","status":"approved"}],"issues":[]}
google-apps-script json javascript
2 Answers

As doubleunary said, you are likely missing .getContentText()

Summary: That the response from:

let response = UrlFetchApp.fetch(url, {'muteHttpExceptions': true});

Is an HTTPResponseObject, so trying to parse it as a string or JSON won't work. To fix it you need to add in a call to getContentText().

Except doubleunary's answer won't work because the result is BigQuery newline delimited JSON, so you will still need to split it by newlines as you have suggested.

So just adding that step:

function myFunction() {

let url = 'url_address';
let storedData = [];
let response = UrlFetchApp.fetch(url, {'muteHttpExceptions': true});
let contentText = response.getContentText();
let storedResponse = contentText.split("\n");

storedResponse.forEach((data) => {
   storedData.push(JSON.parse(data));
});
}

Should solve your issue, give it a try (seeing as I can't test it).

Reference

Edit:

Its possible that you may get a few JSON lines being invalid due to the presence of a newline character that is within the JSON.

A way to minimize this would be to split by a closing curly brace and a newline "}\n". Though in theory, a JSON line could also contain this.

function myFunction() {

let url = 'url_address';
let storedData = [];
let response = UrlFetchApp.fetch(url, {'muteHttpExceptions': true});
let contentText = response.getContentText();
let storedResponse = contentText.split("}\n");

storedResponse.forEach((data) => {
   storedData.push(JSON.parse(data + "}")); // you would need to add the ending brace again to parse it
});
}

Another approach you could take, if the JSON structure is always the same, is to use a regex like this one:

^{.+"issues":\[.+\]}$/gm

Which looks for chunks of text that start with a newline, and { with a variable amount of content inbetween, and then having "issues":[ with another variable about of data and finally ]}\n. This should catch most instances of possible newlines within a JSON that are causing the malformed JSONs coming out the split. The gm is the global flag allowing multiple results and the m is the multiline flag that allows you to identify line starts and ends with the ^ and $ characters.

You could implement it with string.match:

function myFunction() {

let url = 'url_address';
let storedData = [];
let response = UrlFetchApp.fetch(url, {'muteHttpExceptions': true});
let contentText = response.getContentText();
let storedResponse = contentText.match(/^{.+"issues":\[\]}$/gm);

storedResponse.forEach((data) => {
   storedData.push(JSON.parse(data));
});
}
user_104450170

UrlFetchApp.fetch() will get you an HTTPResponse object rather than a JSON string. You will need to get the contents of the response like this:

  const response = UrlFetchApp.fetch(url, {'muteHttpExceptions': true});
  const str = response.getContentText();
  const jsonObj = JSON.parse(str);
user_130451930
Related