I love PostgreSQL, and would recommend it anytime. Even though jsonb is very powerful, in general if you store JSON in a SQL database you're very likely doing something wrong. (You're breaking first normal form)
But if you really wanted to use SQLite to store JSON à la jsonb in PostgreSQL you can use generated fields[1]
sqlite> create table t(id integer primary key autoincrement, data text);
sqlite> insert into t(data) values ('{"foo": "value", "bar": "other value"}'), ('{"foo": "baz", "bar": "qux"}');'
[…]
sqlite> alter table t add column foo text generated always as (json_extract(data, '$.foo')) virtual;
sqlite> select * from t;
id data foo
-- -------------------------------------- -----
1 {"foo": "value", "bar": "other value"} value
2 {"foo": "baz", "bar": "qux"} baz
You can even use a "stored" (instead of 'virtual') generated field, and create an index on it for fast lookups.
It's not as powerful as Postgres, but it does a pretty good job.
Database optimization and the level of normalization is a complex topic that varies widely based on application and workload.
JSON is absolutely a valid approach to data storage when dealing with certain data structures. As is using a relational database and jsonb data types for it. Cherry picking columns gives you the advantages for both a NoSQL document storage database as well as a relational analytics database.
This is a pretty common modern technique that Postgres, for example, has made easy.
Go try modeling a FHIR database structure using standard normalization rules.
You'll quickly discover why no one does it. Even HAPI FHIR server (the most full featured and popular FHIR server) written in Java doesn't attempt it.
Disagree storing json in databases is often the best thing to do. Clickhouse for example allows you to quickly pull out relevant data and feed materialized views.
I never said storing json in databases was wrong. I said storing json in *SQL* databases was an anti-pattern.
https://en.wikipedia.org/wiki/First_normal_form
It's perfectly fine
one column for a timestamp and one column to store the json
You then build materialized views pulling out whatever json fields you need.
Just because its easier doesn't mean its better - json is just one possible materialization for your set.