5 ms·
They'd have one trillion + 1 if they had better JSON support. I'd be using it in a large healthcare project I'm working on now. Unfortunately, the JSON suppor
by claytongulick 5y ago
They'd have one trillion + 1 if they had better JSON support.
I'd be using it in a large healthcare project I'm working on now.
Unfortunately, the JSON support is basically "serialize to a string". Postgres is miles ahead with jsonb.
Shame, I <3 sqlite.
- acatton 5y agoI 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. [1] https://www.sqlite.org/gencol.html https://www.sqlite.org/gencol.html
- nojito 5y agoDisagree 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.
- acatton 5y agoI 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 https://en.wikipedia.org/wiki/First_normal_form
- nojito 5y agoIt'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.
- hobs 5y agoJust because its easier doesn't mean its better - json is just one possible materialization for your set.
- claytongulick 5y agoDatabase 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.
- wmanley 5y agoCould you expand on what Postgres offers that SQLite doesn't in this regard? I'm curious to know what I'm missing out on.
- acatton 5y agoNot GP, but PostgreSQL offers jsonb fields[1] Since PostgreSQL allows for functional indexes, you can query and index the data in a structured way. For example you have a table t with "id" and "data" where data is a jsonb field like {foo: ..., bar: ...}. You can do SELECT id, data->'foo' FROM t WHERE data->'bar' > 5 Which will yield the value of "foo" (inside the json field) for rows where "bar" (inside the json field) is greater than 5. [1] https://www.postgresql.org/docs/9.5/functions-json.html https://www.postgresql.org/docs/9.5/functions-json.html
- wmanley 5y agoUnless I'm misunderstanding SQLite can do this as well, but with less nice syntax. The above could be written something like: SELECT id, json_extract(data, '$.foo') FROM t WHERE json_extract(data, '$.bar') > 5 in SQLite. You can also add an index on one of these expressions: https://dgl.cx/2020/06/sqlite-json-support https://dgl.cx/2020/06/sqlite-json-support
- acatton 5y agoThat is true, but SQLite doesn't support functional indexing. This means that you cannot do CREATE INDEX idx_t_bar ON t(json_extract(data, '$.bar')); for fast lookups. But you can do CREATE INDEX idx_t_bar ON t(data->'bar'); in PostgreSQL. You have some workarounds like I explained.[1] Also PostgreSQL has many operators[2] which makes using JSON fields easier. [1] https://news.ycombinator.com/item?id=29461926 https://news.ycombinator.com/item?id=29461926 [2] https://www.postgresql.org/docs/9.5/functions-json.html https://www.postgresql.org/docs/9.5/functions-json.html
- wmanley 5y ago
- literallyaduck 5y agoSqlite is just that, lightweight. If you want better JSON support specialized for what you want, extend it. The product allows extensions easily.