This is what PathQL does in one sentence:

PathQL automatically structures SQL query results as nested JSON by analyzing your database’s foreign keys and join relationships

This is what PathQL does in one example:

select 
    posts.id, posts.title,
    comments.id, comments.message
from 
    posts
left join
    comments on comments.post_id = posts.id
where 
    posts.id = 1

Without PathQL the results would be (wrong):

[
    {
      "id": 1,
      "title": "Hello world!",
      "id": 1,
      "message": "Hi!"
    },
    {
      "id": 1,
      "title": "Hello world!",
      "id": 2,
      "message": "Thank you."
    }
]

With PathQL the results will be (correct):

[
    {
        "id": 1,
        "title": "Hello world!",
        "comments": [
            {
                "id": 1,
                "message": "Hi!"
            },
            {
                "id": 2,
                "message": "Thank you."
            }
        ]
    }
]

The rows have been automatically merged into a tree based on the foreign key relationships and join structure in your query.

Automatic Path Inference

PathQL automatically determines the JSON structure by analyzing:

  1. Foreign key relationships - The engine reads your database schema to understand which tables are related and how (one-to-many, many-to-one)
  2. Join structure - LEFT JOIN typically indicates a one-to-many relationship where the joined table should be nested as an array
  3. Table names - Table names become property names in the JSON output

For most queries, you don’t need to specify any paths at all!

Optional Path Hints

When you need to customize the structure, you can override the automatic inference using SQL comments with path hints:

select 
    posts.title,
    comments.id, comments.message
from 
    posts
left join
    comments on comments.post_id = posts.id
where 
    posts.id = 1

And the following path hints:

{
    "posts": "$.blog.posts",
    "comments": "$.blog.posts[].comments"
}

Lead to a result liek this:

{
    "blog": {
        "posts": [
            {
                "id": 1,
                "title": "Hello world!",
                "comments": [
                    {
                        "id": 1,
                        "message": "Hi!"
                    },
                    {
                        "id": 2,
                        "message": "Thank you."
                    }
                ]
            }
        ]
    }
}

Path hints use a subset of JSONPath operators:

  • ”$” root element
  • ”.” object child operator
  • ”[]” array element operator

Note that path hints apply to table aliases, not individual columns. The brackets should always be empty as the index in the array is determined by the path merging algorithm.