How to postgresql sequelize query on json array

Question

How to write the sequelize where query on medium and subject over the PostgreSQL table in NodeJs?

E.g. select * from tablename where params.medium IN("Hindi", "English") AND params.subject IN("Hindi","Science");

here table column name is params

Data in params column:

{
   "board":"CBSE",
   "medium":[
      "Hindi",
      "English",
      "Urdu"
   ],
   "subject":[
      "Hindi",
      "Mathematics",
      "Science"
   ]
}

check out this thread stackoverflow.com/a/48369512/12761193

Aryan
– Aryan

2020-09-19 05:35:26 +00:00
Commented Sep 19, 2020 at 5:35 — Aryan
– Aryan, Commented Sep 19, 2020 at 5:35
@Arya thanks for the solution. you are my lifesaver :-)

Amol Ghatol
– Amol Ghatol

2020-09-20 12:34:38 +00:00
Commented Sep 20, 2020 at 12:34 — Amol Ghatol
– Amol Ghatol, Commented Sep 20, 2020 at 12:34

Amol Ghatol · Accepted Answer · 2020-09-20 12:40:04Z

1

I have done as following

   const Op = Sequelize.Op;


    const filter = {
      [Op.and]: [{
        [Op.or]:[
          {
            'params.medium': {
              [Op.regexp]: 'Hindi'
            }
          },
          {
            'params.medium': {
              [Op.regexp]: 'English'
            }
          }
        ],
      },
      {
        [Op.or]:[
          {
            'params.subject': {
              [Op.regexp]: 'Hindi'
            }
          },
          {
            'params.subject': {
              [Op.regexp]: 'Science'
            }
          }
        ],
      }
    ]};

    model.findAll({
        where: {
          ...filter
        }
   )};

Please suggest if any better solution.

answered Sep 20, 2020 at 12:40

Amol Ghatol

3195 silver badges8 bronze badges

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

Collectives™ on Stack Overflow

How to postgresql sequelize query on json array

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related