masukomi

#SQLite question
Am i right in thinking that you can combine use #FTS5 to have full text search on data from within #JSON columns?

I am _assuming_ it's just a matter of writing a valid trigger to extract said data into a virtual table but I'm having trouble finding an example.

I've never fully groked the syntax & limitations thereof for building those virtual tables. Utterly failed to do one with a simple join. 🤦‍♀️

Jan 31, 2023 at 2:07pmWeb
Show threadMarkus 🤓✨Jan 31, 2023
@masukomi I wonder if you could just do FTS on the JSON column directly? Depending on what else is in the column.
Show threadmasukomiJan 31, 2023
@markus ... what do you mean by that? my impression is that you can't do FTS directly on ANY normal column. I thought it only works via the virtual table stuff.
Show threadMarkus 🤓✨Jan 31, 2023
@masukomi you can use the virtual table just for the search index, and index whatever you want. SQLite doesn’t have a JSON data type, it’s just text. So you could try that and see what happens. 😊 I’ve written a bit about FTS here (scroll down a bit and ignore the Go stuff): https://www.golang.dk/articles/go-and-sqlite-in-the-cloud
Go and SQLite in the Cloud

Why SQLite and Go in the cloud are a great match, getting started, best practices, and more.

Show threadmasukomiJan 31, 2023
@markus I think we're saying the same thing. I'm just ALSO saying "the syntax for futzing with virtual tables is limited and frustrating and i haven't figured out how to successfully do even simple things like joins in it. I'd really like someone to point me to an example of extracting JSON fields into it because SQLite's virtual table syntax has destroyed my confidence that i can do anything beyond newb level crap in it. "
Show threadmasukomiJan 31, 2023
@markus see also: "why does integrating FTS5 have to be such a pain in the ass after all these years?!"
Show threadMarkus 🤓✨Jan 31, 2023
@masukomi yeah, agreed. It’s not very intuitive.