Postgres index creation for jsonb column having JSON Array value

Question

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

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
– rootkonda, Commented Feb 26, 2020 at 10:51
yes it will be having many duplicates in my case. This is an example scenario. — j '
– j ', Commented Feb 26, 2020 at 10:53

Laurenz Albe · Accepted Answer · 2020-02-26 11:03:28Z

1

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);

answered Feb 26, 2020 at 11:03

Laurenz Albe

255k21 gold badges307 silver badges381 bronze badges

7 Comments

j ' Over a year ago

I have used the same query but it always returns 0 rows as result.

j ' Over a year ago

Is it because when i look at the data using select * , i see mobile columns like below,"[{\"mobile\": \”1234567891\”, \"status\": \"verified\"}, {\"mobile\": \"1265439872\", \"status\": \"verified\"}]"

Laurenz Albe Over a year ago

Well, it works for me. Perhaps you are not using jsonb after all, or you have some whitespace somewhere, or something else. Perhaps you should come up with a reproducible test case and add that to the question.

Laurenz Albe Over a year ago

What program are you using to look at the data?

j ' Over a year ago

using terminal only

|

Collectives™ on Stack Overflow

Postgres index creation for jsonb column having JSON Array value

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related