Query hierarchical data structures on Hasura with Postgres ltree
Tags: postgres, community, releases
Published: 2021-04-21 16:44:49

---
title: Query hierarchical data structures on Hasura with Postgres ltree
published: true
date: 2021-04-21 12:24:58 UTC
tags: Postgres,PostgreSQL,Community,Releases
canonical_url: https://hasura.io/blog/query-hierarchical-data-structures-on-hasura-with-postgres-ltree/
---

![Query hierarchical data structures on Hasura with Postgres ltree](https://hasura.io/blog/content/images/2021/04/ltree.png)<figcaption>Query hierarchical data structures on Hasura with Postgres ltree</figcaption>

<!--kg-card-begin: markdown-->
## 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](https://hasura.io/docs/latest/graphql/cloud/getting-started/index.html#cloud-getting-started) 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](https://www.postgresql.org/docs/current/ltree.html).

`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](https://hasura.io/docs/latest/graphql/core/auth/authorization/permission-rules.html) 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](https://www.postgresql.org/docs/current/ltree.html), which looks like this:

<!--kg-card-end: markdown-->

![Query hierarchical data structures on Hasura with Postgres ltree](https://hasura.io/blog/content/images/2021/04/ltree_v1-1.png)<figcaption>example ltree data structure</figcaption>

<!--kg-card-begin: markdown-->
### Project setup

1. [Set up a new project in Hasura Cloud](https://hasura.io/docs/latest/graphql/cloud/getting-started/index.html#cloud-getting-started) 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.

![Query hierarchical data structures on Hasura with Postgres ltree](https://hasura.io/blog/content/images/2021/04/project-explorer.png)<figcaption>Hasura Cloud Project Explorer</figcaption>

<!--kg-card-begin: markdown-->
### `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`
<!-- The query descriptions in italics might also be more readable as code comments. I've given examples of both here, please pick whichever you prefer. -->

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

```
<!--kg-card-end: markdown-->
### 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](https://github.com/hasura/graphql-engine/issues/625) if you'd like to share your own use case, or request support for other `ltree` operators.

See the [API reference in Hasura docs](https://hasura.io/docs/latest/graphql/core/api-reference/graphql-api/query.html#ltree-operators) 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](https://beta.usecodeflow.com/sassela/graphql-engine/c/add-support-for-a-postgresql-comparison-operator) of the relevant parts of the codebase. We look forward to your contribution!

<!--kg-card-end: markdown-->

{% youtube _hPbpDUniFQ %}