Unexpected non-whitespace character after JSON at position 123

I am getting this error when running parseJSON() on a column with json objects:

parseJSON([jsonColumn]) is returning error “Unexpected non-whitespace character after JSON at position 123”. 123 is always the length of the text of the first object of the json object. For example if there is a json object with 5 items in it, and it says error at position 123, then

thisRow.[jsonColumn].First().ToText().Length() = 123

Here is an image of the error constructed in Coda

However parseJSON([jsonColumn].nth(int)) works for any location in the json.

Why does it return this error, and why is it after the first entry?

I’ve run lleft([jsonColumn],123) and it returns exactly the first entry (see image left to first length, and the string ends with "} which is what would be expected, so its saying the unexpected non-whitespace character is the comma?

Why is this an error it seems like the right thing.

you dont show us the entire json string.
what is the last character?
is the json string correctly formed json syntax?

perhaps you might copy/paste the string into a json validator like

when i get parseJson() errors, it is usually because i have mal-formed json strings

@Xyzor_Max I have used a validator. If I [jsonColumn].totext()and paste it in, it fails. If I concat an [ and a ] to both sides of the .totext() and paste it in, it passes.

When I construct the json in CFL I concat the ‘[’ ‘]’ in, like:
concat("[",object(),"]") and CFL takes it.

What can I give you to help with this one?

the jsonColumn seems to be a list of values rather than a single value.

the jsonColumn.toText() shows a LIST of comma seperated {…} objects - not a single object.

so you need to do parseJson(“[”+jsonColumn.toText().join(“,”)+“]”) to get well-formed json to parse

does that fix it?

1 Like

@Xyzor_Max No it hasn’t fixed it. I think my original json is constructed correctly (however I haven’t used join()) but to be sure I created a new column that restructured it according to your instructions. I’m still getting the error. Here’s a gif that shows the setup:


i seem to recall that parseJson does NOT like the outermost element to be an array (ie […] ) but instead wants the outermost element to be an object (ie {“o”:[…]} ).

so i think you need to wrap the json like this

parseJson( '{"o":[' + jsonColumn.toText().join(',') + ']}'

and then extract the “o” object to get the array

OK awesome. That worked, and its allowing me to use jsonPath to filter! as described by you and @Paul_Danyliuk here: Filtering with ParseJSON - #2 by Paul_Danyliuk

So that’s great! But I’m curious now how to ‘do json right’ in coda? For example I have this jsonHistory column in my table. When I have a new entry, I am using object() to create the newJson, then I am using listcombine(newJson,jsonHistory) to append it.

This has been working (except for this use case). Should I be using join() instead of listcombine()? Do I need to add the {“o”:[ … ]} each time? What is the best practice here @Xyzor_Max ?

OK I’ve played with this for a bit. My findings:

  1. If I take the json and write it to a row as text (such that Coda recognizes the cell value as text and not as an object/list), I can use parsejson() with jsonpath in a separate column to extract what I want out of it.

  2. However, if I write it to a row as json (modifyrows(thisrow,col, parsejson(json)), trying to run a jsonpath with parsejson() is what induces the non-whitespace character error.

Which kind of makes sense, because you’re running parsejson()on text twice. It would be nice if this failed more gracefully.

If I enclose it in the {“o”: that you suggested, you can write it as json, and then use parsejson() on it again, but with jasonpath you have to use the double period $… to search through it.

It would be nice if a @codan could provide some guidance and best practices on how to do this properly.

My conclusion (and tell me if I’m wrong) is that the best practice here is to ‘save’ the json as text in the row, and then to reference it I can always run parsejson() when needed.

you might be able avoid the double encoding to/from text by using the hidden function deref_object() directly on the source object, to extract the item you need. i think it takes standard json-path as an argument, but i am not sure.

of course, your doc becomes unsupported once you use it.

1 Like

What does deref_object() do? How should I think of it in this workflow @Xyzor_Max ?

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.