I am designing some schema in my Postgres 12. And I have an entity (lets call it "Laptop") with an attribute (lets call it "Properties") like:

{ <someKey>: { <someLabel>: <someValue> } }

For example, the laptop has these properties:

{ "cpu": { "count": 8, "model": "Core i7" },
  "disk": { "size": 123456789, "model": "Samsung", "speed": 7200 },

And each laptop has own set of properties...

And now I have to choose how to implement it: as tables or just as a JSON/JSONB column in the table "laptops". What are the criteria / principles when to use JSON/JSONB columns or to design such attributes as tables?

  • 1
    Will you need to query independent items within the JSON string? Yes? Don't use JSON. Do I need the structure clearly defined and no deviation in data elements/types? Yes? Don't use JSON. – bbaird Feb 22 at 15:18

The rule of thumb is: if the data are just payload to the database and will never be used in WHERE conditions and the like, use json. Else, use jsonb.

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