TheLazy.Dev
Portfolio GitHub

Blog

postgres |community |releases

Query hierarchical data structures on Hasura with Postgres ltree

hasurahq_staff

Hasura

Posted on April 21, 2021

Query hierarchical data structures on Hasura with Postgres ltree

Query hierarchical data structures on Hasura with Postgres ltree
Query hierarchical data structures on Hasura with Postgres ltree

Introducing ltree operator support in Hasura

This post introduces support in Hasura 2.0 for queries on hierarchical data structures, with the help of Postgres ltree operators. It includes several examples, so we recommend that you create a new project in Hasura Cloud to try them out.

What's ltree?

ltree is a Postgres extension for representing and querying data stored in a hierarchical tree-like structure. This post assumes some knowledge of the ltree extension in Postgres, which you can read about here.

ltree enables powerful search functionality that can be used to model, query and validate hierarchical and arbitrarily nested data structures. Here are just a few other use-cases, including suggestions from current Hasura users:

  • configuring permission rules in Hasura for a Customer Relationship Management tool.
  • modelling employee reporting relationships, or a marketplace with sub/categories.
  • validating filesystem paths or DNS records.

Exploring ltree

This post illustrates some of the newly supported ltree comparison operators in Hasura. We'll be working with the same ltree example data structure mentioned in the Postgres docs, which looks like this:

Query hierarchical data structures on Hasura with Postgres ltree
example ltree data structure

Project setup

  1. Set up a new project in Hasura Cloud 2.0 or higher with a Postgres database.
  2. Enable the extension and populate the Postgres database with a value for each label path. You can do this via the Data > SQL tab, as follows: 
CREATE EXTENSION IF NOT EXISTS ltree;

CREATE TABLE test (path ltree);

INSERT INTO
    test
VALUES
    ('Top'),
    ('Top.Science'),
    ('Top.Science.Astronomy'),
    ('Top.Science.Astronomy.Astrophysics'),
    ('Top.Science.Astronomy.Cosmology'),
    ('Top.Hobbies'),
    ('Top.Hobbies.Amateurs_Astronomy'),
    ('Top.Collections'),
    ('Top.Collections.Pictures'),
    ('Top.Collections.Pictures.Astronomy'),
    ('Top.Collections.Pictures.Astronomy.Stars'),
    ('Top.Collections.Pictures.Astronomy.Galaxies'),
    ('Top.Collections.Pictures.Astronomy.Astronauts');

-- Optionally, create indexes to speed up certain operations
CREATE INDEX path_gist_idx ON test USING GIST (path);
CREATE INDEX path_idx ON test USING BTREE (path);
Enter fullscreen mode Exit fullscreen mode
  1. Visit the Explorer section of the API tab in your Hasura Cloud project. You should see ltree operators such as _ancestor and _descendant under the path filter options.

Query hierarchical data structures on Hasura with Postgres ltree
Hasura Cloud Project Explorer

ltree operators

The standard comparison operators such as _eq, _gt and _is_null were available on ltree data before Hasura v2.0, so this post will only illustrate the newer ltree comparison operators, including:

_ancestor 

query {
  test(where: { path: { _ancestor: "Top.Collections.Pictures.Astronomy" } }) {
    path
  }
}


{
  "data": {
    "test": [
      {
        "path": "Top"
      },
      {
        "path": "Top.Collections"
      },
      {
        "path": "Top.Collections.Pictures"
      },
      {
        "path": "Top.Collections.Pictures.Astronomy"
      }
    ]
  }
}
Enter fullscreen mode Exit fullscreen mode

For all ltree queries, the given label path must be valid, otherwise an error message is returned:

query {
  test(where: { path: { _ancestor: "Top.Collections.Pictures." } }) {
    path
  }
}


{
  "errors": [
    {
      "extensions": {
        "path": "$.selectionSet.test.args.where.path._ancestor",
        "code": "parse-failed"
      },
      "message": "Expecting label path: a sequence of zero or more labels separated by dots, for example L1.L2.L3"
    }
  ]
}
Enter fullscreen mode Exit fullscreen mode

_descendant 

query {
  test(where: { path: { _descendant: "Top.Collections.Pictures.Astronomy" } }) {
    path
  }
}


