Postgres index creation for jsonb column having JSON Array value

0
votes

I have an employee table in postgres having a JSON column "mobile" in it. It stores JSON Array value ,

e_id(integer)  name(char)   mobile(jsonb)
1              John         [{\"mobile\": \"1234567891\", \"status\": \"verified\"},{\"mobile\": \"1265439872\",\"status\": \"verified\"}]
2              Ben          [{\"mobile\": \"6453637238\", \"status\": \"verified\"},{\"mobile\": \"4437494900\",\"status\": \"verified\"}]

I have a search api which queries this table to search for employee using mobile number.

How can I query mobile numbers directly ? How should I create index on the jsonb column to make query work faster ?

*updated question

1
arraysjsonpostgresqlindexing
Index is useful only when the jsonb column value is going to be repeated. Question is, does the jsonb column will have many duplicate values ? – rootkonda
yes it will be having many duplicates in my case. This is an example scenario. – j '

1 Answers

1
votes

You can query like this:

SELECT e_id, name
FROM employees
WHERE mobile @> '[{"mobile": "1234"}]';

The following index would help:

CREATE INDEX ON employees USING gin (mobile);