,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.

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.