I've been playing around with this for a while, and I have to say I'm rather stumped.
I have a table called "physical_sites", and created the column "history" as a "json" type in this table.
My API request function is as follows:
const response = await fetch(BASE_URL + "physical_sites", {
method: "POST",
headers: {
"Content-Type": "application/json",
},
body: JSON.stringify({ data: jobSite }),
});
With my incoming data as:
{
"physical_site_name": "Here",
"physical_site_loc": "test",
"created_by": "ME",
"status": "Active",
"history": [{
"action_date": "2022-07-21T01:22:44.056Z",
"action_taken": "Create Job Site",
"action_by": "me",
"action_by_id": 24,
"action_comment": "Initial Upload",
"action_key": "1jt9JPRLy7RHJUwmz3kqoy98u"
}]
}
I will continually be adding items to the 'array' in history, so there will be multiple objects here. I checked this in an online JSON validator, and it seems correct.
Lastly, my "create" function in the controller is as follows:
async function create(req, res) {
const result = req.body.data;
console.log(result);
const data = await knex("physical_sites")
.insert(result)
.returning("*")
.then((results) => results[0]); //insert body data into assets
res.status(201).json({ data });
}
Fairly simple.
However, I keep getting this error:
message: 'insert into "physical_sites" ("created_by", "history", "physical_site_loc", "physical_site_name", "status") values ($1, $2, $3, $4, $5) returning
* - invalid input syntax for type json'
Not exactly sure what is going on, can someone help me understand what I'm missing?
Thanks.
Most likely one of you column for this table has a JSON type. I believe that it is the history column.
You could parse the history property to String before sending the request.
// ...
body: JSON.stringify({
data: {
...jobSite,
history: JSON.parse(jobSite.history)
}
}),
// ...