I have structure like this: user_id, a.
a is of type jsonb and has the following structure:
{ b:
[
{ids: [1,2,3,4]},
{ids: [2,3,4]},
{ids: [1,2,4]},
...
]
}
How would I make an index that enabled me to find all users (user_id) that has a certain id in the ids list?
Is a GIN index what you want?
It seems that you first need to organized the IDs into a form that is more tractable. I'm more familiar with Python than I am with the PostgreSQL ways of manipulating JSON, so I used PL/Python for this purpose.
DROP TABLE IF EXISTS ids;
CREATE TABLE ids (user_id integer, a jsonb);
INSERT INTO ids VALUES
(1, '{"b": [{"ids": [1, 2, 3, 4]}, {"ids": [2, 3, 4]}, {"ids": [1, 2, 4]}]}'),
(2, '{"b": [{"ids": [2, 3, 4]}]}'),
(3, '{"b": [{"ids": [4, 5, 6]}, {"ids": [6, 7, 8]}]}');
CREATE OR REPLACE FUNCTION extract_ids(a_json jsonb)
RETURNS int[] AS
$BODY$
import json
s = set()
a = json.loads(a_json)
for key in a.keys():
for id_set in a[key]:
s.update(id_set['ids'])
return(list(s))
$BODY$ LANGUAGE plpythonu IMMUTABLE;
SELECT user_id, extract_ids(a)
FROM ids;
CREATE INDEX ON ids USING gin (extract_ids(a));
SELECT user_id
FROM ids
WHERE ARRAY[3] <@ extract_ids(a);
