Query hierarchical data structures on Hasura with Postgres ltree

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.

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.

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:

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);

3. 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.

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"
      }
    ]
  }
}

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"
    }
  ]
}

_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"
      }
    ]
  }
}

_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"
      }
    ]
  }
}

_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"
      }
    ]
  }
}

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"
      }
    ]
  }
}

_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"
      }
    ]
  }
}

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!