Problem extracting nested jsonData?

[cristoslc]

I'm passing a script a parameter that looks like this (shown if you use Get ( ScriptParameter )):

{ "jsonData":{ "idMarkingPeriod":"2014-2015","idRoster":10310,"idSection":"409-1 2014-2015","idStudent":"S0000184","comment":"This is a comment.","created":9/24/2015 4:16:27 PM,"effort":100,"exam":85,"mark":87,"modified":9/24/2015 5:16:27 PM } }

When I try to run jsonGet ( Get ( ScriptParameter ) ; "jsonData" ), the result is a question mark ("?").

The contents of $json.error are:

Expected "," instead of "/" [ch:/] [at:159] [context:created":9/24/2015 4]

Here's the calculation used to created the script parameter:

Let (
[   ~idRoster =  "10300"
;   ~idStudent = "100"
;   ~idSection = "410-M"
]
;   jsonO 
    (   jsonOp ( "idMarkingPeriod" ; "2014-2015") 
    &   jsonOp ( "idRoster" ; ~idRoster )  
    &   jsonOp ( "idSection" ; ~idSection )  
    &   jsonOp ( "idStudent" ; ~idStudent )  
    &   jsonOp ( "comment" ; "This is a comment.") 
    &   jsonOp ( "created" ; Quote ( Get ( CurrentTimestamp ) - 3600 ) )
    &   jsonOp ( "effort" ; "100")
    &   jsonOp ( "exam" ; "85") 
    &   jsonOp ( "mark" ; "87") 
    &   jsonOp ( "modified" ; Quote ( Get ( CurrentTimestamp ) ) )
    )
)

[dansmith65]

This is happening because the created and modified date values are not encoded in the sample json you provided.

But, when I copy and paste the example calculation into the sample file that ships with this project (text expression field), then click "Run this test", the Actual Result field does have the created and modified values encoded.

I've got two guesses about what your issue is:

1. There is something unique about your system's settings that's causing these functions to NOT encode dates. To test this, paste the expression above in a new record of the sample files Test Expression field, then copy the value from the Actual Result field and paste it in http://jsonlint.com/ to see if it's valid.
2. The expression you're using to generate the json isn't exactly like the sample expression in this post. (review the created and modified sections)

[cristoslc]

My bad, I accidentally pasted the code from one of my own attempts to fix it. In the failing code, the timestamps are not Quote()d. Here's the correct version:

Let (
[   ~idRoster =  "10300"
;   ~idStudent = "100"
;   ~idSection = "410-M"
]
;   jsonO 
    (   jsonOp ( "idMarkingPeriod" ; "2014-2015") 
    &   jsonOp ( "idRoster" ; ~idRoster )  
    &   jsonOp ( "idSection" ; ~idSection )  
    &   jsonOp ( "idStudent" ; ~idStudent )  
    &   jsonOp ( "comment" ; "This is a comment.") 
    &   jsonOp ( "created" ; Get ( CurrentTimestamp ) - 3600 )
    &   jsonOp ( "effort" ; "100")
    &   jsonOp ( "exam" ; "85") 
    &   jsonOp ( "mark" ; "87") 
    &   jsonOp ( "modified" ; Get ( CurrentTimestamp ) )
    )
)

[cristoslc]

Ok, after running the calculation result through jsonlint.org and browsing briefly on StackOverflow, looks like dates are a bit of a hassle in JSON (http://stackoverflow.com/questions/4511705/how-to-parse-json-to-receive-a-date-object-in-javascript).

What's the safest way to pass this in FileMaker? Should I GetAsNumber ( Get ( CurrentTimestamp) ) and write that result to the Timestamp field in the database? I'd prefer to avoid needing to Quote() and Evaluate() every field (there's no way to effectively know when one is a date/timestamp field in this part of the app).

[cristoslc]

Ok, after some further testing, GetAsNumber ( Get ( CurrentTimestamp ) ) does appear to work safely inside of FileMaker (the number is JSON-safe, and FileMaker merrily converts it back to a timestamp when inserting it into a database field).

It looks like the value 9/24/2015 also works, but not 9/24/2015 12:00 PM -- I'm guessing it's the colon that renders the JSON invalid.

Unfortunately, my app is getting data from an API, and does not know in advance whether a given field is a date or not. I'll have to go back to the drawing board to figure out a real-world solution we can apply. I'll leave this open in case you want to add some kind of date-safing (or new $json.error), but understand if you consider it beyond the scope of the module (given that JSON hasn't solved it either). Thanks!

[dansmith65]

It turns out this is a bug, but this issue was posted on the wrong project I created an issue for this at: dansmith65/FileMaker-JSON-Functions#34