Is it possible to have full text search in SQLite along using json?
In SQLite json is stored as
TEXT
([JSON1](https://www.sqlite.org/json1.html)) .
Full text search requires creation of virtual table ([full text](https://www.sqlitetutorial.net/sqlite-full-text-search/)) 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
.
Asked by SundayProgrammer
(11 rep)
May 3, 2020, 12:27 PM
Last activity: May 10, 2025, 09:03 PM
Last activity: May 10, 2025, 09:03 PM