Sample Header Ad - 728x90

Full text search with json field in SQLite

1 vote
1 answer
1868 views
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