Hi everyoneI have exteranal Postgresql database, and in the table account_result column 'additional' is jsonb data type. When I try to save a value after serializing local record variable to json and passing json data to the sql insert statement as text, I got an error: column "additional" is of type jsonb but expression is of type text
screenshot_2 - is my flow and variables
screenshot_4 - query how I insert into table
how should I save text as jsonb properly?
In this case you need set Expand Inline Property Yes so it can accept special characters.
If you want avoid SQL inject warning when Expand Inline then user Create Action From entity.
Also check Attribute length also when you try to insert record.
Hello Rahul Sahu
Appreciate your feedback. I changed Advanced SQL to Create Action from entity as others also sujecsted to do this, but still getting an error: column "additional" is of type jsonb but expression is of type text
and also tried to set Expand Inline to Yes in additional query param, but now getting another error: syntax error at or near "{"
I will be great to get some ideas.
You can not save json directly in database. Either you need to use text datatype or binary datatype in your database.
JSON support might be implemented in future
https://www.outsystems.com/ideas/5310/json-datatype-in-outsystems/
Hi Alex,
could you please try to use create or update outsystems entity action to add JSON
Regards
Shradha Rawlani
Hello Shradha Rawlani
I have tried out that what you said, but got same error
did you change the datatype of column from jsonb to text ?
no, I could try to talk with team to change datatype to text, but want to be sure that there is no other way to work around with json datatype. So I do my research and asking people to get some ideas in this forum
Unfortunately, I don't think there is a way. As outsystems support strict datatypes so json is something out of scope right now.
I tried lots of things but no success. If someone done this please correct me and provide way to directly save json datatype in db. Text and BinaryData is only the options to save unstructured data as far as I know.
If you are using Postgres you can try BinaryData type and pass it to JSONB. i am not sure it work or not but you can try this.
I tired odc as well but no success.
I appreciate you sharing your story. After that, I'll go persuade the team to change the data type.
Hello,
In my opinion, I would store it as a string using JSON.stringify(), and when needed, convert it back to JSON using JSON.parse() or handle it as a list object.
I hope this helps. Your case doesn't seem too complicated.
Have a great day!
Best regards.