Select element from a JSONB array?

[city41]

In postgres I can do select data->'my_array'->0 and get the first element of my_array. But when I do the equivalent in postgrest, I always get a result with { 0: null }. Is it possible to select an element out of an array?

[wolfgangwalther]

It would help with troubleshooting, if you were to show the exact request you were trying to make, including a sample schema with some test data that reproduced your problem. Otherwise we will be able to take random guesses only.

(Answer in comment below)

[city41]

Thanks for the reply. I am actually using postgrest through another system, so I will investigate that system first. But it's good to know that this is not expected behavior and that grabbing an element out of an array should work.

[steve-chavez]

@city41 It could be that your postgrest library is quoting the index("0"), then PostgREST will consider that a key(a string) instead of an index(a number).

IIRC the same will also happen on PostgreSQL, try select data->'my_array'->'0'

If you don't force quoting, it should all work as specified here: https://postgrest.org/en/v8.0/api.html#json-columns

[city41]

That makes sense. I have a feel that might be what is happening. Thanks for the help Steve and Wolfgang!