Mulesoft Postgresql Error inserting JSON into column

Question

I have a Mulesoft flow that is inserting data into a PostgreSQL table. The data is of type JSON. Here is a copy of the data: (I grabbed this from the debugger as it is about to call the insert command)

[
  {
    "id": {
      "type": "MyType",
      "value": "MyValue"
    }
  }
]

When I try and insert it into a JSON column I get this error

"ERROR: invalid input syntax for type json
  Detail: Token "id" is invalid.
  Where: JSON data, line 1: [{id..."

So I decided to insert it into a TEXT column and this is what it looks like

[{id={type=MyType, value=MyValue}}]

Notice the addition of an '=' after 'id' instead of the ':' and the removal of the quotes.

I am not sure why this is happening. Basically I just need to insert the JSON data into a JSON column.

Thanks

score 0 · Answer 1 · answered Aug 13 '21 at 14:40

Probably what Mule consider a JSON is not matching what Postgresql expects for a JSON field. You'll need to do some conversion.

What you see in the text field is just a serialization of the object that DataWeave, the expression language of Mule 4, uses internally. You would see the same if you use a logger in your application. DataWeave doesn't store internally JSON or XML objects. It reads JSON or XML inputs into an internal representation (in Java), transforms it and writes it in JSON, XML or another supported format.

In your case to output something in JSON format as a string you can use the DataWeave function write() to output to Java:

#[ output application/java --- write(vars.jsonData, "application/json") ]

Then adapt the SQL query to convert an input from a string to the JSON type that Postgresql uses using Postgresql's SQL function to_json(). This is explained in a different question: https://stackoverflow.com/a/35847081/721855

Mulesoft Postgresql Error inserting JSON into column

1 Answers1