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

Question

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.

Mikael Lepistö · Accepted Answer · 2018-10-21 11:30:15Z

1

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.

edited Oct 21, 2018 at 11:30

answered Oct 21, 2018 at 11:19

Mikael Lepistö

19.8k4 gold badges73 silver badges74 bronze badges

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

1 Comment

tabish89 Over a year ago

Thanks Mikael. This makes a lot of sense; however, I ended up solving this specific problem/issue by converting the array into a single JSON object becasue in this case there is need for an array. Your comment will definitely be helpful when I encounter the same issue for an array that must be an array.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related