Is it possible to have full text search in SQLite along using json?
In SQLite json is stored as TEXT
(JSON1).
Full text search requires creation of virtual table (full text) but I don't know how to connect those two extensions so that full text search wouldn't search in json field names.
WRITING (ID, FK_PERSON_ID, BOOK_NAME, TEXT_JSON)
PEOPLE (PERSON_ID, NAME)
additionally TEXT_JSON
for certain person contains following json objects:
-- WRITING.ID = 1, WRITING.FK_PERSON_ID = 1, BOOK_NAME = "NAME_1"
{
"chapter1": "Title1",
"text1": "This is sample sentence with word text",
"text2": "This is sample sentence with word text"
}
-- WRITING.ID = 2, WRITING.FK_PERSON_ID = 1, BOOK_NAME = "NAME_101"
{
"chapter1": "Title2",
"text1": "This is sample sentence without"
}
(the structure of json object can differ)
How should I setup virtual table for full text search to search single person writings BOOK_NAME
and values of all TEXT_JSON
attributes? Searching word text
in writings of PERSON_ID = 1
would return only WRITING.ID = 1
.