Full text search with json field in SQLite

Question

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.

Answer 1

A full-text search table cannot be used for most 'normal' operation; you should think of it as something like an index.

The FTS documentation shows how you can use triggers to keep the actual table and the FTS table in sync.