Hasura: How to filter inside arrays

Question

I have a Postgres database with the following schema:

"article_id" type: text

"recommendations" type: text[]

An example row would look like this:

"xxxx1", ["xxxx2", "xxxx3", "xxxx5"]

Now in Hasura I can filter on recommendations for an article_id like this for example:

query get_recommendations {
  recommendations(limit: 10, where: {article_id: {_eq: "xxxx1"}}) {
    recommendations
  }
}

This would give me ["xxxx2", "xxxx3", "xxxx5"]

But how would I filter specific recommendations from the recommendations array?

Basically I would like to get recommendations for article_id "xxxx1", but not recommendation "xxxx3".

The result should be ["xxxx2", "xxxx5"].

I tried all combinations of filters in Hasura, but this doesn't seem to be possible? Can you help me here?

score 2 · Accepted Answer · answered Feb 19 '21 at 02:10

You should be able to do this using computed fields in hasura.

Create a function like so, which takes a row from recommendations table as input

CREATE FUNCTION public.filtered_recommendations(rec_row recommendations, recommendations_to_filter text[])
 RETURNS json
 LANGUAGE sql STABLE
AS $function$
  select ( select json_agg(unnest) AS filtered_recommendations FROM UNNEST(rec_row.recommendations) where unnest <> ALL(recommendations_to_filter) )  FROM recommendations
$function$

Then add this function as a computed field. This computed field will show up as a field inside recommendations.

What has made this a bit complicated is the use of an array to store recommendations for an article. If schema of the table was instead (article_id text, recommendation text), you could have had filters for recommendations just like you did with article_id.

Hasura: How to filter inside arrays

1 Answers1