Querying JSONB field on a associated model with Sequelize.js and PostgreSQL

user_2619989 0
Viewed 7166

I have my two models Foo and Bar. Foo has a field barId, therefore has one Bar object associated with it. I can query all my Foo objects and include their assiciated Bar object as so (I am using TypeScript with sequelize-typescript): 

Foo.findAll<Foo>({
  include: [{ model: Bar }]
});

Bar object has a JSONB field jsonb_field with structure 

{ inner_field1: 'some text', inner_field2: 'some more text' }

I can query Bar objects and filter by inner_field1 as such:

Bar.findAll<Bar>({
  where: { 'jsonb_field': { inner_field1: 'text to find' } }
});

This produces following SQL query: 

SELECT ... FROM "Bar" AS "Bar" 
WHERE ("Bar"."jsonb_field"#>>'{inner_field1}') = 'text to find'

So far so good. Now let's try querying Foo objects, include Bar objects and filtering by inner_field1:

Foo.findAll<Foo>({
  where: { '$bar.jsonb_field$': { inner_field1: 'text to find' } },
  include: [{ model: Bar }]
});

Now this throws an exception:

Error: Invalid value [object Object]
    at Object.escape ({project_root}\node_modules\sequelize\lib\sql-string.js:50:11)
    at Object.escape ({project_root}\node_modules\sequelize\lib\dialects\abstract\query-generator.js:917:22)
    at Object.whereItemQuery ({project_root}\node_modules\sequelize\lib\dialects\abstract\query-generator.js:2095:41)
    at _.forOwn ({project_root}\node_modules\sequelize\lib\dialects\abstract\query-generator.js:1937:25)
    ...

For the record, I am including the Bar object correctly, because I can filter by other non-JSONB properties as such:

Foo.findAll<Foo>({
  where: { '$bar.number_field$': 5 },
  include: [{ model: Bar }]
});

As far as I know, the problem lies in Sequelize not being aware of the type of jsonb_field so it throws an error when an object is passed to the where query.

Is there a way around this error, maybe using sequelize.literal() or sequelize.json()?

sequelize.js typescript node.js postgresql
1 Answers

Use sequelize.cast and $contains operator:

Foo.findAll<Foo>({
  where: { '$bar.jsonb_field$': {
    $contains: sequelize.cast('{ "inner_field1": "text to find" }', 'jsonb')
  },
  include: [{ model: Bar }]
});

Or do it using sequelize.literal as you suggested:

Foo.findAll<Foo>({
  where: { '$bar.jsonb_field$': {
    $contains: sequelize.literal(`'{ "inner_field1": "text to find" }'::json`)
  },
  include: [{ model: Bar }]
});

Both solutions are vulnerable to SQL Injections, to make sure to escape or remove all characters that could cause problems (",',...)

Yes, I have just answered my own question. You are welcome.

user_26199890
Related