3 ms·
For a start, the top level of the JSON document could be an array.
by discodave 4y ago
For a start, the top level of the JSON document could be an array.
- jpcapdevila 4y agoFair, but for objects like in the OP sample? Is there any advantage that justifies the extra steps?
- zrail 4y agoI 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 agoThat 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.