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
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.
@Agile_Dynamics 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.
@Agile_Dynamics 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 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 @Agile_Dynamics ?
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.
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.
