Indexing on jsonb keys in postgresql

Question

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?

Indexes are created to support queries. You have to show us what kind of query (where condition) you want to support with that index. e.g. the index you have would support where data ? '1' — user330315
– user330315, Commented Jun 6, 2020 at 18:21
Again: that condition can make use of a gin index on the column. — user330315
– user330315, Commented Jun 7, 2020 at 7:08
Aha. May I ask you to show me what to write in command? CREATE INDEX test ON tablename (data ?); like that? — Amir Mansoubi
– Amir Mansoubi, Commented Jun 7, 2020 at 7:11
ِYes. But this indexes all the data (keys + values) which is not pleasant for me. I want to index just keys. — Amir Mansoubi
– Amir Mansoubi, Commented Jun 7, 2020 at 7:17

jjanes · Accepted Answer · 2020-06-07 13:35:43Z

2

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.

edited Jun 7, 2020 at 13:35

answered Jun 6, 2020 at 23:45

jjanes

45k5 gold badges39 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Amir Mansoubi Over a year ago

Thanks for answering. I added another query in question. Does this index support queries like: 'select count(*) from tablename where data ? '2';'

Collectives™ on Stack Overflow

Indexing on jsonb keys in postgresql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related