JSON error -- looking for help

[TenTen71]

Trying out Data Merge for the first time in Publisher. I checked my JSON code with an online validator to confirm its syntax but Publisher keeps giving me this error when I try to upload my file...

Error parsing JSON file:
in Json::Value::operator[](ArrayIndex): requires arrayValue

Does Publisher not accept JSON with blank arrays? Is that what this is saying?

[BloodDrop]

This is simply infuriating! I have tried a lot of different JSON structures to get the data, but nothing works. I get the same error as TenTen71 or a "syntax" error, even though my file is fine. If there is some specific structure of a JSON file we should use, could we get the "recipe" in documentation for data merge? If it is not about structure, could someone confirm or deny that the problem exists?

[walt.farrell]

It might help if we had a sample JSON file that exhibits the error.

[Seneca]

3 minutes ago, BloodDrop said:

If it is not about structure, could someone confirm or deny that the problem exists?

I'm not sure whether this helps but Affinity doesn't parse JSON beyond the 1st level. 

From the help File:

• For JSON files, obtained from web/mobile services or database export, only a single top-level array of objects (and values in those objects) are supported; lower level arrays and objects are not supported.

[Old Bruce]

I am just more curious than anything else regarding the use of JSON files. I too have tried using them and come up with the same error. I have also searched the web with DuckDuckGo and found nothing other than the statement from Serif/Affinity saying the Merge function can use JSON files. No examples of it see to exist. What I would like to see are some (simple) example JSON files which do work.

[BloodDrop]

These files are so simple, it does not require any complicated files to recreate the problem. The most basic json file should work, even if resulting in a lacking data structure. With CSV you can simply put some words, separate them with commas, and you can see how it is interpreted by Publisher. Then you can make adjustments. In the json case, I don't even know how it is understood by the application. I started with the simplest example:

{
  "Name": "Test",
  "Mobile": "12345678",
  "mail": "atsomething"
}

I have tried making the whole thing an array using "[]" as the top level. I have tried changing the values into arrays. I have tried changing the names into an array. But nothing wanted to compile. I have even used a table-structure with the json syntax, knowing only the first level would be parsed.

I agree with Old Bruce, it would be enough if we got a working json file as a reference, instead we are trying to wrap our heads around it and wasting time.

[walt.farrell]

35 minutes ago, Old Bruce said:

I am just more curious than anything else regarding the use of JSON files. I too have tried using them and come up with the same error. I have also searched the web with DuckDuckGo and found nothing other than the statement from Serif/Affinity saying the Merge function can use JSON files. No examples of it see to exist. What I would like to see are some (simple) example JSON files which do work.

Here is a very simple example that works for me. The .zip contains a .afpub file and two forms of the same data file to be merged. One is a .csv file, and the other a .json file created by an online conversion program from the .csv file.

SimpleDataMerge.zip

[Old Bruce]

1 minute ago, walt.farrell said:

Here is a very simple example that works for me. The .zip contains a .afpub file and two forms of the same data file to be merged. One is a .csv file, and the other a .json file created by an online conversion program from the .csv file.

 

Nothing here that I can see...

[walt.farrell]

12 minutes ago, BloodDrop said:

I started with the simplest example:

{
  "Name": "Test",
  "Mobile": "12345678",
  "mail": "atsomething"
}

 

Here is how the JSON file I converted from CSV ended up, and it works:

[
{"First":"Walt","Last":"Farrell","Telephone":"555"},
{"First":"Marjorie","Last":"Farrell","Telephone":"666"}
]

 

[walt.farrell]

1 minute ago, Old Bruce said:

Nothing here that I can see...

Sorry. Look again.

[walt.farrell]

18 minutes ago, BloodDrop said:

I started with the simplest example:

{
  "Name": "Test",
  "Mobile": "12345678",
  "mail": "atsomething"
}

Yours will work if you put [ and ] around that whole thing:

[
{
  "Name": "Test",
  "Mobile": "12345678",
  "mail": "atsomething"
}
]

 

[BloodDrop]

Ok, that is interesting

Thanks Walt, that does explain a lot. Apparently, we need an array ( [ ] ) in the first level, and then every "row" of our table is an object ( {} ) containing a field:value pair for each cell in the row.

Yep, this should definitely be included in documentation for Publisher.

Thanks again Walt, helpful as always

 

[Seneca]

1 hour ago, BloodDrop said:

Yep, this should definitely be included in documentation for Publisher.

And it is.

See my earlier post.

From the help File:

• For JSON files, obtained from web/mobile services or database export, only a single top-level array of objects (and values in those objects) are supported; lower level arrays and objects are not supported.

[BloodDrop]

8 minutes ago, Seneca said:

And it is.

See my earlier post.

From the help File:

• For JSON files, obtained from web/mobile services or database export, only a single top-level array of objects (and values in those objects) are supported; lower level arrays and objects are not supported.

No,no, I mean the file structure should be included or an example file. This definition alone is not clear enough. It is easy to interpret it as the correct answer, if you already have the answer  Notice, the basic json structure meets the requirement as well, yet is not right for this application. Anyway, it works and I hope others will find the answer here useful.

[LS_67]

Hi, I'm just new with Affinity products.

I don't know if you resolved this problem, but in my case the fusion doesn't recognise JSON files.

I have read this post and the documentation about the first level...

My file looks like the same Walt describes, as a collection of objects, with just one level, but this is not accepted 🤔

 

As a feature request with this system, it would be very cool to get JSON Data as an URL (HTTP Get), so that at the refreshment of the document always take the latest version of the customer's Data to place in it.

here a JSON file which gives an error on Line 1 / Char 1

If someone could explain me what's wrong in there, this will really help me.

Luc

data.json

[walt.farrell]

18 minutes ago, LS_67 said:

If someone could explain me what's wrong in there, this will really help me.

Your data was saved in UTF-8 (good) but with a BOM character (Byte Order Mark) as the first byte (bad).

Save it without the BOM and it will work.

data-nobom.json

[LS_67]

Hi Walt,

I've generated a new file without the BOM and it worked well !

This should perhaps be added in the documentation.

 thank you for your help.