Search jsonb fields in postgresql with Hasura

user_1984151 0
Viewed 1670

Is it possible to do a greater than search across a jsonb field using hasura?

it looks to be possible in PostgreSQL itself, How can I do less than, greater than in JSON Postgres fields?

in postgres I'm storing a table

asset

  • name: string
  • version: int
  • metadata: jsonb

the metadata looks like this.

{'length': 5}

I am able to find asset that matches exactly using the _contains.

{
  asset(where:{metadata : {_contains : {length: 5}}}){
    name
    metadata
  }
}

I would like to be able to find asset with a length over 10.

I tried:

{
  asset(where:{metadata : {_gt : {length: 10}}}){
    name
    metadata
  }
}
hasura postgresql
1 Answers

A. Possibility to do on graphql level directly

Hasura documentation: JSONB operators (_contains, _has_key, etc.) mentions only 4 operators:

The _contains, _contained_in, _has_key, _has_keys_any and _has_keys_all operators are used to filter based on JSONB columns.

So direct answer for your question: No. It's not possible to do on graphql level in hasura.

(At least it's not possible yet. Who knows: maybe in future releases more operators will be implemented. )

B. Using derived views

But there is another way, the one explained in https://hasura.io/blog/postgres-json-and-jsonb-type-support-on-graphql-41f586e47536/#derived-data

This recomendation is repeated in: https://github.com/hasura/graphql-engine/issues/6331

We don't have operators like that for JSONB (might be solved by something like #5211) but you can use a view or computed field to flatten the text field from the JSONB column into a new column and then do a like on that.

Recipe is:

1. Create a view

CREATE VIEW assets -- note plural here. Name view accordingly to your style guide
AS
SELECT
      name, 
      version,
      metadata,
      (metadata->>'length')::int as meta_len  -- cast to other number type if needed
   FROM asset

2. Register this view

3. Use it in graphql queries as usual table

E.g.

query{
  assets(where: {meta_len: {_gt:10}}){
   name
   metadata
}

C. Using SETOF-functions

1. Create SETOF-function

CREATE FUNCTION get_assets(min_length int DEFAULT 0)
RETURNS SETOF asset
LANGUAGE SQL
STABLE
AS $$
SELECT * FROM asset
WHERE
  (metadata->>'length')::int > min_length;
$$;

2. Register in hasura

3. Use in queries

query{
  get_assets(args: {min_length: 10}){
   name
   metadata
}

I think that was the last possible option.

It will not gives you full "schemaless freedom" that maybe you're looking but IDK know about other ways.

user_11682120
Related