CODEWITHSUNDEEP
postgresqlknex.jsbookshelf.js
tabish89
tabish89

Reputation: 371

Using bookshelf.js to query JSON column in a PostgreSQL database

I have a column in the users table of my Postgres dB table that is named email_subscription_prefs, which stores some some information in JSON format. It has an array_length of 1.

Sample Data: [{"marketing":true,"transactional":true,"collaboration":true,"legal":true,"account":true}]

Issue: I am trying to use bookshelf.js ORM to query and search all records in this table based on the value of the marketing key, specifically when its valueis true.

Here is an edited snippet of my code showing what I'm trying to implement this query using bookshelf.js:

return new User()
   qb.where(function() {
     this.where('domicile', 'USA').orWhere('domicile', null)
   })
   qb.whereRaw('cast(email_subscription_prefs->>? as boolean) = ?', ['marketing', true])
   qb.limit(100)
  })

Can someone tell me what I'm doing wrong on qb.whereRaw statement where I'm trying to query the JSON column email_subscription_prefs?

The code returns nothing where there are several thousands records in the users table.

Thanks in advance.

Upvotes: 0

Views: 719

Answers (1)

Mikael Lepistö
Mikael Lepistö

Reputation: 19718

You seem to have an array of objects in sample data instead of single json object.

[
  {
    "marketing":true,
    "transactional":true,
    "collaboration":true,
    "legal":true,
    "account":true
  }
]

so looks like you are trying to refer email_subscription_prefs->>'marketing' which is not found from the array.

To fetch marketing attribute of the first item in the array you should do:

email_subscription_prefs->0->>'marketing'

If that is not the problem, then you would need to add some example data from your DB to be able to tell what is the problem. You current description doesn't describe the queried table well enough.

Upvotes: 1

Related Questions