Monday, February 15, 2016

Postgres search - overlapping arrays

Postgres Overlapping Arrays

We've got some spare food hanging around that we need to get rid of, but have to figure out which of our animals will eat it. Luckily we've recorded what each of them eat in our database! Given the three animals

{ 'species': 'cat', 'name': 'frank', 'eats': ['fish', 'biscuits', 'pudding'] }
{ 'species': 'dog', 'name': 'frankie', 'eats': ['fish', 'vegemite', 'cat'] }
{ 'species': 'snake', 'name': 'francine', 'eats': ['biscuits'] }

We want to find who will take the fish and pudding that we have sitting around. We'll get them out of their cage and bring them to the one spot to feed them. How can we figure this out from our data?

If the document was modelled to it's 3rd normal form in sql we could do some joins and figure it out without too much drama. To do this in Postgres with our jsonb storage a simple way to achieve the same outcome is to store the values we want to search on - eats in our case - is to store those values in their own column as an array (in addition to the jsonb column) and then use the Postgres array comparisons to do a search where the eats column contains any of ['fish', 'pudding']. What does this look like using Massive?

db.run(`select * from animals
  where eats && $1`,
  ['fish', 'pudding'],
  (err, results) => {
    // do something interesting with the results
})

Note that you'll need to add the extra column manually and populate it when saving a document:

db.animals.insert({body: jsonDocument, eats: jsonDocument.eats}, (err, results) => {})

No comments:

Post a Comment