0

I need to store some data with values like 0, 1 (true / false). I am considering grouping the different integer fields into one JSONB field with a structure like this:

{
  "status": 1,
  "approved": 0,
  "notified": 0
}

Is the speed of queries (with WHERE) the same when using JSONB vs. normal integer fields to filter the data?

  • 2
    If these are always the same fields/keys, don't do that. Use boolean for boolean values, in a properly normalized way. Also, you can try this easily for yourself, just set up the same data in both ways. – dezso Mar 3 '18 at 23:20
2

Is the speed of queries (with WHERE) the same when using JSONB vs. normal integer fields to filter the data?

Of course not. The JSONB adds indirection. You have to resolve the key to the value.

That said, if you create an index on a boolean column, or a jsonb's boolean key it should be the same speed for an index lookup.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.