#IMPORTANT NOTICE GQL is now fully re-written and enhanced. The repository has been moved to this link https://github.com/Vedadiyan/genql however this repository will remain as it is since many of our project already use this repository and have referenced it.
GQL is an implementation of MySQL
dialect for querying data in complex data structures.
GQL allows you to query data in large and complex data structures at very high performance. The motivation behind writing this library was to use it together with Protobuf
in order to bring about automatic mapping between Message
structures and JSON data at runtime.
GQL relies on a modified version of the sqlparser
package in the Vitess project. It is guaranteed to parse SQL code flawlessly.
You can use GQL to re-model JSON data structures so that they can be auto mapped to your desired data models. For instance, if you are writing a microservice that retrieves data from a third-party API, you can focus on modeling internal data structures while using GQL to re-shape the output of that API to match the internal data model. Once this is done, the output of GQL can be automatically mapped to the internal data model.
- ✅ Subqueries
- ✅ Select Only Expressions
- ❌ Multiple Object Selection (Statements such as
SELECT * FROM object_01, object_02
are not supported) - ✅ Case When
- ✅ Aliases
- ✅ Like Expressions
- ✅ Aggregate Functions (GQL functions are extensible and can be injected when required)
- 🆒 Singleton Functions (The
ONCE
function only executes the function once for all rows) - 🆒 Multi-Dimensional Selectors (
$.root.data.users.{?}.coordinates.{?}.{?}
) - ✅ Limit
- ✅ Group By
- ❎ Joins (Joins are experimental and may require performance tuning)
- ✅ INNER JOIN
- ✅ LEFT JOIN
- ✅ RIGHT JOIN
- ⭕ FULL OUTER JOIN (MySQL does not have full outer joins and GQL is restricted by the MySQL syntax)
- ❌ NATURAL JOIN (There is no plan to implement this feature)
- ❌ NATURAL LEFT JOIN (There is no plan to implement this feature)
- ❌ NATURAL RIGHT JOIN (There is no plan to implement this feature)
- ⭕ Apply / Cross Apply (MySQL does not have apply / cross apply and GQL is restricted by the MySQL syntax)
- ✅ Unions
- ✅ CTEs
- ✅ Having Expression (experimental)
- ✅ Order By
- Join conditions ALWAYS require table aliasing even if they are used in a CTE query
- Keys should always be specified within backticks. For example:
`a.b`
is valid whilea.b
is not. - An array selector keeps the data type and always returns an array even though a single index is selected
1- Basic Example
func Query(json map[string]any) {
ctx := sql.New(json)
result, err := ctx.Exec("SELECT ONCE(AVG(UNWIND(`$.root.data.users.{?}.age`))) as avg_of_age, name, `email.{0}` first_email FROM `$.root.data.users` WHERE `is_verified` = true")
}
2- Encapsulation
SELECT (SELECT `price`, `quantity`) AS stock FROM `$.data.items`
This query will retrieve price
and quantity
from the row and will turn them into a new object called stock
which will be created per row.
3- Arrays
SELECT (SELECT `amount` FROM `tax_data`) AS taxes FROM `$.data.items`
This query will retrieve amount
from an array of objects called tax_data
.
4- Array Selectors
SELECT `rates.{0}` AS first_item FROM `$.data.items` WHERE `rates.{?}.amount` > 10
Array indexes can be reached using the {}
selector. You can pass either a number or a wildcard using the {?}
to select and query arrays.
Please note that although multi-dimensional selectors such as {?}.{?}
are supported, the FROM
clause does not support multi-dimensional selectors. However, the following is valid $.data.items.{0}.rates
Updated in the v0.0.3, multi-dimensional selectors can be used in the FROM
clause
5- Look-Behind and Root Selectors
SELECT
(
SELECT
`$.$.another_object.name` AS `name`
FROM `details`
)
FROM
`$.data`
The FROM
block scopes the reading to the current row. In order to read data not associated with the current row, the Look-Behind or Root Selector feature can be used to navigate back to the previous object. This can be done by using the $
operator.
To use functions, simply import them from the function
package:
import (
_ "github.com/vedadiyan/gql/pkg/functions/avg"
_ "github.com/vedadiyan/gql/pkg/functions/once"
_ "github.com/vedadiyan/gql/pkg/functions/unwind"
)
Although this project is well tested, it is still in development.
Copyright © 2023 Pouya Vedadiyan.
This project is Apache-2.0 licensed.