JSON generation in view columns

REM {list of fields where we should return an array, even for one value};
_fieldsWithArrays:=@LowerCase("form");
REM {list of fields to exclude from the output};
_exclude:=@LowerCase("$FILE":"$Fonts":"$papercolor":"$papercolorex":"received":"body");


REM {GMT timezone for time conversion to ISO8601};
_GMT:="Z=0$DO=1$DL=3 -1 1 10 -1 1$ZX=37$ZN=GMT";
REM {escape list for special characters in JSON};
_jsonFrom:=  "\\"       :   "\""      :     @Char(10)         :     @Char(9);
_jsonTo:=      "\\\\"     :   "\\\""    :     "\\n"                     :     "\\t";

REM {build list of fields to iterate over};
_fields:=@Unique(@Sort(@Trim(@ReplaceSubstring(@LowerCase(@DocFields);_exclude;""))));

REM {start generating the JSON object};
_json:="{";

@For(n := 1; n <= @Elements(_fields); n := n + 1;
  _value:=@GetField(_fields[n]);

_localDT:=@If(!@IsTime(_value);"";@TextToTime(@TimeToTextInZone(  _value  ;  _GMT  )) );

_dateVal:=@If(
         !@IsTime(_localDT);"";@Day(_localDT)<1;"";
         @Right("000"+@Text(@Year(_localDT));4)+"-"+@Right("0"+@Text(@Month(_localDT));2)+"-"+@Right("0"+@Text(@Day(_localDT));2)
);
_timeVal:=@If(
         !@IsTime(_localDT);"";@Hour(_localDT)<1;"";
         @Right("0"+@Text(@Hour(_localDT));2)+":"+@Right("0"+@Text(@Minute(_localDT));2)+":"+@Right("0"+@Text(@Second(_localDT));2)
);
_iso8601DT:=@If(!@IsTime(_localDT);"";_dateVal+@If(_timeVal="";"";"T"+_timeVal+"+00:00"));

_valueAsStr:=@If(@IsTime(_localDT);"\""+_iso8601DT+"\"";
                                @IsNumber(_value);@Text(_value);
                                 "\""+@ReplaceSubstring(@Text(_value);_jsonFrom;_jsonTo)+"\""
                          );
_json:=_json+@If(n>1;",";"");
_json:=_json+"\""+_fields[n]+"\":";
_json:=_json+@If(@Elements(_valueAsStr)>1 | @IsMember(_fields[n];_fieldsWithArrays);"["+@Implode(_valueAsStr;", ")+"]";
                              _valueAsStr)
);

_json:=_json+"}";
_json





I hope that all characters are properly escaped. Any feedback is welcome if I missed something.

Formula
Karsten Lehmann
December 6, 2013 9:16 AM
Rating
70

All code submitted to OpenNTF XSnippets, whether submitted as a "Snippet" or in the body of a Comment, is provided under the Apache License Version 2.0. See Terms of Use for full details.



No comments yetLogin first to comment...