Looking to iterate over JSON in a Sequelize / Postgres DB query to find a matching value

Question

This is my first post here so please be gentle. I am using Sequelize w/ PostgresDB in Node and trying to search for a contact by email using findOrCreate. The email entry is in JSON format (see structure below)...

I want to find a contact by email and iterate over each email address in the JSON array for each contact entry.

I am passing in an email address and a name in the POST payload.

If I cannot find the contact by email I want to create a new contact (this part is easy).

router.post('/gmail/fetchcontact', function (req, res, next){

    Contacts.findOrCreate({
        where: {
          email:  // this is where I am stuck. How do I access this array and loop over?          
        },
        defaults: {
          name: req.body.name // 
        }
      })
      .then(response => {
        if (response[1] === true) {
          console.log('RESPONSE!!!', response[1])
        }

      })

// THIS IS THE STRUCTURE OF THE JSON I AM LOOKING TO ITERATE OVER

[{
          address: '[email protected]',
          primary: true,
          rel: 'http://schemas.google.com/g/2005#home'
        },{
          address: '[email protected]',
          primary: false,
          labels: ['work'],
        },{
          address: '[email protected]',
          primary: false,
          labels: ['play'],
        }]

Any ideas? Any help would be greatly appreciated. Thanks!

Alex Karalanian · Accepted Answer · 2017-07-13 19:25:00Z

1

I found a great solution for this...

First, you must change the datavalue to type: Sequelize.JSONB

Then you can iterate over nested JSON objects and arrays like so:

 Contacts.findOne({
    where: {
      email: {
        $contains: [{
          address: req.body.email
        }]
      },
      UserId: req.user.id
    }
  })

** Please note that in $contains I wrapped the object in an array

edited Jul 13, 2017 at 19:25

answered Jul 13, 2017 at 18:03

Alex Karalanian

811 silver badge8 bronze badges

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

Comments

LT- · Accepted Answer · 2017-07-06 22:59:41Z

0

I would create an array of just the emails and then do an $in search.

const emails = [{
      address: '[email protected]',
      primary: true,
      rel: 'http://schemas.google.com/g/2005#home'
    },{
      address: '[email protected]',
      primary: false,
      labels: ['work'],
    },{
      address: '[email protected]',
      primary: false,
      labels: ['play'],
    }].map(email => email.address);

Search:

Contacts.findOrCreate({
    where: {
      email: { $in: emails }
    },
    defaults: {
      name: req.body.name // 
    }
  })
  .then(response => {
    if (response[1] === true) {
      console.log('RESPONSE!!!', response[1])
    }
  });

Documentation on $in is here: http://docs.sequelizejs.com/manual/tutorial/models-usage.html#-findall-search-for-multiple-elements-in-the-database

answered Jul 6, 2017 at 22:59

LT-

3711 silver badge8 bronze badges

1 Comment

Alex Karalanian Over a year ago

Thank you much for the reply. Appreciate it., will try out the code and circle back if it works...

Collectives™ on Stack Overflow

Looking to iterate over JSON in a Sequelize / Postgres DB query to find a matching value

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related