Querying postgresql using sequelize ORM for an nested JSONB object

Question

This is how the JSONB looks into the database

matrix:{
  toolsMethodsOther:{
    networking: [],
    testing: {
      testingGeneric: [],
      testingTools: []
    },
    developmentTools: [],
    developmentDesignTehniques: [],
    developmentOnSpecificAreas: [],
    serverAdministration: {
      administration: [],
      microsoftProject: []
    },
    managementLeadership: [],
    developmentMethodologies: [],
    industryExperience: []
  }

}

This is how an item from an array looks like:

item:{
  item: "Test item",
  year: 2016,
  skill: 4,
  lastYear: 2014
}

Let's say that i want to get all rows WHERE toolsMethodsOther-> networking contains an object of this form:

item:{
  item: {
    $like: "Test"
  },
  year: 2016,
  skill: 4,
  lastYear: 2014
}

Can you guys point me into the right direction? I've read their documentation about querying JSONB but has no success in getting any results other than an emptry result with 0 rows. Also, google did not help with much ...

Thank you in advance!

Welcome to Stack Overflow! You can read up on how to How to Ask a question and create a minimal reproducible example. That makes it easier for us to help you. — Katie
– Katie, Commented Jan 31, 2017 at 15:02

Lachezar Raychev · Accepted Answer · 2017-06-09 11:46:09Z

1

The example is for multiple WHERE clause with regular columns combined with JSONB field matches.

Try this:

where:{
  field1: 'value1',
  field2: 'value2',
  $and:[
   Sequelize.where(
        Sequelize.literal('"tableName"."jsonbColumn"->>\'keyInsideJSONB\''),
        valueWhichYouWantToMatch
   ),
   Sequelize.where(
        Sequelize.literal('"tableName"."jsonbColumn"->"nestedJsonKey"->>\'keyInsideNestedJSONBKey\''),
        nestedValueWhichYouWantToMatch
   )
 ]
}

Hope it helps.

answered Jun 9, 2017 at 11:46

Lachezar Raychev

2,1234 gold badges24 silver badges35 bronze badges

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

Collectives™ on Stack Overflow

Querying postgresql using sequelize ORM for an nested JSONB object

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related