,column <columnName> is of type jsonb but expression is of type text[]

Question

Have array as below, needs to be saved in JSONB column:

[{"FoodType":"veg","pref":"High"}
,{"FoodType":"sea food","pref":"Medium"}
,{"FoodType":"Chicken","pref":"Low"}]

I simply pass the req.body object (from Express) for insert to DB.

db.one('insert into foodies(FoodieName, FoodPref,country,languagePref)' +
'values(${FoodieName}, $[FoodPref], ${country} ,${languagePref})  RETURNING FoodieId',req.body)

**PG DB via pg-promise library throws error :

{ [error: column "foodpref" is of type jsonb but expression is of type text[]]
name: 'error',
length: 196,
severity: 'ERROR',
code: '42804',
detail: undefined,
hint: 'You will need to rewrite or cast the expression.',
position: '123',
internalPosition: undefined,
internalQuery: undefined,
where: undefined,
schema: undefined,
table: undefined,
column: undefined,
dataType: undefined,
constraint: undefined,
file: 'parse_target.c',
line: '529',
routine: 'transformAssignedExpr' }
POST /api/member 500 638.510 ms - 57

i think its driver issue cos array: function (arr) in formatting.js [in pg-promise lib] returns string and postgres cant digest it. If array is nested in any object then it works smoothly e.g.

"MyPref" : {Same Object array as above}

Here MyPref gets through in column "FoodPref" without any issue.

Streusel

You should attach the exact query you're executing. To get the query without execution, use method pgp.as.format. – Zack

I asked you to update the question with the query being generated, by using pgp.as.format, not what you pass into the query method. – Zack

sorry :( cant get the query... please help with sample code and location to get the query generated. I tried response.log in .one method buts returning the same query i had put in the question – Streusel

Call pgp.as.format(query, values), it returns the formatted query string. – Zack

Answer 1

If array is nested in any object then it works smoothly

It points that you are not passing in formatting data correctly. Instead of passing in your data for JSONB as an array, you are passing in an array of internal objects.

If you pass it in as an object property, it just works as you said. To pass it as a parameter inside array, you need to pass it in inside an array:

var data = [{"FoodType":"veg","pref":"High"}
,{"FoodType":"sea food","pref":"Medium"}
,{"FoodType":"Chicken","pref":"Low"}]

query('bla-bla $1', [data])

i.e. your problem is that you are passing it in as:

query('bla-bla $1', data)

which misinterprets the array - JSONB data as an array of values - parameters.

UPDATE

See also this related question.

Zack

So should i stop passing req.body directly and create separate objects for each column??? (check the updated Question for query used for insert) – Streusel

sorry for late reply...solved it by passing individual objects as parameter in place of req.body.... additionally passed the variable in json.stringify which had array of JSON. – Streusel