Hacker News Reader

Comment by jpcapdevila | Hacker News Reader

What's the difference/advantage between defining the virtual column or creating the index directly like this:

CREATE INDEX test_idx ON events(JSON_EXTRACT(data, '$.someKey'));

The two things are actually independent of each other: you can create a virtual column and an index, or do only one. The generated column can be used as an alias of an expression in queries (e.g. ORDER BY clauses), while just creating an index cannot. The generated column can be optionally virtual (although that defeats the purpose listed in this article), while the index is obviously always stored. The generated column reduces the likelihood that you accidentally don't use the index, because "the SQLite query planner will consider using an index on an expression when the expression that is indexed appears... exactly as it is written in the CREATE INDEX statement." [0]

[0] https://sqlite.org/expridx.html

I think the way the article does it just provides an easy, symbolic way to reference the computed value... so it gives a little readability, it's maybe easier to ensure your where clause and index match up.

Also, depending on your access patterns, you might want the generated column to be stored (evaluated on write) rather than virtual (evaluated on read).

SQLite doesn't support adding generated stored columns to a table once the table has been created, so you basically have to decide upfront which generated columns must be stored, and then you can't touch it anymore (short of dropping it).

That’s normal DBA for SQLite - you can’t even rename columns without dropping the table. So not such an obstacle.

You can rename it now, unless I'm missing something

https://www.sqlite.org/lang_altertable.html

For a start, the top level of the JSON document could be an array.

Fair, but for objects like in the OP sample? Is there any advantage that justifies the extra steps?

I think the advantage for simple objects is just that you can refer to the columns directly vs having to go through a function. It's only really a big win if you're manually writing a lot of sql or if you're using something like Rails and you want to write queries against values in the JSON blob without going through a bunch of hoops.

That doesn't matter. The json_extract function returns a string, so json_extract('[{"foo": "x"}]', '$.foo') is NULL and json_extract('[{"foo": "x"}]', '$[0].foo') is 'x' and it doesn't matter if this is destined to go into a generated column or an index.

[append] And SQLite's json_extract doesn't have anything like '$[*].foo' or any mapping functions. You could use json_each to do that, but the process is very different from creating an index.

Reply on news.ycombinator.com