Re:JSON and Virtual Columns in SQLite
by linsomniac on Monday May 16, 2022 @11:10AM
(#31398593)
In my case, I have a bunch of data in the JSON, but found users need to filter requests based on just one field of the data.
In specific, I'm loading Postfix logs into the database, I parse the logs into JSON and then store that JSON in a column. The format of that JSON comes in ~4-5 flavors, depending on the exact log event, but most of them include a "to" field (the envelope recipient), that the API users need to filter on. I ended up adding a "to" column and mirroring the JSON "to" field into that, but this would have worked as well.
The API users still have access to that additional rich data, and the whole thing overall is simpler because I don't have it exploded out into a bunch of fields that have many of them NULLs for various log types.
In specific, I'm loading Postfix logs into the database, I parse the logs into JSON and then store that JSON in a column. The format of that JSON comes in ~4-5 flavors, depending on the exact log event, but most of them include a "to" field (the envelope recipient), that the API users need to filter on. I ended up adding a "to" column and mirroring the JSON "to" field into that, but this would have worked as well.
The API users still have access to that additional rich data, and the whole thing overall is simpler because I don't have it exploded out into a bunch of fields that have many of them NULLs for various log types.