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.

  • 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:

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);
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"
}
]
}
}
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.

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

⚡️ Instant realtime GraphQL APIs! Connect Hasura to your database & data sources (GraphQL, REST & 3rd party API) and get a unified data access layer instantly.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store