I've recently started using SQLite with the JSON1 extension which allows me to store and query dynamic/json data.
Lets take for example the following table and data structure:
# documents table:
--------------------------------------------
id | json
---- ------------------------------------
1 | [{"id": 1}, {"id": 2}, {"id": 3}]
2 | [{"id": 11}, {"id": 12}, {"id": 13}]
The problem I stumbled on is that there doesn't seems to be an easy way to query objects within an array without specifying an index. Or in other words, consider the following pseudo query:
SELECT *
FROM documents
WHERE json_extract(json, "$[*].id") > 1
# expect to return all rows that have json.*.id greater than 1
The above doesn't work because instead of [*] you have to specify a concrete array index.
One workaround of this could be to use json_each or json_tree but that can get pretty quickly out of hand if you have to handle nested array objects, eg. sub1.*.sub2.*.sub3.id
I found that the MySQL json data type supports [*] but I wasn't able to find anything similar for SQLite.
Is there some "hidden" syntax to specify [*] in json path queries for SQLite that I'm missing or this is a limitation of the JSON1 extension?