3 ms·
Sqlite is great as data store for storing JSON. We receive realtime data from different sources in JSON format. I dump the raw data into MongoDB, but it's sti
by ilitirit 9y ago
Sqlite is great as data store for storing JSON. We receive realtime data from different sources in JSON format. I dump the raw data into MongoDB, but it's still not very easy to query. This is where Sqlite comes in handy. I import the data straight into Sqlite from MongoDB which then enables me to run SQL queries using the JSON1 extension. Currently I'm using it to store data that drive feed simulators. We can extract the data using queries like:
SELECT json_extract(RawData, '$.sourceId') SourceId
, json_extract(RawData, '$.messageId') MessageId
, json_extract(RawData, '$.message') Message
, json_extract(RawData, '$.message.parties[0].name') Party
, json_extract(RawData, '$.timestamp') Timestamp
FROM RawFeed
JOIN Source
ON SourceId = Source.Id
WHERE Source.Name = 'Foo Company'
ORDER BY Timestamp
- zepolen 9y agoDon't get me wrong, I was hating mongo before it was cool to do so, but your example is one place which makes zero sense, just learn to write a mongo query already or use SQL straight up.
- rockostrich 9y agoYou could also just structure your data in MongoDB in a way that creates an index on source id and name? This just makes it seem like you're trying to fit a square peg in a round hole.