A friend has a use case involving searching structured data (like everyone). I think it's basically reports on different companies, so have fields like name, location, market cap, etc. They come as JSON documents, with very similar structures, which change slowly over time as fields are added and removed. He needs to give users an interface to these, where they can filter on various fields ("show me all small hairdressers in Minnesota" etc).
He currently dumps the JSON documents into ElasticSearch, and queries that. It works, but my gut feeling is that ElasticSearch is wildly less efficient than the theoretical optimum for this. Could DuckDB be an alternative?
The easiest thing would be if he could put whole JSON documents into a JSON column. Less easy would be breaking them up into real columns; i know that DuckDB is able to do this automatically, but it feels like a brittle thing to depend on.
I did some playing around with searching data from JSON, comparing a single JSON column to automatic 'proper' columns, and the proper columns were massively faster. Is there scope for making queries on JSON much faster, using indexes, or some setting i missed, or something?
Is DuckDB even likely to be a good solution for this? It's intended for analytics, whereas this is more of a retrieval / search / filtering use.