0

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

|improve this 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 Feb 26 at 10:51
  • yes it will be having many duplicates in my case. This is an example scenario. – j ' Feb 26 at 10:53
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);
|improve this answer
  • I have used the same query but it always returns 0 rows as result. – j ' Feb 26 at 11:08
  • 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\"}]" – j ' Feb 26 at 11:10
  • 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 Feb 26 at 11:11
  • What program are you using to look at the data? – Laurenz Albe Feb 26 at 11:12
  • 1
    thanks. I fixed Django code ! And the query works.. – j ' Feb 26 at 13:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.