3 ms·

I find if my first idea is to use a JSON field in postgres, I am usually being lazy. Adding schema to the JSON and creating a related table is almost always bet

by dexwiz 10y ago

I find if my first idea is to use a JSON field in postgres, I am usually being lazy. Adding schema to the JSON and creating a related table is almost always better. I try to only use JSON fields for serialized data that will not be modified, mostly record keeping. The JSON functions in postgres are different enough from SQL that I try to avoid them. Writing SQL interspersed with JSON functions becomes clunky, and is not near as powerful as just plain SQL.

scandox 10y ago
When you're using Javascript the temptation is almost unbearable. But you are correct.
jeffdn 10y ago
One place we use them effectively at the healthcare company where I work is as an abstraction over the vastly complex and varied data we have on file. Put a little more simply than the reality, we "tag" patients with various statuses, conditions, and other things. All the tags are stored in a single table. The metadata for those tags, which can be, for instance, an array of hospitalization dates, a dictionary/map of various lab values, etc., is stored in a JSONB column. There is a "population explorer" in our web application that allows users to search for patients based on the existence (or lack) of any combination of tags, and additionally to filter each tag by the values contained. The JSONB metadata is generated in SQL, the filtering is done on the frontend, and the searching is done by a query generated in the Python backend. It is surprisingly easy to maintain and extend, and even after a year and a half, we haven't run into any insurmountable issues, or even any difficulties of note. For instance, if a user searched for all patients tagged with a hospitalization since November 1, the following steps would happen (this is vastly different from the actual code, just trying to give a sense of how it functions): 1. the backend generates an SQL query: select ... from patient p where exists ( select 1 from tag t where t.tag_type_id = :type_id and t.deleted is false and t.patient_id = p.id ) 2. the backend iterates through the results, filtering as necessary exclude_list = [] if filters: for key, patient in results: for filter in filters: # each filter is a lambda generated by the # user-defined parameters (in this case, # since November 1) if not filter(patient): exclude_list.append(key) for key in set(exclude_list): del results[key] 3. on the frontend, if the filters for any of the desired tags are changed, a process very similar to the above is run to recompute the display set However, on the whole, I tend to agree with you. Really the only other places we use JSON at present are in storing API requests, where the data sent with each HTTP POST are in widely differing formats, and in various intermediate abstraction layers, where data from different sources contains different columns, and we can carry forward the "outlier" columns as a JSON object for later reference.
- kornish 10y ago
  Out of interest, why did you guys design the system to perform filtration at the application layer instead of generating SQL for filtering and pushing that into the DB?
- ubernostrum 10y ago
  I also work in health care, and also am using JSON columns in Postgres to store some "schemaless" (in the sense that the schema is not fixed in advance and subject to change over time) data in a convenient way. There's a serialization mapper that turns it into a normalized structure for some types of consumption, but the source of truth is the JSON column and the normalized structure is generated from it.
mioelnir 10y ago
I use the json field to store precomputed/-assembled write-once-read-many json documents. For that I find it incredibly useful, as the JSON functions allow me to drill into the generated JSON for analysis and diagnostics.
cookiecaper 10y ago
This describes not only JSON fields/blobs in a traditional RDBMS, but also the impulse that drives much NoSQL adoption. It's just easier to be able to dump whatever you want in there, developers love it, no chatter about normalization and all of that silly DB stuff. This almost invariably comes back to haunt them, often in astonishingly damaging ways. Use schemaless options, including JSONB in PgSQL or doc DBs like Mongo, for data that really needs it due its unpredictable nature, NOT data that you're too lazy to draw out a schema for.

by dexwiz 10y ago

scandox 10y ago

When you're using Javascript the temptation is almost unbearable. But you are correct.

jeffdn 10y ago

One place we use them effectively at the healthcare company where I work is as an abstraction over the vastly complex and varied data we have on file. Put a little more simply than the reality, we "tag" patients with various statuses, conditions, and other things. All the tags are stored in a single table. The metadata for those tags, which can be, for instance, an array of hospitalization dates, a dictionary/map of various lab values, etc., is stored in a JSONB column. There is a "population explorer" in our web application that allows users to search for patients based on the existence (or lack) of any combination of tags, and additionally to filter each tag by the values contained. The JSONB metadata is generated in SQL, the filtering is done on the frontend, and the searching is done by a query generated in the Python backend. It is surprisingly easy to maintain and extend, and even after a year and a half, we haven't run into any insurmountable issues, or even any difficulties of note. For instance, if a user searched for all patients tagged with a hospitalization since November 1, the following steps would happen (this is vastly different from the actual code, just trying to give a sense of how it functions): 1. the backend generates an SQL query: select ... from patient p where exists ( select 1 from tag t where t.tag_type_id = :type_id and t.deleted is false and t.patient_id = p.id ) 2. the backend iterates through the results, filtering as necessary exclude_list = [] if filters: for key, patient in results: for filter in filters: # each filter is a lambda generated by the # user-defined parameters (in this case, # since November 1) if not filter(patient): exclude_list.append(key) for key in set(exclude_list): del results[key] 3. on the frontend, if the filters for any of the desired tags are changed, a process very similar to the above is run to recompute the display set However, on the whole, I tend to agree with you. Really the only other places we use JSON at present are in storing API requests, where the data sent with each HTTP POST are in widely differing formats, and in various intermediate abstraction layers, where data from different sources contains different columns, and we can carry forward the "outlier" columns as a JSON object for later reference.

kornish 10y ago
Out of interest, why did you guys design the system to perform filtration at the application layer instead of generating SQL for filtering and pushing that into the DB?
ubernostrum 10y ago
I also work in health care, and also am using JSON columns in Postgres to store some "schemaless" (in the sense that the schema is not fixed in advance and subject to change over time) data in a convenient way. There's a serialization mapper that turns it into a normalized structure for some types of consumption, but the source of truth is the JSON column and the normalized structure is generated from it.

mioelnir 10y ago

I use the json field to store precomputed/-assembled write-once-read-many json documents. For that I find it incredibly useful, as the JSON functions allow me to drill into the generated JSON for analysis and diagnostics.

cookiecaper 10y ago

This describes not only JSON fields/blobs in a traditional RDBMS, but also the impulse that drives much NoSQL adoption. It's just easier to be able to dump whatever you want in there, developers love it, no chatter about normalization and all of that silly DB stuff. This almost invariably comes back to haunt them, often in astonishingly damaging ways. Use schemaless options, including JSONB in PgSQL or doc DBs like Mongo, for data that really needs it due its unpredictable nature, NOT data that you're too lazy to draw out a schema for.