5 ms·

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

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'));

discodave 4y ago
For a start, the top level of the JSON document could be an array.
- jpcapdevila 4y ago
  Fair, but for objects like in the OP sample? Is there any advantage that justifies the extra steps?
  - zrail 4y ago
    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.
- CGamesPlay 4y ago
  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.
jmull 4y ago
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).
- SPBS 4y ago
  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).
  - ComputerGuru 4y ago
    That’s normal DBA for SQLite - you can’t even rename columns without dropping the table. So not such an obstacle.
    - jpcapdevila 4y ago
      You can rename it now, unless I'm missing something https://www.sqlite.org/lang_altertable.html https://www.sqlite.org/lang_altertable.html
CGamesPlay 4y ago
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 https://sqlite.org/expridx.html

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'));

discodave 4y ago

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

jpcapdevila 4y ago
Fair, but for objects like in the OP sample? Is there any advantage that justifies the extra steps?
- zrail 4y ago
  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.
CGamesPlay 4y ago
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.

jmull 4y ago

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

SPBS 4y ago
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).
- ComputerGuru 4y ago
  That’s normal DBA for SQLite - you can’t even rename columns without dropping the table. So not such an obstacle.
  - jpcapdevila 4y ago
    You can rename it now, unless I'm missing something https://www.sqlite.org/lang_altertable.html https://www.sqlite.org/lang_altertable.html

CGamesPlay 4y ago

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 https://sqlite.org/expridx.html