Hi
As json essentially only has three basic data types, string, int, and
boolean, I wonder how much of this - to index, search, and sort on
unstructured data - is possible. I guess part of the answer would be
'jsquery and vodka', but let me describe the problem first.
The basics is, that I have a column with what is essentially json data;
a number of data structures of different depths. Perhaps 10 - 30 top
levels, and probably no more than 3, max 4 levels deep. In total there
are some hundred thousands of rows in each table. It would probably be
best stored as jsonb. Right now it's text, because it's only used by the
application itself.
It would be incredibly useful to add an index to this column, and to be
able to search, using the index, on arbitrary elements. This part seems
already there, with jsquery.
The hard part is that some of the data items really have another type.
There are dates and floating points, as the most important ones. And the
really hard part is that sorting and range searches are important,
especially for these two types. Having dates is iso-format, and
left-padding floats with zeros is a low tech solution, and especially
the latter is not very efficient.
The solution might be to add functional indexes for these data items,
but it's cumbersome and not easily maintainable. If a one-stop solution
is in the works, or already there, it could save a lot of time.
/kaare