Reg JSON Object

Question

Hi All, 
 Thanks in advance for the Help. 
 I am trying to store the a Json String ex: "{"a":null,"b":2}" into a column in MySQL of type JSON. 
 but when i call a rule which returns the json string and pass it to the write to data store entity , it is failing to store JSON string saying "invalid JSON text"as the 
 input is getting changed to [a:,b:2] without the quotes. 
 Could you please suggest how to preserve the quotes in transfer and storing the value? 
 appreciate your help. 
 Thanks Raj

Robert Shankin · Accepted Answer

Hi Raj - 
 I also had some difficulty performing this save in process. Have you opened a ticket with Appian support for this? Perhaps you should and let us know here what the solution is. Note: I was able to complete the write to data store from SAIL though. You just need to format the text very carefully. See the following example. 
 a!writeToDataStoreEntity(
 cons!json_table_DSE,
 'type!{urn:com:appian:types}json_table'(
 id:3, 
 jsoncol:"{"&chr(34)&"a"&chr(34)&":null,"&chr(34)&"b"&chr(34)&":2}"
 )
)

OR

a!writeToDataStoreEntity(
 cons!json_table_DSE,
 'type!{urn:com:appian:types}json_table'(
 id:3, 
 jsoncol: a!toJson({"a":null,"b":2})
 )
) 
 Finally, I'm not sure about your use case, but, if you do get the solution for process, keep in mind that this approach could arguably increase the size of your process variables, and thus increase the footprint of each of these processes. Another solid approach could be to store the variables in the DB individually and to later construct the JSON when it's actually needed.

PhilB · Answer

I'm not sure if this will help, but it might be worth mentioning how to pass a json string when you're testing expressions, as you need to escape the quotation marks rather carefully. 
 So, for example, to produce a JSON string of {"a":null,"b":2} then you would need to use "{""a"":null,""b"":2}" within an expression. Note that the quotes within the JSON string have been entered twice - this can be tricky to do as the designer auto-adds multiple quotes when doing this, so be very careful when entering the values. 
 An alternative to the above is to convert to JSON from a dictionary, which can be easier than messing around with quotes. In your example, the following would get the JSON string that you require: 
 a!toJson( { a: null, b: 2 } ) 
 The above applies to other, but not all areas of Appian; for example in the integration designer you need to escape the JSON string in this way, but not in the Value field of an expression rule input. You do, however, need to escape in this way in the Expression field for an expression rule input.