PartiQL is an expressive, SQL-compatible query language giving access to relational, semi-structured, and nested data — wherever it lives.
Maintained by Amazon. Developed by open-source contributors.
-- orders: raw JSON documents, no schema, no flattening
SELECT tier,
COUNT(DISTINCT o.id) AS orders,
SUM(i.qty * i.price) AS revenue
FROM orders AS o,
o.items AS i -- unnest, like a join
-- a nested path that is often absent
GROUP BY COALESCE(o.customer.tier, 'basic') AS tier
ORDER BY revenue DESC[
{ 'tier': 'basic', 'orders': 2, 'revenue': 697 },
{ 'tier': 'silver', 'orders': 1, 'revenue': 178 },
{ 'tier': 'gold', 'orders': 2, 'revenue': 165 }
]Why PartiQL
PartiQL's extensions to SQL are easy to understand, treat nested data as first-class citizens, and compose seamlessly with each other and SQL. Filter, join, and aggregate across structured, semi-structured, and nested datasets.
PartiQL separates the syntax and semantics of a query from the format of the data or the store being accessed, giving unified query access across data stores — with or without a regular schema.
The syntax, semantics, embeddable reference interpreter, CLI, test framework, and tests are licensed under Apache 2.0 — use, copy, and distribute your changes under the terms of your choice.
See it in action
Query JSON and Ion documents interactively with the PartiQL REPL, or embed the reference implementation in your own JVM or Rust application.
Install PartiQL CLI$ partiql -d ./db
partiql ▶ WITH revenue AS (
| SELECT o.region, o.qtr, SUM(o.amount) AS total
| FROM orders AS o
| GROUP BY o.region, o.qtr
| )
| SELECT r.region, r.qtr, r.total,
| LAG(r.total) OVER (PARTITION BY r.region
| ORDER BY r.qtr) AS prev
| FROM revenue AS r
| ORDER BY r.region, r.qtr;
=== RESULT ===
[
{ 'region': 'EU', 'qtr': 1, 'total': 200, 'prev': null },
{ 'region': 'EU', 'qtr': 2, 'total': 260, 'prev': 200 },
{ 'region': 'US', 'qtr': 1, 'total': 150, 'prev': null },
{ 'region': 'US', 'qtr': 2, 'total': 130, 'prev': 150 }
]
OK!
partiql ▶ In production
Interact with DynamoDB tables and run ad hoc queries using the AWS Management Console, NoSQL Workbench, AWS CLI, and DynamoDB APIs for PartiQL.
Amazon Redshift uses PartiQL to offer SQL-compatible access to relational, semi-structured, and nested data.
PartiQL syntax is used to write knowledge-graph queries, including Graph Pattern Matching Language.
Multiple Amazon and AWS services use PartiQL to give their customers SQL-compatible access to their data.
PartiQL is open-sourced under the Apache 2.0 License. Read the charter, file an issue, or give us a star on GitHub.