Re: json indexing and data types

On Wed, Dec 2, 2015 at 8:04 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Merlin Moncure <mmoncure(at)gmail(dot)com> writes:
>> On Wed, Dec 2, 2015 at 12:03 AM, Kaare Rasmussen <kaare(at)jasonic(dot)dk> wrote:
>>> 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 feel your pain. jsquery is superb for subdocument searching on
>> *specific* subdocuments but range searching is really limited.
>
> Yeah. The problem here is that a significant part of the argument for
> the JSON/JSONB datatypes was that they adhere to standards (RFC 7159 in
> particular). I can't see us accepting a patch that changes them into
> JSON-plus-some-PG-enhancements.
>
> For cases where you know that specific sub-fields can be expected to be
> of particular datatypes, I think you could get a lot of mileage out of
> functional indexes ... but you'd have to write your queries to match the
> indexes, which could be painful.

If you create a view which has columns defined according to the index
expression, it does remove a lot of the pain of making queries that
use those expressions. It looks just like using a real column, as
long as you don't update it.

Cheers,

Jeff