SQL Query to JSONiq Query

Question

I want to convert an SQL query into a JSONiq Query, is there already an implementation for this, if not, what do I need to know to be able to create a program that can do this ?

Answer 1

I am not aware of an implementation, however, it is technically feasible and straightforward. JSONiq has 90% of its DNA coming from XQuery, which itself was partly designed by people involved in SQL as well.

From a data model perspective, a table is mapped to a collection and each row of the table is mapped to a flat JSON object, i.e., all fields are atomic values, like so:

{
  "Name" : "Turing",
  "First" : "Alan",
  "Job" : "Inventor"
}

Then, the mapping is done by converting SELECT-FROM-WHERE queries to FLWOR expressions, which provide a superset of SQL's functionality.

For example:

SELECT Name, First
FROM people
WHERE Job = "Inventor"

Can be mapped to:

for $person in collection("people")
where $person.job eq "Inventor"
return project($person, ("Name", "First"))

More complicated queries can also be mapped quite straight-forwardly:

SELECT Name, COUNT(*)
FROM people
WHERE Job = "Inventor"
GROUP BY Name
HAVING COUNT(*) >= 2

to:

for $person in collection("people")
where $person.job eq "Inventor"
group by $name := $person.name
where count($person) ge 2
return {
  name: $name,
  count: count($person)
}

Actually, if for had been called from and return had been called select, and if these keywords were written uppercase, the syntax of JSONiq would be very similar to that of SQL: it's only cosmetics.