CODEWITHSUNDEEP
postgresqlindexingjsonb
Amir Mansoubi
Amir Mansoubi

Reputation: 63

Indexing on jsonb keys in postgresql

I'm using PostgreSQL. Is there any way to create index just on dictionary keys, not values.

For example imagine a jsonb column like:

select data from tablename where id = 0;

answer: {1:'v1', 2:'v2'}

I want to index on the key set (or key list) which is [1, 2]. To speed up queries like:

select count(*) from tablename where data ? '2';

As you can see in docs, there is a way for indexing the column entirely (keys + values):

CREATE INDEX idxgin ON api USING GIN (jdoc);

This is not good for me, considering that I store a large amount of data in values.

I tried this before:

CREATE INDEX test ON tablename (jsonb_object_keys(data));

The error was:

ERROR:  set-returning functions are not allowed in index expressions

Also, I don't want to store keys in the dictionary as a value.

Can you help me?

Upvotes: 5

Views: 4186

Answers (1)

jjanes
jjanes

Reputation: 44227

Your example doesn't make much sense, as your WHERE clause isn't specifying a JSON operation, and your example output is not valid JSON syntax.

You can hide the set-returning function (and the aggregate) into an IMMUTABLE function:

create function object_keys(jsonb) returns text[] language SQL immutable as $$ 
    select array_agg(jsonb_object_keys) from jsonb_object_keys($1)
$$;

create index on tablename using gin ( object_keys(data));

If you did it this way, you could then query it formulated like this:

select * from tablename where object_keys(data) @> ARRAY['2'];

You could instead make the function return a JSONB containing an array rather than returning a PostgreSQL text array, if you would rather query it that way:

select * from tablename where object_keys_jsonb(data) @> '"2"';

You can't use a ? formulation, because in JSONB that is specifically for objects not arrays. If you really wanted to use ?, you could instead write a function which keeps the object as an object, but converts all the values to JSON null or to empty string, so they take up less space.

Upvotes: 2

Related Questions