4 ms·

I have a very dumb question: why would you use this instead of a traditional schema? I thought the value of json columns was to be partially schemaless and flex

I have a very dumb question: why would you use this instead of a traditional schema? I thought the value of json columns was to be partially schemaless and flexible What is the use case for this versus normal column definitions, if you’re looking to enforce schemas?

spprashant 4y ago
Not the author. Developers sometimes really just want to dump the data as a JSON. For them this means not having to write a lot of boiler plate ORM or SQL templates, and shipping trivial features quickly. Example, user UI preferences are a good candidate for something like this. You probably don't want to add a new column just to remember the status of checkbox, when the user last logged in. As a DBA, you probably still want to define a schema for this data, so as to not cause unexpected web app crashes. It ensures the some level of data consistency without increasing the maintenance overhead. Obviously you wouldn't use it for business critical data, in my opinion.
- edmundsauto 4y ago
  Ah gotcha, so the use case is when the frontend wants to send JSON and there is no backend dev around to munge things into a standard postgres schema in order to save it? Honestly, it seems like a middleware layer would suffice in order to stick with more "standard" postgres features. (not to indict this feature, but simply because its more likely a maintainer would understand and have experience)
  - gregplaysguitar 4y ago
    There’s a lot of value in keeping data in the same format everywhere. Why “munge” it into some other format if there’s no actual need?
shroompasta 4y ago
I'm using a pg json column to store data as a second source of truth for data that is primarily held on a separate microservice. The schema on that end is pretty intricate but to prevent hitting two services for certain types of data, we just dump it to a json column. Furthermore, for a personal project of mine to help me with productiving / daily schedules, i'm using a json column for a to-do list in the schema of {[some_todo_item]: boolean,} which can't traditionally be represented in pg columns as the to do items are variable.
- edmundsauto 4y ago
  Do you need schema to be enforced? I guess my question was: "why use a JSON column with an enforced schema to save data, when perfectly good normal pg schema are available?" Totally get why you would want to just save data in whatever format you send it in, that's how I prototype as well. But a regular column has the advantage of familiarity with other devs, not to mention better syntax for querying.
gregplaysguitar 4y ago
“Flexibility” isn’t why nosql patterns are useful - at least not in the sense of fewer constraints on your data. Imagine you’re writing an editing interface of some sort with images, text blocks etc. Each is conceptually a “node” but have different attributes. A single node table with a column per attribute means redundant columns. A table per node type introduces other problems because you need to query all your nodes together. Basically however you model this you end up with tradeoffs and real apps can obviously get much more complex - next thing you have 50 tables and a complex query just to get the data out. Contrast to the other extreme - storing the whole thing in a single hierarchical json document. There’s no restriction on the data shape, and you can just pass the whole thing around as json. Versioning becomes much simpler because you’re versioning a single document. Your export format is just the json. There’s tradeoffs of course, and often a middle ground is the right approach - but json columns with validation definitely have their place.

spprashant 4y ago

Not the author. Developers sometimes really just want to dump the data as a JSON. For them this means not having to write a lot of boiler plate ORM or SQL templates, and shipping trivial features quickly. Example, user UI preferences are a good candidate for something like this. You probably don't want to add a new column just to remember the status of checkbox, when the user last logged in. As a DBA, you probably still want to define a schema for this data, so as to not cause unexpected web app crashes. It ensures the some level of data consistency without increasing the maintenance overhead. Obviously you wouldn't use it for business critical data, in my opinion.

edmundsauto 4y ago
Ah gotcha, so the use case is when the frontend wants to send JSON and there is no backend dev around to munge things into a standard postgres schema in order to save it? Honestly, it seems like a middleware layer would suffice in order to stick with more "standard" postgres features. (not to indict this feature, but simply because its more likely a maintainer would understand and have experience)
- gregplaysguitar 4y ago
  There’s a lot of value in keeping data in the same format everywhere. Why “munge” it into some other format if there’s no actual need?

shroompasta 4y ago

I'm using a pg json column to store data as a second source of truth for data that is primarily held on a separate microservice. The schema on that end is pretty intricate but to prevent hitting two services for certain types of data, we just dump it to a json column. Furthermore, for a personal project of mine to help me with productiving / daily schedules, i'm using a json column for a to-do list in the schema of {[some_todo_item]: boolean,} which can't traditionally be represented in pg columns as the to do items are variable.

edmundsauto 4y ago
Do you need schema to be enforced? I guess my question was: "why use a JSON column with an enforced schema to save data, when perfectly good normal pg schema are available?" Totally get why you would want to just save data in whatever format you send it in, that's how I prototype as well. But a regular column has the advantage of familiarity with other devs, not to mention better syntax for querying.

gregplaysguitar 4y ago

“Flexibility” isn’t why nosql patterns are useful - at least not in the sense of fewer constraints on your data. Imagine you’re writing an editing interface of some sort with images, text blocks etc. Each is conceptually a “node” but have different attributes. A single node table with a column per attribute means redundant columns. A table per node type introduces other problems because you need to query all your nodes together. Basically however you model this you end up with tradeoffs and real apps can obviously get much more complex - next thing you have 50 tables and a complex query just to get the data out. Contrast to the other extreme - storing the whole thing in a single hierarchical json document. There’s no restriction on the data shape, and you can just pass the whole thing around as json. Versioning becomes much simpler because you’re versioning a single document. Your export format is just the json. There’s tradeoffs of course, and often a middle ground is the right approach - but json columns with validation definitely have their place.