Hacker News Reader

Hacker News

Comment by claytongulick | Hacker News Reader

Full thread

claytongulick

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.

View on HN

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.

[1] https://www.sqlite.org/gencol.html

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.

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.

Could you expand on what Postgres offers that SQLite doesn't in this regard? I'm curious to know what I'm missing out on.

Not 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

Unless 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

That 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

[2] https://www.postgresql.org/docs/9.5/functions-json.html

> SQLite doesn't support functional indexing

SQLite has "Indexes On Expressions"[1] since 2015. I'm assuming functional indexing in Postgres is the same. So I think the syntax would be:

    CREATE INDEX idx_t_bar ON json_extract(data, '$.bar');

[1]: https://www.sqlite.org/expridx.html

> Also PostgreSQL has many operators[2] which makes using JSON fields easier.

This does look a little more convenient to write than SQLite's JSON syntax, but I don't see any fundamental difference here.

It seems like one thing is that Postgres has two types for JSON: JSON and JSONB.

Both validate syntax on insert. The JSONB datatype (not JSON; note the B on the end) further stores the content in a parsed, binary form so future accesses are faster. The schema author does not have to indicate which columns they would like "fast" access to - queries are free to access any arbitrary path in the JSON, and will get reads that don't require JSON parsing.

It seems like SQLite can achieve something similar but not quite the same. It looks like the user has to specifically enumerate and materialize the columns to which they want fast access.

So it seems like json_extract(...) is comparable to Postgres's JSON type, but there's not quite an analagous thing for Postgres's JSONB type.

Postgres's JSONB type can essentially turn Postgres into a full NoSQL database. Infact, its so good that it can beat MongoDB at its own game in a lot of benchmarks.

https://www.theguardian.com/info/2018/nov/30/bye-bye-mongo-h...

Example: The Guardian switched from Mongo to Postgres jsonb as a nosql db.

Sqlite is just that, lightweight. If you want better JSON support specialized for what you want, extend it. The product allows extensions easily.

Reply on news.ycombinator.com