Query filter for array of composite type

[skuby2]

Hi!

I have a table that has an array of a composite type and am trying to figure out how to create an API query that will search the array. I've tried iterating on a lot of different syntaxes and haven't found the solution yet. The use case is basically this: #2743, but instead of using json or jsonb I'm instead using a composite type. Maybe the composite type won't work and I need to switch(?), but I was hoping to find out for sure from the experts before making a switch on data types.

Here's an example that I hope illustrates the problem.

SQL:

CREATE TYPE alias AS (source text, alias text);

CREATE TABLE table_person (
    id varchar(36) PRIMARY KEY,
    aliases alias []
);

CREATE VIEW "person" AS
SELECT
    t."id" AS "id",
    t."aliases" AS "aliases"
FROM
    "table_person" t;

If I try something like this:

GET person?aliases=cs.[{"alias":"some alias to find"}]

I get this error return:

{
	"hint": null,
	"details": "\"[\" must introduce explicitly-specified array dimensions.",
	"code": "22P02",
	"message": "malformed array literal: \"[{\"alias\":\"some alias to find\"}]\""
}

[laurenceisla]

Hello!

I'm not sure if this can be done directly with custom types. The cs operator, equivalent to contains @> in PostgreSQL, works in that example because it suggests using a jsonb type. For arrays, it's a bit different, I think you would need to use the whole composite type (source and alias) to compare.

But since you're using a VIEW to query the data, it should be easier to just convert your custom type to jsonb in another column like:

CREATE VIEW "person" AS
SELECT
    t."id" AS "id",
    t."aliases" AS "aliases",
    to_jsonb(t."aliases") AS "aliases_jsonb" -- convert custom type to jsonb
FROM
    "table_person" t;

Then your query should work with that new column:

GET /person?select=id,aliases&aliases_jsonb=cs.[{"alias":"some alias to find"}]

You could also convert "aliases" directly in the view if you prefer (though some things may change, like the order of the custom type columns).

[skuby2]

Thanks! If I wanted to find out for certain whether it will work without using jsonb do you have any suggestions for how I would go about that?

[wolfgangwalther]

To do the same in PostgreSQL first, without jsonb, it seems only possible by using unnest: https://www.postgresql.org/message-id/flat/CACgbiFtfrX5SGFEZSPDFpAOE%3DPtqA8UzJXDOF8Mq-n3HcnTgUA%40mail.gmail.com

But that kind of query is impossible to create with PostgREST. So I'd say using the jsonb approach is very likely your best option.

[wolfgangwalther]

Writing this.. maybe there even is a way to basically use unnest. You could try to use a computed relationship (https://postgrest.org/en/stable/references/api/resource_embedding.html#computed-relationships). You can just return the unnested array from this function and then try your regular embedding operators, including filtering, on them.

[wolfgangwalther]

While the computed relationship way would work as well, it's rather complicated. However, using a computed column, this can be done easily:

CREATE FUNCTION alias(table_person) RETURNS text[]
IMMUTABLE
LANGUAGE SQL AS $$
  SELECT array_agg(alias) FROM unnest($1.aliases);
$$;

and then

GET /table_person?alias=cs.{some alias to find}