On Wed, Dec 2, 2015 at 11:48 AM, Kaare Rasmussen <kaare@jasonic.dk> wrote:
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.
This is known problem, that's why we stop developing jsquery and are working on sql-level query language for jsonb, then you'll use all power and extendability of SQL. The idea is to use power of subselects and unnest to unroll jsonb to sql level. There is presentation at pgconf.eu on this https://wiki.postgresql.org/images/4/4e/Createam.pdf, see slide #27
