Computed index on transformation of jsonb key set

[Steven Schlansker]

Hi Postgres fans,

We store a Java Map in Postgres as a jsonb column.
As json does not have a UUID type, it is of course stored as text.

A simple value might be:
{"04e623c0-6940-542f-a0de-4c999c626dfe": 5000, 
"6d3e24b6-9e8c-5eb1-9e4c-f32cc40864c9": 20825}

I am trying to implement an efficient set-intersection test.
"Find all rows where the map has a key in (uuid1, uuid2, uuid3)"

The most straightforward approach is to create a GIN over the whole column.
This has the documented disadvantage that all keys and values are stored in the 
GIN repeatedly, with the kicker that the UUID will be stored as text.

I figured I'd end up with significantly better storage and performance 
characteristics if I first compute a uuid[] value and build the GIN over that, 
and use the array operator class instead.  Additionally, this eliminates 
possible confusion about uuid casing (text is case sensitive, uuid is not) and 
this has already caused at least one bug in our application.

I attempted to optimize a query like:
select * from tbl where array(select jsonb_object_keys(mapData)::uuid) && 
array['320982a7-cfaa-572a-b5ea-2074d7f3b014'::uuid];

with:
create index my_idx on tbl using gin((array(select 
jsonb_object_keys(mapData)::uuid)));

Of course, this fails, ERROR: cannot use subquery in index expression
Clever me says, "well, I'll just use array_agg instead!"

create index my_idx on tbl using 
gin((array_agg(jsonb_object_keys(mapData)::uuid)));

Postgres 9.6 says:
ERROR:  aggregate functions are not allowed in index expressions

Just to see what happened, knowing that GIN supports multi-valued data, I tried:

create index my_idx on tbl using gin((jsonb_object_keys(mapData)::uuid));
ERROR:  index expression cannot return a set

How can I efficiently implement the feature I've described?  It seems difficult 
to use computed indexing with GIN.

Thank you for any advice and have a great weekend,
Steven

Re: Computed index on transformation of jsonb key set

[Steven Schlansker]

> On Apr 26, 2019, at 2:33 PM, Rob Sargent  wrote:
> 
> 
> On 4/26/19 3:25 PM, Steven Schlansker wrote:
>> 
>> 
>> How can I efficiently implement the feature I've described?  It seems 
>> difficult to use computed indexing with GIN.
>> 
> Storing the map in a child table (parentId, UUID, int) is out of the question?

Yes, thanks Rob and David, that will be my approach if I can't figure out 
anything better.

I was hoping to not have to denormalize the data given that Postgres generally 
has great support for computing indexes,
both to avoid storage costs and eliminate the need to maintain triggers or 
application code to handle it.

Re: Computed index on transformation of jsonb key set

[Steven Schlansker]

> On Apr 27, 2019, at 12:55 AM, Andrew Gierth  
> wrote:
> 
> Obvious solution:
> 
> create function uuid_keys(mapData jsonb) returns uuid[]
>  language plpgsql immutable strict
>  as $$
>begin
>  return array(select jsonb_object_keys(mapData)::uuid);
>end;
>  $$;
> 
> create index on tbl using gin (uuid_keys(mapData));
> 
> select * from tbl where uuid_keys(mapData) && array[...];

Thank you Andrew, I modified it slightly to use `language sql` but otherwise
it gets me going forward.