{
  "data": {
    "test": [
      {
        "path": "Top.Collections.Pictures.Astronomy"
      },
      {
        "path": "Top.Collections.Pictures.Astronomy.Stars"
      },
      {
        "path": "Top.Collections.Pictures.Astronomy.Galaxies"
      },
      {
        "path": "Top.Collections.Pictures.Astronomy.Astronauts"
      }
    ]
  }
}
Enter fullscreen mode Exit fullscreen mode

_matches

Match any label path containing the node Astronomy

# Match any label path containing the node `Astronomy`
query {
  test(where: { path: { _matches: "*.Astronomy.*" } }) {
    path
  }
}


{
  "data": {
    "test": [
      {
        "path": "Top.Science.Astronomy"
      },
      {
        "path": "Top.Science.Astronomy.Astrophysics"
      },
      {
        "path": "Top.Science.Astronomy.Cosmology"
      },
      {
        "path": "Top.Collections.Pictures.Astronomy"
      },
      {
        "path": "Top.Collections.Pictures.Astronomy.Stars"
      },
      {
        "path": "Top.Collections.Pictures.Astronomy.Galaxies"
      },
      {
        "path": "Top.Collections.Pictures.Astronomy.Astronauts"
      }
    ]
  }
}
Enter fullscreen mode Exit fullscreen mode

_matches_fulltext

In addition to ltree comparison, Hasura now supports queries on lquery, a String type suitable for pattern matching on ltree label paths.

Match any label path containing a node containing the substring "Astro"

query {
  test(where: { path: { _matches_fulltext: "Astro*%" } }) {
    path
  }
}


{
  "data": {
    "test": [
      {
        "path": "Top.Science.Astronomy"
      },
      {
        "path": "Top.Science.Astronomy.Astrophysics"
      },
      {
        "path": "Top.Science.Astronomy.Cosmology"
      },
      {
        "path": "Top.Hobbies.Amateurs_Astronomy"
      },
      {
        "path": "Top.Collections.Pictures.Astronomy"
      },
      {
        "path": "Top.Collections.Pictures.Astronomy.Stars"
      },
      {
        "path": "Top.Collections.Pictures.Astronomy.Galaxies"
      },
      {
        "path": "Top.Collections.Pictures.Astronomy.Astronauts"
      }
    ]
  }
}
Enter fullscreen mode Exit fullscreen mode

Match any label path containing a node containing the substring "Astro", excluding any descendants of the (case-insensitive) "Pictures" node

query {
  test(where: { path: { _matches_fulltext: "Astro*% & !pictures@" } }) {
    path
  }
}


{
  "data": {
    "test": [
      {
        "path": "Top.Science.Astronomy"
      },
      {
        "path": "Top.Science.Astronomy.Astrophysics"
      },
      {
        "path": "Top.Science.Astronomy.Cosmology"
      },
      {
        "path": "Top.Hobbies.Amateurs_Astronomy"
      }
    ]
  }
}
Enter fullscreen mode Exit fullscreen mode

_any variants

ancestor, descendant and matches operators all have the *_any variant available, too.

query {
  test(
    where: {
      path: {
        _ancestor_any: [
          "Top.Collections.Pictures.Astronomy"
          "Top.Science.Astronomy"
        ]
      }
    }
  ) {
    path
  }
}


{
  "data": {
    "test": [
      {
        "path": "Top"
      },
      {
        "path": "Top.Science"
      },
      {
        "path": "Top.Science.Astronomy"
      },
      {
        "path": "Top.Collections"
      },
      {
        "path": "Top.Collections.Pictures"
      },
      {
        "path": "Top.Collections.Pictures.Astronomy"
      }
    ]
  }
}
Enter fullscreen mode Exit fullscreen mode

Further resources

In this post we looked at the newly supported ltree operators, along with some motivating use-cases and examples. A recording of the same demo from Hasura's community call is embedded below.

Please add a comment to the original ltree support GitHub issue if you'd like to share your own use case, or request support for other ltree operators.

See the API reference in Hasura docs for the up-to-date list of supported ltree operators.

If you'd like to extend support for ltree or other operators, here's a CodeFlow walkthrough of the relevant parts of the codebase. We look forward to your contribution!

💖 💪 🙅 🚩
hasurahq_staff
Hasura

Posted on April 21, 2021

Join Our Newsletter. No Spam, Only the good stuff.

Sign up to receive the latest update from our blog.

Related

Query hierarchical data structures on Hasura with Postgres ltree
postgres Query hierarchical data structures on Hasura with Postgres ltree

April 21, 2021