Part of JSON with SQL query being converted to [object Object]

Discussion
,
1

I have a workflow where I have a JSON with SQL resource transforming the results out of a REST API call to prep it for a bulk insert to a SQL database. The query outputs the data exactly as I need it:

image
image618×743 26.8 KB

The first 4 columns are constant and directly mapped from the API results, the problem is with the column AppendResults highlighted in the screenshot. This section has a variable number of columns included depending on the output from the API. I want to store the AppendResults to the database exactly as it appears in the JSON SQL query. The first four columns populate correctly in the db however the AppendResults is producing the [object Object] reference when writing to the database, even though in the hover on the array in the workflow looks perfect.

image
image485×693 19.5 KB

I have tried JSON.stringify function on the JSON SQL query but that produces the [object Object] reference in the query output. What can I do to cleanly persist the AppendResults column from the JSON SQL into the SQL database?

1 Like
2

What is the definition of the field that is storing the AppendResults array? Are you trying to store an array (or JSON) or as a text representation of the array?

3

It is nvarchar(max) in an Azure SQL database. I am trying to store the array exactly as it appears when I hover on the datasource for the bulk insert. But the array is written to the db as the [object Object] reference.

4

JSON.stringify() is the right way to go. You said you tried it, but did you do it on the whole result of the jsonXForm query or just on the data in the AppendResults field? I would think a transformer on the result of the jsonXForm would work. Something like:

const modifiedArray = data.map(obj => {
  return {
    ...obj,
    AppendResults: JSON.stringify(obj.AppendResults)
  };
});

return modifiedArray

Is that what you tried previously?

5

It was on the whole result of the JSON query not just the Append Results. Let me give that a go and will advise, thank you.

6

I am new to Retool so please bear with me...
I am in a Retool workflow and I added a java script block with the function you suggested, but I am not clear how to leverage the function in the output of the the JSONXForm. What am I missing or is this something that can only be done in an app and not a workflow?

7

No worries - that's what the forums are for. I forgot you were in a workflow - in that case, the JavaScript Block should be something like:

const data = jsonXForm.data
const modifiedArray = data.map(obj => {
  return {
    ...obj,
    AppendResults: JSON.stringify(obj.AppendResults)
  };
});

return modifiedArray

Give it a whirl and let us know!

8

Nailed it!
That did the trick, thank you so very much!
Appreciate you!

2 Likes