Introduction to PathQL

This is what PathQL does in one sentence:

PathQL allows grouping and nesting of the results of a complex SQL query using JSONPath notation in SQL column aliases

This is what PathQL does in one example:

select 
    posts.id as "$.posts[].id", 
    comments.id as "$.posts[].comments[].id" 
from 
    posts, 
    comments 
where 
    post_id = posts.id and
    posts.id = 1 

Without PathQL the results would be (wrong):

[
    {
      "$.posts[].id": 1,
      "$.posts[].comments[].id": 1
    },
    {
      "$.posts[].id": 1,
      "$.posts[].comments[].id": 2
    }
]

With PathQL the results will be (correct):

{
    "posts": [
        {
            "id": 1,
            "comments": [
                {
                    "id": 1
                },
                {
                    "id": 2
                }
            ]
        }
    ]
}

The rows have been merged into a tree according to the paths specified in the column aliases.

JSON path syntax

In JSONPath a language is defined for reading data from a path in a JSON document. We need a langauge to write data to a path in a JSON document and that is why we only use a subset of the JSONPath operators:

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

Note that the brackets should always be empty as the index in the array is determined by the path merging algorithm.

Base path

The path “$[].posts.id” consists of two parts “$[].posts” (the base path) and it’s last segment “id” (the property name). Only columns that have an alias starting with a “$” are defining a new base path. Any other alias or column name is treated as a property name.

The full path of a column can be constructed by combining the last specified base path with the property name. The initial base path for every query is “$[]”.

Thus queries without specified paths generate a simple object per result row:

SELECT * FROM posts;

[
    {
        "id": 1,
        "title": "Hello world!"
    },
    ...(more rows)...
]

Columns that follow a column with a specified path will inherit the base path:

SELECT id as "$[].post.id", title FROM posts;

[
    {
        "post": {
            "id": 1,
            "title": "Hello world!"
        }
    },
    ...(more rows)...
]

Note that duplicate and/or conflicting paths trigger an error message.

PathQL web API

A path engine implementation can be added to any DBAL. Before it is a PathQL web API you also need to implement the JSON conversion and HTTP handling.

Here is an example showing how to “speak” PathQL over HTTP(S). The request:

POST /pathql HTTP/1.1
Content-Type: application/json

{"query":"select posts.id as \"$.posts[].id\", comments.id as \"$.posts[].comments[].id\" 
from posts, comments where post_id = posts.id and posts.id = :id;","params":{"id":1}}

And the response:

Content-Type: application/json

{"posts":[{"id":1,"comments":[{"id":1},{"id":2}]}]}

As you can see you should make an endpoint named “pathql” that accepts and returns JSON. The request should be sent in the POST body as a JSON object with properties “query” and “params”, where “query” must be a (SQL) string with named parameters and “params” must be the set of named parameters that should be applied.