PostgreSQL: using indices on json fields

• 01, 29, 2019
•  bullgare
•  Databases
• No Comments.

It’s obviously a really bad idea.
But if you really need it, that’s what you can do.

Simple index on a field

CREATE INDEX ON public.chats USING GIN (meta jsonb_path_ops);

Then you can get data like this:

EXPLAIN ANALYZE SELECT
	COUNT(*)
FROM chats
INNER JOIN chat_users u
	ON chats.id = u.chat_id AND u.user_id = 9813283
WHERE meta @> '{"current_page":"qs113dxcv1f2"}'
	AND meta @> '{"order_id":555}'
LIMIT 1;

Indexing exact fields from json

CREATE INDEX chats_id_meta_idx ON chats (id, ((meta ->> 'order_id')::BIGINT), ((meta ->> 'current_page')::CHARACTER));
DROP INDEX chats_id_meta_idx;

Then you should retrieve data like that:

EXPLAIN ANALYZE SELECT
	chats.id
FROM chats
INNER JOIN chat_users u
	ON chats.id = u.chat_id AND u.user_id = 9813283
WHERE (meta ->> 'current_page') = 'qs113dxcv1f2' // OR (meta ->> 'current_page')::CHARACTER = 'qs113dxcv1f2'
    AND (meta ->> 'order_id') = 555 // OR (meta ->> 'order_id')::BIGINT = 555
LIMIT 1;

Interesting links on topic

https://www.compose.com/articles/faster-operations-with-the-jsonb-data-type-in-postgresql/
https://stackoverflow.com/questions/36075918/postgresql-index-on-json
https://stackoverflow.com/questions/26499266/whats-the-proper-index-for-querying-structures-in-arrays-in-postgres-jsonb/