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

Question

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()?

aljazerzen · Accepted Answer · 2017-09-26 10:08:23Z

6

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.

answered Sep 26, 2017 at 10:08

aljazerzen

4911 gold badge5 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Efog Over a year ago

It will work if you need to check for equality. But in case of [Op.Like], for example, it isn't helpful.

mdmb Over a year ago

How would you do it if the jsonb_field object has more than just inner_field1, and you'd like to e.g. search for a Foo where Bar.jsonb_field.inner_field2 is equal to xyz?

b9s · Accepted Answer · 2023-09-21 13:22:49Z

0

Sequelize has better support for JSON columns nowadays, I think since v6, see https://sequelize.org/docs/v7/querying/json/

For example:

FooModel.findAll({
  where: {
    'jsonb_field.inner_field': 'text to find',
  },
});

Or, when the inner field can be one of several options:

FooModel.findAll({
  where: {
    'jsonb_field.inner_field': { [Op.in]: ['text to find', 'other text to find'] },
  },
});

answered Sep 21, 2023 at 13:22

b9s

5774 silver badges15 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related