News for hackers. Stuff that matters.
Re:JSON and Virtual Columns in SQLite by discodave on Monday May 16, 2022 @10:17AM (#31397576)
For a start, the top level of the JSON document could be an array.
Re:JSON and Virtual Columns in SQLite by CGamesPlay on Monday May 16, 2022 @11:44AM (#31399131)
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.

Re:JSON and Virtual Columns in SQLite by jpcapdevila on Monday May 16, 2022 @10:21AM (#31397652)
Fair, but for objects like in the OP sample? Is there any advantage that justifies the extra steps?
Re:JSON and Virtual Columns in SQLite by zrail on Monday May 16, 2022 @10:43AM (#31398130)
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.