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 ?
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.