Search within n-th item of an array?

[aweisse]

I have a table with a few text arrays as columns (e.g. mylist text[]).

Can I search within only the n-th elements of such lists? How does the API call look like?

I can use ?select=mylist->2 to retrieve only the 3rd element of such lists, but filtering, e.g., with ?mylist->2=eq.42 does not seem to work.

[laurenceisla]

Hmm... it should work. Which PostgREST version are you using and do you have a minimal example to demonstrate that it's not working?

The filter works in our tests. This is the table:

postgrest/test/spec/fixtures/schema.sql

Lines 2495 to 2499 in 5206319

	create table test.arrays (
	id int primary key,
	numbers int[],
	numbers_mult int[][]
	);

And this is a filter test:

postgrest/test/spec/Feature/Query/JsonOperatorSpec.hs

Lines 215 to 221 in 5206319

	it "can filter array item" $ do
	get "/arrays?select=id&numbers->0=eq.1"
	`shouldRespondWith`
	[json| [{"id":0}] |]
	get "/arrays?select=id&numbers->>0=eq.11"
	`shouldRespondWith`
	[json| [{"id":1}] |]

[laurenceisla]

Totally missed the text[] type 🤦. The answer below should be the solution #4093 (comment)

[wolfgangwalther]

That's probably because -> will look for JSON. This works in our tests, because we are filtering for numbers. With -> you'd probably look for a quoted string. But with ->> it should work, because that will return text.

[aweisse]

Yes, cool.
->> did the trick.

Thank you very much! PostgREST rocks!