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:
Project setup
- Set up a new project in Hasura Cloud 2.0 or higher with a Postgres database.
- 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!
Originally published at https://hasura.io on April 21, 2021.