How to get index of an array value in PostgreSQL?

Question

I have a table called pins like this:

id (int) |      pin_codes (jsonb)
--------------------------------
1        |  [4000, 5000, 6000]
2        |  [8500, 8400, 8600]
3        |  [2700, 2300, 2980]

Now, I want the row with pin_code 8600 and with its array index. The output must be like this:

     pin_codes       |  index
------------------------------
[8500, 8500, 8600]   |   2

If I want the row with pin_code 2700, the output :

     pin_codes       |  index
------------------------------
[2700, 2300, 2980]   |   0

What I've tried so far:

SELECT pin_codes FROM pins WHERE pin_codes @> '[8600]'

It only returns the row with wanted value. I don't know how to get the index on the value in the pin_codes array!

Any help would be great appreciated.

P.S:

I'm using PostgreSQL 10

Answer 1

If you were storing the array as a real array not as a json, you could use array_position() to find the (first) index of a given element:

select array_position(array['one', 'two', 'three'], 'two') 

returns 2

With some text mangling you can cast the JSON array into a text array:

select array_position(translate(pin_codes::text,'[]','{}')::text[], '8600')
from the_table;

The also allows you to use the "operator"

select *
from pins
where '8600' = any(translate(pin_codes::text,'[]','{}')::text[])

The contains @> operator expects arrays on both sides of the operator. You could use it to search for two pin codes at a time:

select *
from pins
where translate(pin_codes::text,'[]','{}')::text[] @> array['8600','8400']

Or use the overlaps operator && to find rows with any of multiple elements:

select *
from pins
where translate(pin_codes::text,'[]','{}')::text[] && array['8600','2700']

would return

id | pin_codes         
---+-------------------
 2 | [8500, 8400, 8600]
 3 | [2700, 2300, 2980]

---

If you do that a lot, it would be more efficient to store the pin_codes as text[] rather then JSON - then you can also index that column to do searches more efficiently.