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.
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.
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
)
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]
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.