json indexing and data types

On Wed, Dec 2, 2015 at 12:03 AM, Kaare Rasmussen <kaare(at)jasonic(dot)dk
<javascript:;>> 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.

I feel your pain. jsquery is superb for subdocument searching on
*specific* subdocuments but range searching is really limited. Value
searching is there for numerics but dates and text range searching are not
present. We also have to understand that you are asking the index to make
assumptions about the json that are not clear from the structure itself
(such as subfield 'x' is a date).

The only workaround I've been able to come up with is to migrate the json
to a specially encoded text field, stored side by side with the source
json, that is more amenable to pg_trgm based searching (to give you a taste
of that complexity, keys are stored upper case and values are stored lower
case).

Some might say that you're better off using a dedicated json searching
server like solr but these systems aren't magic; they will quickly boil
down to a brute force search in the face of complex queries, and they have
lots of other problems in my experience (starting with, lack of proper
transactions and painfully slow insertion of large documents). Other
people recommend them; I don't.

One way of looking at this problem is that the "schemaless" check is
getting cashed. If you need detailed data driven queries (as opposed to
more 'test searchy' type searches) perhaps it's time to start running your
data through a normalized structure.

merlin