Database Depth
SFSQL is designed to solve two hard problems often encountered with relational databases.

"Data Structure Flexibility" - Managing complex and frequently changing data structures

"Querying Ability" - Adhoc querying of complex structures.

SFSQL enhances the functionality of a SQL database with the flexibility of NoSQL's schemaless capabilities while maintaining the Deep Querying Power of SQL Relations.

Database Depth
Complex Object Graphs
with Object-Attribute-Value Queries
SFSQL instantly persist Object Graphs from unaltered JSON with the command.

Object Graphs are easily evolved through many to many Object linking with the elegant yet powerful command as well as the , and commands.

Access point-in-time Object attribute structures with the command.

Object attribute paths and values are maintained in a OAV table space, enabling index speed, adhoc, Object attribute queries (including all DB functions) and even across Object Graphs, with a familiar SQL syntax.


Modify
[ { "modify": { "data": { "Person": [ { "fname": "Sue", "lname": "Smith", "age": 30, "address": { "street": "346 Style Street", "city": "New York", "state": "NY" } }, { "fname": "Tom", "lname": "Jones", "age": 55, "address": { "street": "346 Style Street", "city": "New York", "state": "NY" } } ] }, "_comment": "Create Object Graph" } } ]
Ref
[ { "modify": { "data": { "Company": { "Cname": "Apple", "i:coid": 1, "Employees": { "Employee": [ { "#ref": "SELECT $o:.Person.oid() WHERE $s:.Person.fname='Sue' AND $s:.Person.lname='Smith'" }, { "#ref": "SELECT $o:.Person.oid() WHERE $s:.Person.fname='Tom' AND $s:.Person.lname='Jones'" } ] } } }, "_comment": "Reference Person Object from Employee Object" } }, { "modify": { "data": { "Contactlist": { "contact": [ { "#ref": "SELECT $o:.Person.oid() WHERE $s:.Person.fname='Sue' AND $s:.Person.lname='Smith'" }, { "#ref": "SELECT $o:.Person.oid() WHERE $s:.Person.fname='Tom' AND $s:.Person.lname='Jones'" } ] } }, "_comment": "Reference Person Object from Contact Object" } } ]
Set
[ { "modify": { "data": { "o:Person": { "#set": { "where": "$s:Person.fname='Joe'" }, "fname": "Joe", "lname": "Smith", "age": 35, "address": { "street": "346 Style Street", "city": "New York", "state": "NY" } } }, "_comment": "Set Person with first name 'Joe', creates new object if Object does not exist Updates if Object exists" } } ]
Update
[ { "modify": { "data": { "o:Person": { "#update": { "where": "$s:Person.fname='Sue'" }, "fname": "Sue Ann", "i:age": 35 } }, "_comment": "update Sue's first name to 'Sue Ann'" } } ]
Append
[ { "modify": { "data": { "o:Person": [ { "#append": {} }, { "fname": "Cathy", "lname": "Johnson", "i:age": 25, "d:birthdate": "06/15/1996" } ] }, "_comment": "Append Person Object" } } ]
Show Attributes
[ { "showattrs": {} } ] // results [ { "data" : [ "o:Company", "s:Company.Cname", "i:Company.coid", "o:Company.Employees", "o:Company.Employees.Employee", "o:Company.Employees.Employee.address", "s:Company.Employees.Employee.address.city", "s:Company.Employees.Employee.address.state", "s:Company.Employees.Employee.address.street", "i:Company.Employees.Employee.age", "s:Company.Employees.Employee.fname", "s:Company.Employees.Employee.lname", "o:Contactlist", "o:Contactlist.contact", "o:Contactlist.contact.address", "s:Contactlist.contact.address.city", "s:Contactlist.contact.address.state", "s:Contactlist.contact.address.street", "i:Contactlist.contact.age", "s:Contactlist.contact.fname", "s:Contactlist.contact.lname", "o:Person", "o:Person.address", "s:Person.address.city", "s:Person.address.state", "s:Person.address.street", "i:Person.age", "d:Person.birthdate", "s:Person.fname", "s:Person.lname" ], "success" : 1, "cmdname" : "showattrs" } ]
Query
[ { "query": { "sfsql": "SELECT $o:Person.oid() AS OID, $s:Person.fname AS FNAME, $s:Person.lname AS LNAME, $i:Person.age AS AGE, $$s:Person.address.city as CITY,$$d:Person.birthdate as BDAY", "_comment": "query Person '$$' for left join" } }, { "query": { "sfsql": "SELECT $o:Company.oid() as OID, $s:Company.Cname as Cname, $s:Company.Employees.Employee.lname as Lname, $i:Company.Employees.Employee.age as Age where $i:Company.Employees.Employee.age > 40", "_comment": "query Company employee with where clause" } }, { "query": { "sfsql": "SELECT $s:Person.lname AS LASTNAME, count($s:Person.lname) AS COUNT GROUP BY LASTNAME", "_comment": "Query with Count and GROUP BY" } } ] // RESULTS [ { "data" : [ { "LNAME" : "Smith", "CITY" : "New York", "BDAY" : null, "OID" : 2, "FNAME" : "Sue Ann", "AGE" : 35 }, { "LNAME" : "Jones", "CITY" : "New York", "BDAY" : null, "OID" : 4, "FNAME" : "Tom", "AGE" : 55 }, { "LNAME" : "Smith", "CITY" : "New York", "BDAY" : null, "OID" : 9, "FNAME" : "Joe", "AGE" : 35 }, { "LNAME" : "Johnson", "CITY" : null, "BDAY" : "1996-06-15 00:00:00", "OID" : 11, "FNAME" : "Cathy", "AGE" : 25 } ], "_comment" : "query Person '$$' for left join", "success" : 1, "cmdname" : "query" }, { "data" : [ { "Lname" : "Jones", "OID" : 6, "Cname" : "Apple", "Age" : 55 } ], "_comment" : "query Company employee with where clause", "success" : 1, "cmdname" : "query" }, { "data" : [ { "LASTNAME" : "Smith", "COUNT" : 2 }, { "LASTNAME" : "Jones", "COUNT" : 1 }, { "LASTNAME" : "Johnson", "COUNT" : 1 } ], "_comment" : "Query with Count", "success" : 1, "cmdname" : "query" } ]
SQL-like Query Language
SFSQL is a SQL-like query language. It allows related objects to be represented via simple dot-notation (e.g. $s:company.person.address)

If you know SQL, then you already know how to query data using SFSQL because SFSQL is SQL, but simplified.

NOT a Document Store
Below is a nested Object Graph represented in JSON. SFSQL creates the Object Graphs attribute structure from the submitted JSON. To retrieve the attributes in the nested company, person and address objects see the query below


{ "company": [ // Array of company Objects { "id": 1, "name": "ABC INC", "person": [ // Array of person Objects { "firstName": "Tom", "lastname": "Jones", "age": 55, "address": { "street": "346 Style Street", "city": "New York", "state": "NY" } }, ... ] }, .... ] }
[ { "data" : [ // attribute structure created from JSON Object Graph "o:company", "i:company.id", "s:company.name", "o:company.person", "o:company.person.address", "s:company.person.address.city", "s:company.person.address.state", "s:company.person.address.street", "i:company.person.age", "s:company.person.firstName", "s:company.person.lastname" ], "success" : 1, "cmdname" : "showattrs" } ]


SELECT
$s:company.name,
$s:company.person.firstName,
$s:company.person.lastName,
$i:company.person.age,
$s:company.person.address.street,
$s:company.person.address.city,
$s:company.person.address.state
WHERE $i:company.person.age > 40
ORDER BY $s:company.name ASC

NOTE: The s: attribute prefix denotes a string type. See datatypes.

Notice that all "columns" (in SchemafreeSQL they are "attributes") must be prefixed with a dollar sign ($) which differentiates a SchemafreeSQL attribute from other SQL grammar.

Optionally, the double dollar sign ($$) can be used which denotes a left-join relationship (i.e. include the parent object even when it doesn't have a value for a specified child attribute.)

Syntax
SFSQL is essentially standard SQL syntax but without the JOIN complexities and FROM clauses.

SELECT
select_expr [, select_expr ...]
FROM
JOIN
[WHERE where_condition]
[GROUP BY {col_name | expr | position}
  [ASC | DESC], ... [WITH ROLLUP]]
[HAVING where_condition]
[ORDER BY {col_name | expr | position}
  [ASC | DESC], ...]
[LIMIT {[offset,] row_count
  | row_count OFFSET offset}] [FOR UPDATE]

Example SFSQL query
SELECT count($o:company.person.oid()) AS PCOUNT, $s:company.name AS CONAME
WHERE $i:company.person.age > 25
GROUP BY CONAME
HAVING PCOUNT > 1
ORDER BY CONAME ASC
LIMIT 100
To Introduce you to SFSQL we will create a "Hello World Shop Admin" App.
SFSQL is schemaless so we can just start adding data to our "Hello World Shop Admin" app. Commands are issued to SFSQL via a JSON over HTTPS API. SFSQL expects an array of commands even for a single command payload.

To insert and update data we use the modify command. The modify data attribute accepts valid JSON (with the exception of nested arrays)

A unique Object ID (OID) is created for each Object SFSQL encounters. Non-Object attributes values are auto typed or type hinted. Data types are not validated, they are set, with the exception of date types which are validated. SFSQL will conform the submitted value to the set type e.g.("text" converted to 0 for integer type). This ensures integrity in the data. Currently in development is a "Rules API", which will allow for configurable fine grained attribute validations. Validation is left up to the application for now.

A reference path is created for each Object encountered e.g. "shops.shop.supplier". Multiple reference paths may be created for each Object. All attributes, objects and non-objects, are indexed arrays allowing data to be appended to each attribute in the Object Graph.

[ { "modify": { "data": { // data attribute accepts valid JSON, may be deeply nested Objects Graph ( nested arrays not allowed [1,2,3,[4,5],6] ) "shops": { "shop": { "supplier": { "name": "Sony Group Corporation", "id": "sony" } } } } } } ]
SFSQL will create the Object Graph structure as it is encountered within a modify command, allowing Objects to be immediately referenced within the same modify command and by subsequent commands in the payload with the ref command.

We added an array of Product Objects to our Shop. We also created a Payload Scoped Reflective Parameter "__sony" and set its value to the OID of its created Parent Object "!@result.oid@!".

Each Product has a child manufacturer Object. The "#ref" attribute is used to creates a reference to an existing Object. The value of the #ref attribute must be an OID, this is where the Payload Scoped Parameter with "!@result.oid@!" values can be used or an OID may be selected with a query. The query must return a single OID. For the two other products, the manufacturer Object is created inline or embedded as it is often called.

Reflective Parameters are underscore-prefixed. They are returned with the payload results. Payload Scoped Parameters have all the qualities of Reflective Parameters with the addition of being available to all other commands within the same request payload. Payload Scoped Parameters are double-underscore-prefixed.

[ { "modify": { "data": { "shops": { "shop": { "supplier": { "__sony": "!@result.oid@!", // Payload Scoped Reflective Parameter "name": "Sony Group Corporation", "id": "sony" }, "product": [ { "name": "FinePix Pro2 3D Camera", "code": "3DcAM01", "image": "camera.jpg", "n:price": 300.0, "i:totalordered": 0, "manufacturer": { "#ref": "!@__sony@!" // reference OID using Payload Scoped Reflective Parameter } }, { "name": "EXP Portable Hard Drive", "code": "USB02", "image": "external-hard-drive.jpg", "n:price": 800.0, "i:totalordered": 0, "manufacturer": { "#ref": "SELECT $o:.shop.supplier.oid() WHERE $s:.shop.supplier.id='sony'" // reference OID with query } }, { "name": "Luxury Ultra thin Wrist Watch", "code": "wristWear03", "image": "watch.jpg", "n:price": 100.0, "i:totalordered": 0, "manufacturer": { // create Object inline (embedded) "name": "TAG Heuer", "id": "tag" } }, { "name": "XP 1155 Intel Core Laptop", "code": "LPN45", "image": "laptop.jpg", "n:price": 250.0, "i:totalordered": 0, "manufacturer": { // create Object inline (embedded) "name": "DELL Inc", "id": "dell" } } ] } } } } } ]
Now let's finish off this modify command by adding customers, orders, and do a little house cleaning.

We created a Customer Object with a set sub command. Set determines if an Object satisfies the where condition. If a single Object is found it is updated if no Objects are found one is created, multiple returned objects results in an error.

Two Order Objects are added to the Order Array with the append sub command. Append adds new attributes to the end of the array.

The "cust" object of Order 1 references an existing Objects. The "ref" attribute behaves like an update sub command. Attributes added after the "ref" attribute will be added to the objects or updated if they exist. In this case the "email" attribute is added to the referenced Object. The "cust" Object of Order 2 is embedded into the order.

The Embedded "cust' objects and "manufacturer" objects are appended to the Customer and Supply arrays. This creates references to theses Objects in one location, aiding in querying them. Creating references to an object has no overhead associated with it. As different views to your data is required, reshaping its structure is easily accomplished with SFSQL.

Notice the "o:customer and "o:supplier", adding the data type avoids the JSON processing error of duplicate key. This could have been done in another modify command or even a separate payload but we want to keep things as concise as possible.

Notice the "#pass": "v + 'qty' " attribute in the item objects of the orders, it increments the value "v" of the referenced object by the 'qty'. This is accomplished by passing the "+" function to the underlying DB. With SFSQL you have access to the SQL Functions of the back-end DB (MySQL 8 currently).

[ { "modify": { "data": { "shops": { "shop": { "supplier": { "__sony": "!@result.oid@!", "name": "Sony Group Corporation", "id": "sony" }, "product": [ { "__3DcAM01": "!@result.oid@!", // added Payload Scoped Reflective Parameter "name": "FinePix Pro2 3D Camera", "code": "3DcAM01", "image": "camera.jpg", "n:price": 300.0, "i:totalordered": 0, "manufacturer": { "#ref": "SELECT $o:.shop.supplier.oid() WHERE $s:.shop.supplier.id='sony'" } }, { "__USB02": "!@result.oid@!", // added Payload Scoped Reflective Parameter "name": "EXP Portable Hard Drive", "code": "USB02", "image": "external-hard-drive.jpg", "n:price": 800.0, "i:totalordered": 0, "manufacturer": { "#ref": "SELECT $o:.shop.supplier.oid() WHERE $s:.shop.supplier.id='sony'" } }, { "__wristWear03": "!@result.oid@!", // added Payload Scoped Reflective Parameter "name": "Luxury Ultra thin Wrist Watch", "code": "wristWear03", "image": "watch.jpg", "n:price": 100.0, "i:totalordered": 0, "manufacturer": { "__tag": "!@result.oid@!", // added Payload Scoped Reflective Parameter "name": "TAG Heuer", "id": "tag" } }, { "__LPN45": "!@result.oid@!", // added Payload Scoped Reflective Parameter "name": "XP 1155 Intel Core Laptop", "code": "LPN45", "image": "laptop.jpg", "n:price": 250.0, "i:totalordered": 0, "manufacturer": { "__dell": "!@result.oid@!", // added Payload Scoped Reflective Parameter "name": "DELL Inc", "id": "dell" } } ], "o:supplier": [ // appended embedded "manufacturer" objects to suppler array with append sub command. Object is typed to avoid JSON processing error of duplicate key { "#append": {} }, { "#ref": "!@__tag@!" }, { "#ref": "!@__dell@!" } ], "customer": { // created objects with set sub command "#set": { "where": "$s:customer.id='c1111'" }, "__c1111": "!@result.oid@!", "s:id": "c1111", "s:first_name": "Larry", "s:last_name": "Smith", "o:address": { "street": "5 Elmwood Avenue", "city": "Rochester", "state": "NY", "zip": "14616" } }, "o:order": [ // inserted orders with append sub command { "#append": {} }, { "i:order_id": 1, "o:cust": { "#ref": "!@__c1111@!", // referenced and object and updated it with a new string attribute "email" "email": "support@schemafreesql.com" }, "o:lineitem": [ { "o:item": { "#ref": "!@__3DcAM01@!", // referenced object and increment "totalordered" by quantity ordered "i:totalordered": { "#pass": "v + 1" } }, "i:no": 1, "i:qty": 1, "n:price": 300.0 }, { "o:item": { "#ref": "!@__wristWear03@!", // referenced object and increment "totalordered" by quantity ordered "i:totalordered": { "#pass": "v + 2" } }, "i:no": 2, "i:qty": 2, "n:price": 100.0 } ] }, { "i:order_id": 2, "o:cust": { // embedded cust object "__c2222": "!@result.oid@!", "s:id": "c2222", "s:first_name": "Sally", "s:last_name": "Swanson", "email": "feedback@schemafreesql.com", "o:address": { "street": "7 Broadway", "city": "New York", "state": "NY", "zip": "10003" } }, "o:lineitem": [ { "o:item": { "#ref": "!@__3DcAM01@!", // referenced object and increment "totalordered" by quantity ordered "i:totalordered": { "#pass": "v + 1" } }, "i:no": 1, "i:qty": 1, "n:price": 300.0 }, { "o:item": { "#ref": "!@__LPN45@!", // referenced object and increment "totalordered" by quantity ordered "i:totalordered": { "#pass": "v + 2" } }, "i:no": 2, "i:qty": 2, "n:price": 250.0 } ] } ], "o:customer": [ // appended embedded "cust" objects to customer array with append sub command. Object is typed to avoid JSON processing error of duplicate key { "#append": {} }, { "#ref": "!@__c2222@!" } } } } } } ]
These 4 queries commands are appended to the "Hello World Shop Admin" app Payload.

While SFSQL models complex Tree and Graph Structures, its current querying capabilities are Object-attribute-value (OAV). Object attributes selected in the query are returned instead of complete Object graphs. This allows for Object attributes to be selected across the entire Object Graph and Across Object Graphs easily and efficiently. Complete Object graph results are on the todo list.

The four queries select supplier, product, customer, and order attributes respectfully. All attributes in a query must be typed unlike a modify command where auto typing is available. Attribute paths starting with a '.' are global. This avoids having to enter the full path of the attribute and also will return attributes across Object Graphs. Object Attributes selected in a query must be in the same path to the right of the starting path name e.g. $s:.suppler.name $s:.supplier.address.street

You can see the the complete payload here

{ "query": { // .supplier global search avoids having to enter full path of "shops.shop.supplier." "sfsql": "SELECT $s:.supplier.name as name, $s:.supplier.id as id", "_q": "supp" //Reflective Parameter } }, { "query": { // select all products global "sfsql": "SELECT $s:.product.code as code,$s:.product.name as name,$s:.product.image as image,$n:.product.price as price,$i:.product.totalordered as totordered,$s:.product.manufacturer.name as manname ORDER BY code ASC", "_q": "prod" //Reflective Parameter } }, { "query": { // select all customer global "sfsql": "SELECT $s:.customer.id as cusid,$s:.customer.first_name as fname,$s:.customer.last_name as lname,$s:.customer.email as email,$s:.customer.address.street as street,$s:.customer.address.city as city,$s:.customer.address.state as state,$s:.customer.address.zip as zip", "_q": "cust" //Reflective Parameters }, }, { "query": { // select order,line items, manufacturers, customers info in a single query "sfsql": "SELECT $i:.order.order_id as orderid, $s:.order.lineitem.item.code as code,$s:.order.lineitem.item.name as name,$s:.order.lineitem.item.manufacturer.name as manname, $s:.order.lineitem.item.image as image, $i:.order.lineitem.no as no, $i:.order.lineitem.qty as quantity, $n:.order.lineitem.price as price, $s:.order.cust.id as cusid,$s:.order.cust.first_name as fname, $s:.order.cust.last_name as lname,$s:.order.cust.email as email,$s:.order.cust.address.street as street, $s:.order.cust.address.city as city, $s:.order.cust.address.state as state, $s:.order.cust.address.zip as zip", "_q": "order" //Reflective Parameter } }
An array of result Objects is returned. The Reflective parameters and Payload Scoped Parameters set in each command are returned with the commands results as well.

SFSQL's ubiquitous JSON over HTTPS API lends itself well as a back-end for many platforms. You can see the "Hello World Shop Admin" deployed on various serverless environments and other Demo Apps created with SFSQL here

[ { "result.oid": "20", "__3DcAM01": "5", "__c1111": "11", "__c2222": "17", "success": 1, "__USB02": "6", "__dell": "10", "cmdname": "modify", "__sony": "4", "__LPN45": "9", "__wristWear03": "7", "__tag": "8" }, { "_q": "supp", "data": [ { "name": "Sony Group Corporation", "id": "sony" }, { "name": "TAG Heuer", "id": "tag" }, { "name": "DELL Inc", "id": "dell" } ], "success": 1, "cmdname": "query" }, { "_q": "prod", "data": [ { "image": "camera.jpg", "code": "3DcAM01", "price": 300.0, "name": "FinePix Pro2 3D Camera", "manname": "Sony Group Corporation", "totordered": 2 }, { "image": "laptop.jpg", "code": "LPN45", "price": 250.0, "name": "XP 1155 Intel Core Laptop", "manname": "DELL Inc", "totordered": 2 }, { "image": "external-hard-drive.jpg", "code": "USB02", "price": 800.0, "name": "EXP Portable Hard Drive", "manname": "Sony Group Corporation", "totordered": 0 }, { "image": "watch.jpg", "code": "wristWear03", "price": 100.0, "name": "Luxury Ultra thin Wrist Watch", "manname": "TAG Heuer", "totordered": 2 } ], "success": 1, "cmdname": "query" }, { "_q": "cust", "data": [ { "zip": "14616", "fname": "Larry", "lname": "Smith", "city": "Rochester", "cusid": "c1111", "street": "5 Elmwood Avenue", "state": "NY", "email": "support@schemafreesql.com" }, { "zip": "10003", "fname": "Sally", "lname": "Swanson", "city": "New York", "cusid": "c2222", "street": "7 Broadway", "state": "NY", "email": "feedback@schemafreesql.com" } ], "success": 1, "cmdname": "query" }, { "_q": "order", "data": [ { "zip": "10003", "image": "camera.jpg", "no": 1, "fname": "Sally", "code": "3DcAM01", "quantity": 1, "orderid": 2, "city": "New York", "manname": "Sony Group Corporation", "lname": "Swanson", "price": 300.0, "cusid": "c2222", "street": "7 Broadway", "name": "FinePix Pro2 3D Camera", "state": "NY", "email": "feedback@schemafreesql.com" }, { "zip": "14616", "image": "camera.jpg", "no": 1, "fname": "Larry", "code": "3DcAM01", "quantity": 1, "orderid": 1, "city": "Rochester", "manname": "Sony Group Corporation", "lname": "Smith", "price": 300.0, "cusid": "c1111", "street": "5 Elmwood Avenue", "name": "FinePix Pro2 3D Camera", "state": "NY", "email": "support@schemafreesql.com" }, { "zip": "14616", "image": "watch.jpg", "no": 2, "fname": "Larry", "code": "wristWear03", "quantity": 2, "orderid": 1, "city": "Rochester", "manname": "TAG Heuer", "lname": "Smith", "price": 100.0, "cusid": "c1111", "street": "5 Elmwood Avenue", "name": "Luxury Ultra thin Wrist Watch", "state": "NY", "email": "support@schemafreesql.com" }, { "zip": "10003", "image": "laptop.jpg", "no": 2, "fname": "Sally", "code": "LPN45", "quantity": 2, "orderid": 2, "city": "New York", "manname": "DELL Inc", "lname": "Swanson", "price": 250.0, "cusid": "c2222", "street": "7 Broadway", "name": "XP 1155 Intel Core Laptop", "state": "NY", "email": "feedback@schemafreesql.com" } ], "success": 1, "cmdname": "query" } ]
Here is the Object attribute structure created by SFSQL from the submitted JSON Object Graph

[ { "data" : [ "o:shops", "o:shops.shop", "o:shops.shop.customer", "o:shops.shop.customer.address", "s:shops.shop.customer.address.city", "s:shops.shop.customer.address.state", "s:shops.shop.customer.address.street", "s:shops.shop.customer.address.zip", "s:shops.shop.customer.email", "s:shops.shop.customer.first_name", "s:shops.shop.customer.id", "s:shops.shop.customer.last_name", "o:shops.shop.order", "o:shops.shop.order.cust", "o:shops.shop.order.cust.address", "s:shops.shop.order.cust.address.city", "s:shops.shop.order.cust.address.state", "s:shops.shop.order.cust.address.street", "s:shops.shop.order.cust.address.zip", "s:shops.shop.order.cust.email", "s:shops.shop.order.cust.first_name", "s:shops.shop.order.cust.id", "s:shops.shop.order.cust.last_name", "o:shops.shop.order.lineitem", "o:shops.shop.order.lineitem.item", "s:shops.shop.order.lineitem.item.code", "s:shops.shop.order.lineitem.item.image", "o:shops.shop.order.lineitem.item.manufacturer", "s:shops.shop.order.lineitem.item.manufacturer.id", "s:shops.shop.order.lineitem.item.manufacturer.name", "s:shops.shop.order.lineitem.item.name", "n:shops.shop.order.lineitem.item.price", "i:shops.shop.order.lineitem.item.totalordered", "i:shops.shop.order.lineitem.no", "n:shops.shop.order.lineitem.price", "i:shops.shop.order.lineitem.qty", "i:shops.shop.order.order_id", "o:shops.shop.product", "s:shops.shop.product.code", "s:shops.shop.product.image", "o:shops.shop.product.manufacturer", "s:shops.shop.product.manufacturer.id", "s:shops.shop.product.manufacturer.name", "s:shops.shop.product.name", "n:shops.shop.product.price", "i:shops.shop.product.totalordered", "o:shops.shop.supplier", "s:shops.shop.supplier.id", "s:shops.shop.supplier.name" ], "success" : 1, "cmdname" : "showattrs" } ]
Here is a Non-Trivial Data Problem SFSQL Solves with Ease
Explanation
JSON Request Payload
JSON Response Payload
Explanation
JSON Request Payload
JSON Response Payload
Storing, Querying and Modifying Disparate Data Structures is difficult to do efficiently.

Example problem:
We want to store products with vastly different attribute sets from one another into the same product database. We want users to be able to query across all available product attributes (e.g. CPU's CPU by speed, Dog Toys Dog Toy by breed size). We will continually be adding new products and types, each with a combination of common attributes (e.g. price) and uncommon attributes (e.g. flavor). We want to efficiently 'reference' certain attributes (e.g. 'Brand') as Objects, because otherwise we'd have duplicate 'Brand' attributes (e.g. Brand 'Support Address', 'Phone', etc.) embedded in-line with their parent attributes across our data store. We want to efficiently 'reference' products as objects in order to include them within queries that reference them (e.g. sales reports by various product attributes) And of course we want queries to run at indexed speeds regardless of what product attributes are searched on.

Did we mention that we want all of this as pain free as possible?
We considered using:

and determined that we'd need to:
  • Create a table per product type
  • Create indexes for each product table
  • Determine which product attributes from each different product table would need to be queried on.
  • Create and manage the indexes on each different product table.
  • Need to use a UNION to pull together query results that span multiple product types / multiple tables.
  • Repeat the above steps every time we added a new product type that introduced new attributes.
  • Create a 'brand' table
  • Need to use JOINs within each product query that included 'brand' info
  • Need to JOIN the product table to whatever main table we are querying if we want product info included.

and like a straight SQL DB, found we would still need to:
  • Create a 'brand' table
  • Use JOINs within each product query that included 'brand' info
  • Need to JOIN the product table to whatever main table we are querying if we want product info included.

Nicely, the JSON column allowed us to
  • Consolidate our product table mix down to a single product table that contained only the common product attributes.
  • Store all the sparse/disparate product attributes within a JSON column on the same, single product table
  • Add new products containing completely new attributes without database changes

Unfortunately, in order to effciently query the product attributes found in the JSON column, we still needed to:
  • Determine which product attributes from each different product type would need to be queried on.
  • Create and manage the indexes for the different product attributes sourced from the JSON column.
  • Repeat the above steps every time we added a new product type that introduced new attributes.

and determined that we'd need to:
  • Create a 'brand' collection
  • Use lookups within each product query that included 'brand' info

Nicely, the document store allowed us to
  • Store all our product attributes whether common or sparse/disparate in a single product collection.
  • Add new products containing completely new attributes without database changes.
  • Performed very fast queries against all our product attributes whether common or sparse/disparate.

Unfortunately, we encountered inefficiencies when querying across collections:
  • When querying the Product collection while using a lookup to the Brands collection, queries slowed down (indexes or not).
  • The above slowdown also applies to queries run against other collections (e.g. sales records) which need to join/filter to the Products collection.
  • We realized that eventually we may want most 'object sections' found in the Products collection to be stored into their own separate collections just as we un-embedded Brands. Especially any 'unbounded' object arrays (e.g. many 'user reviews' on each Product). This brought us to the best-practice suggestions that normalizing all objects out from their source documents and into their own collections is generally an anti-pattern in a document store. Document stores shine brightest when the objects being embedded are limited in array size and don't need to be updated separately from their parent documents (very often).

All fell short.

SchemafreeSQL provides a solution that meets all the needs of example problem.
Click on the "Insert Data" and "Query Data" Tabs to see how SFSQL solves this problem efficiently and effectively.
Demo Apps
(SFSQL Cloud DB Hosted on AWS Aurora Serverless 2 - SFSQL API VM Hosted on Fly.io)
(SFSQL Cloud DB Hosted on AWS Aurora Serverless 2 - SFSQL API VM Hosted on Fly.io)
(SFSQL Cloud DB Hosted on Planetscale - SFSQL API VM Hosted on Fly.io)
(SFSQL Cloud DB Hosted on AWS Aurora Serverless 2 - SFSQL API VM Hosted on Fly.io)
(SFSQL Cloud DB Hosted on AWS Aurora Serverless 2 -
SFSQL API VM Hosted on Fly.io)
WHY SchemafreeSQL ?
"With every new project, why do I have to go through so much repetitive upfront work just to store data?"
"I am tired of designing tables, altering tables, creating indexes, testing index coverage, optimizing indexes, installing a sdk, managing collections, making sure not to query across collections, having to know my query patterns upfront, learning a new query language, having to change the way I think about data, understanding a best practice that's 5 pages long, and running into a "gotcha" around every corner"
"I like SQL and want something that resembles and has the power of SQL and its aggregate functions."
"I want an easy way to get data in and out of a database."
"I'm a programmer and understand about Objects, Attributes, and Arrays and I want to store Objects, Attributes and Arrays. But I don't want my Object Class structures tightly coupled with my data structures (don't want an ORM). I just want to store and edit Object Instance data."

SchemafreeSQL allows easy Modeling, Querying, and Importing of Complex Semi-Structured Data. Object Structures are Created instantly from Unmodified JSON and modified easily with Semi-modified JSON.

  • Flexibility of "NoSQL"
    Enjoy the flexibility of NoSQL's schemaless capabilities yet maintain the Deep Querying Power of SQL Relations with SFSQL - a "Join-free" SQL-like Language..
  • Keep SQL Functions
    Use SQL built-in functions within SFSQL queries. (currently MySQL)
  • HTTP API
    Access SchemafreeSQL from any language through a ubiquitous JSON-over-HTTP API.
Objects References
  • Every Object is uniquely identified with an oid (Object ID)
  • Any existing Object can be "referenced" to exist as an attribute under any other Object
  • No Data duplication (e.g. directly reference an 'item' object from within multiple order lineitems)
  • Multiple references to the same Object are indistinguishable from each other
In the example below, we combine a 'modify' and 2 'query' commands within the same payload.
Within the modify, we first create a customer and a couple of items and then reference those within a newly created order using #ref.
[ { "modify": { "data": { "o:twtest": { "o:customer": { "s:id": "c1111", "s:last_name": "Smith", "o:address": { "s:state": "NY" } }, "o:item": [ { "s:id": "i000", "s:name": "prod 0" }, { "s:id": "i111", "s:name": "prod 1" } ], "o:order": { "s:order_id": "1", "o:customer": { "#ref": "SELECT $o:twtest.customer.oid() WHERE $s:twtest.customer.id='c1111'" }, "o:lineitem": [ { "o:item": { "#ref": "SELECT $o:twtest.item.oid() WHERE $s:twtest.item.id='i000'" }, "i:qty": 12 }, { "o:item": { "#ref": "SELECT $o:twtest.item.oid() WHERE $s:twtest.item.id='i111'" }, "i:qty": 3 } ], "n:total": 99.99 } } }, "_comment": ": modify: create a new order and set customer and items and reference pre-existing customer and items" } }, { "query": { "sfsql": "SELECT $s:twtest.order.customer.address.state as state, TRUNCATE(SUM($n:twtest.order.total),2) as orders_for_state GROUP BY state", "_comment" : "order totals by state" } } , { "query": { "sfsql": "SELECT $s:twtest.order.lineitem.item.id as id, $s:twtest.order.lineitem.item.name as name, TRUNCATE(SUM($i:twtest.order.lineitem.qty),2) as orders_for_state GROUP BY id,name", "_comment" : "totals by item" } } ]
[ { "result.oid" : "97", "_comment" : ": modify: create a new order and set customer and items and reference pre-existing customer and items", "success" : 1, "cmdname" : "modify", "result.anid" : "32" }, { "data" : [ { "orders_for_state" : 99.99, "state" : "NY" } ], "_comment" : "order totals by state", "success" : 1, "cmdname" : "query" }, { "data" : [ { "orders_for_state" : 12, "name" : "prod 0", "id" : "i000" }, { "orders_for_state" : 3, "name" : "prod 1", "id" : "i111" } ], "_comment" : "totals by item", "success" : 1, "cmdname" : "query" } ]
A Schemaless Object Store?
  • Object Structures Created from Unmodified JSON
  • Store Sparse Data Efficiently
  • Dynamically Modify Object Structures
  • No Tables to Create
  • Auto Index Creation
  • Join-less Queries
In this example Schemaless "Contact" Objects are created, modified, and queried.
[ { "modify": { "data": { "ContactList": { "Contact": [ { "fname": "John", "lname": "Smith", "age": 29 }, { "fname": "Sue", "lname": "Smith", "age": 27, "MI": "P", "d:birthdate": "02/15/1994" } ] } }, "_comment": "Create Contacts with different Attributes" } }, { "modify": { "data": { "ContactList": { "Contact": { "#update": { "where": "$s:Contact.fname='John'" }, "MI": "D", "d:birthdate": "01/15/1992", "Address": { "street": "123 Washington Street", "city": "Boston", "state": "MA", "zip": "02111" } } } }, "_comment": "Modify Object Structure" } } ]
[ { "query": { "sfsql": "SELECT $s:.Contact.fname, $s:.Contact.lname, $i:.Contact.age, $d:.Contact.birthdate,$$s:.Contact.Address.street", "_comment": "Query Contacts, Indexes auto created" } } ]
[ { "data": [ { "s:Contact.lname": "Smith", "i:Contact.age": 29, "d:Contact.birthdate": "1992-01-15 00:00:00", "s:Contact.Address.street": "123 Washington Street", "s:Contact.fname": "John" }, { "s:Contact.lname": "Smith", "i:Contact.age": 27, "d:Contact.birthdate": "1994-02-15 00:00:00", "s:Contact.Address.street": null, "s:Contact.fname": "Sue" } ], "_comment": "Query Contacts, Indexes auto created", "success": 1, "cmdname": "query" } ]
Handle Complex Data
Instantly store and query complex data. No tables or indexes to create.

Example of the deep hierarchically data that SchemafreeSQL can store and query. (source: clinicaltrials.gov)
[ { "modify": { "data": { "FullStudy": { "Rank": 699, "Study": { "ProtocolSection": { "IdentificationModule": { "NCTId": "NCT04970017", "OrgStudyIdInfo": { "OrgStudyId": "17200538" }, "Organization": { "OrgFullName": "Assiut University", "OrgClass": "OTHER" }, "BriefTitle": "Correlation Between Left Ventricular Global Strain Measured by Speckle Tracking Echocardiography and Scar Burden Measured by Cardiac Magnetic Resonance Imaging in Patients With Ischemic Heart Disease", "OfficialTitle": "Correlation Between Left Ventricular Global Strain Measured by Speckle Tracking Echocardiography and Scar Burden Measured by Cardiac Magnetic Resonance Imaging in Patients With Ischemic Heart Disease" }, "StatusModule": { "StatusVerifiedDate": "February 2021", "OverallStatus": "Recruiting", "ExpandedAccessInfo": { "HasExpandedAccess": "No" }, "StartDateStruct": { "StartDate": "January 1, 2021", "StartDateType": "Actual" }, "PrimaryCompletionDateStruct": { "PrimaryCompletionDate": "January 2022", "PrimaryCompletionDateType": "Anticipated" }, "CompletionDateStruct": { "CompletionDate": "January 2022", "CompletionDateType": "Anticipated" }, "StudyFirstSubmitDate": "July 16, 2021", "StudyFirstSubmitQCDate": "July 16, 2021", "StudyFirstPostDateStruct": { "StudyFirstPostDate": "July 21, 2021", "StudyFirstPostDateType": "Actual" }, "LastUpdateSubmitDate": "July 16, 2021", "LastUpdatePostDateStruct": { "LastUpdatePostDate": "July 21, 2021", "LastUpdatePostDateType": "Actual" } }, "SponsorCollaboratorsModule": { "ResponsibleParty": { "ResponsiblePartyType": "Principal Investigator", "ResponsiblePartyInvestigatorFullName": "Mohamed Mahmoud Mohamed Abdellatif", "ResponsiblePartyInvestigatorTitle": "Dr", "ResponsiblePartyInvestigatorAffiliation": "Assiut University" }, "LeadSponsor": { "LeadSponsorName": "Assiut University", "LeadSponsorClass": "OTHER" } }, "OversightModule": { "IsFDARegulatedDrug": "No", "IsFDARegulatedDevice": "No" }, "DescriptionModule": { "BriefSummary": "To verify whether GLS and LV mechanical dispersion, measured by two-dimensional speckle-tracking echocardiography (2D-STE) correlate with LV scar burden measured by cardiac MRI in patients with ischemic heart disease.", "DetailedDescription": "Evaluation of presence, localization, and extent of left ventricular(LV) scar tissue in patients with ischaemic heart disease (IHD) is of fundamental importance in clinical practice. It affects the decision making regarding revascularization and is a determinant of subsequent mortality. (Mele, Fiorencis et al. 2016) Late gadolinium contrast-enhanced cardiac magnetic resonance (LGE-CMR) is considered the current gold standard technique for assessment of the scar burden in IHD patients. However, it is not readily accessible in many areas due to availability and cost issues.(Abou, Prihadi et al. 2020), (Bendary, Afifi et al. 2019) Several recent studies suggested 2-D speckle tracking strain as a potential surrogate for cardiovascular magnetic resonance (CMR) late gadolinium enhancement (LGE) imaging. This would be useful in cases where CMR is not available, gadolinium contrast is contraindicated, or in patients at greater risk of adverse long term events. (Erley, Genovese et al. 2019) Among echocardiography derived strain measurements, global longitudinal strain (GLS) was shown to be superior to global circumferential strain (GCS) in its ability to detect subtle myocardial abnormalities due to better reproducibility (Erley et al, 2019). Left ventricular mechanical dispersion (LVMD) is also considered a valuable parameter that was associated with outcomes after myocardial infarction. (Abou, Prihadi et al. 2020).\n\nDespite the growing number of strain related studies in the literature, it is not clear whether the relationship of strain measurements with LGE is strong enough for strain to be considered as a surrogate. (Erley, Genovese et al. 2019). Also, the differences between GLS and LV MD among strain components in this context are not well established." }, "ConditionsModule": { "ConditionList": { "Condition": [ "Ischemic Heart Disease" ] } }, "DesignModule": { "StudyType": "Observational", "PatientRegistry": "No", "DesignInfo": { "DesignObservationalModelList": { "DesignObservationalModel": [ "Case-Only" ] }, "DesignTimePerspectiveList": { "DesignTimePerspective": [ "Cross-Sectional" ] } }, "EnrollmentInfo": { "EnrollmentCount": "51", "EnrollmentType": "Anticipated" } }, "ArmsInterventionsModule": { "InterventionList": { "Intervention": [ { "InterventionType": "Radiation", "InterventionName": "Cardiac MRI, Echocardiography", "InterventionDescription": "Magnetic resonance imaging, Echocardiography" } ] } }, "OutcomesModule": { "PrimaryOutcomeList": { "PrimaryOutcome": [ { "PrimaryOutcomeMeasure": "Scare burden by echocardiography", "PrimaryOutcomeDescription": "To test the correlation between the scar burden measured by cardiac MRI and GLS measured by 2D STE", "PrimaryOutcomeTimeFrame": "One year" } ] }, "SecondaryOutcomeList": { "SecondaryOutcome": [ { "SecondaryOutcomeMeasure": "Value of spickle tracking echocardiography in clinical practice", "SecondaryOutcomeDescription": "Predicting the scar burden in ICMP patients by GLS and mechanical dispersion.", "SecondaryOutcomeTimeFrame": "One year" } ] } }, "EligibilityModule": { "EligibilityCriteria": "Inclusion Criteria:\n\n• Patients presenting for viability assessment in to cardiac MRI unit, Assiut university heart hospital, with a history of previous stemi, at least 3 months after the acute event ( scar stabilization) and up to one year\n\nExclusion Criteria:\n\nPatients with:\n\nContraindication to cardiac MRI (claustrophobia, Patients with eGFR below 30 mL/min/1.73 m2. and patients with metallic implants)\nNon-ischemic cardiomyopathy.\nValvular heart disease (VHD).", "Gender": "All", "StdAgeList": { "StdAge": [ "Child", "Adult", "Older Adult" ] }, "StudyPopulation": "Patients presenting for viability assessment in to cardiac MRI unit, Assiut university heart hospital, with a history of previous stemi, at least 3 months after the acute event ( scar stabilization) and up to one year", "SamplingMethod": "Non-Probability Sample" }, "ContactsLocationsModule": { "CentralContactList": { "CentralContact": [ { "CentralContactName": "Mohamed Abdellatif, MSC", "CentralContactRole": "Contact", "CentralContactPhone": "+201001073747", "CentralContactEMail": "mohamed.abdellatief@med.au.edu.eg" }, { "CentralContactName": "Shimaa Sayed Khidr, PHD", "CentralContactRole": "Contact", "CentralContactPhone": "+201001346551", "CentralContactEMail": "S.khidr@aun.edu.eg" } ] }, "OverallOfficialList": { "OverallOfficial": [ { "OverallOfficialName": "Mohamed Abdellatif", "OverallOfficialAffiliation": "Assiut University", "OverallOfficialRole": "Principal Investigator" } ] }, "LocationList": { "Location": [ { "LocationFacility": "Assiut university", "LocationStatus": "Recruiting", "LocationCity": "Assiut", "LocationZip": "71511", "LocationCountry": "Egypt", "LocationContactList": { "LocationContact": [ { "LocationContactName": "Mohamed Abdellatif", "LocationContactRole": "Contact", "LocationContactPhone": "00201001073747", "LocationContactEMail": "Mohamed.abdellatief@med.au.edu.eg" } ] } } ] } } }, "DerivedSection": { "MiscInfoModule": { "VersionHolder": "July 27, 2021" }, "ConditionBrowseModule": { "ConditionMeshList": { "ConditionMesh": [ { "ConditionMeshId": "D000006331", "ConditionMeshTerm": "Heart Diseases" }, { "ConditionMeshId": "D000017202", "ConditionMeshTerm": "Myocardial Ischemia" }, { "ConditionMeshId": "D000003324", "ConditionMeshTerm": "Coronary Artery Disease" }, { "ConditionMeshId": "D000007511", "ConditionMeshTerm": "Ischemia" } ] }, "ConditionAncestorList": { "ConditionAncestor": [ { "ConditionAncestorId": "D000010335", "ConditionAncestorTerm": "Pathologic Processes" }, { "ConditionAncestorId": "D000002318", "ConditionAncestorTerm": "Cardiovascular Diseases" }, { "ConditionAncestorId": "D000014652", "ConditionAncestorTerm": "Vascular Diseases" }, { "ConditionAncestorId": "D000003327", "ConditionAncestorTerm": "Coronary Disease" }, { "ConditionAncestorId": "D000001161", "ConditionAncestorTerm": "Arteriosclerosis" }, { "ConditionAncestorId": "D000001157", "ConditionAncestorTerm": "Arterial Occlusive Diseases" } ] }, "ConditionBrowseLeafList": { "ConditionBrowseLeaf": [ { "ConditionBrowseLeafId": "M9126", "ConditionBrowseLeafName": "Ischemia", "ConditionBrowseLeafAsFound": "Ischemic", "ConditionBrowseLeafRelevance": "high" }, { "ConditionBrowseLeafId": "M8002", "ConditionBrowseLeafName": "Heart Diseases", "ConditionBrowseLeafAsFound": "Heart Disease", "ConditionBrowseLeafRelevance": "high" }, { "ConditionBrowseLeafId": "M18089", "ConditionBrowseLeafName": "Myocardial Ischemia", "ConditionBrowseLeafAsFound": "Ischemic Heart Disease", "ConditionBrowseLeafRelevance": "high" }, { "ConditionBrowseLeafId": "M5129", "ConditionBrowseLeafName": "Coronary Artery Disease", "ConditionBrowseLeafAsFound": "Ischemic Heart Disease", "ConditionBrowseLeafRelevance": "high" }, { "ConditionBrowseLeafId": "M4743", "ConditionBrowseLeafName": "Cicatrix", "ConditionBrowseLeafRelevance": "low" }, { "ConditionBrowseLeafId": "M5132", "ConditionBrowseLeafName": "Coronary Disease", "ConditionBrowseLeafRelevance": "low" }, { "ConditionBrowseLeafId": "M15983", "ConditionBrowseLeafName": "Vascular Diseases", "ConditionBrowseLeafRelevance": "low" }, { "ConditionBrowseLeafId": "M3050", "ConditionBrowseLeafName": "Arteriosclerosis", "ConditionBrowseLeafRelevance": "low" }, { "ConditionBrowseLeafId": "M3046", "ConditionBrowseLeafName": "Arterial Occlusive Diseases", "ConditionBrowseLeafRelevance": "low" } ] }, "ConditionBrowseBranchList": { "ConditionBrowseBranch": [ { "ConditionBrowseBranchAbbrev": "BC23", "ConditionBrowseBranchName": "Symptoms and General Pathology" }, { "ConditionBrowseBranchAbbrev": "All", "ConditionBrowseBranchName": "All Conditions" }, { "ConditionBrowseBranchAbbrev": "BC14", "ConditionBrowseBranchName": "Heart and Blood Diseases" } ] } } } } } } } } ]
[ { "query": { "sfsql": "SELECT $s:.BriefTitle LIMIT 10", "_comment": "BriefTitle query" } }, { "query": { "sfsql": "SELECT DISTINCT $s:.LeadSponsor.LeadSponsorName AS 'LeadSponsorName', $s:.LeadSponsor.LeadSponsorClass AS 'LeadSponsorClass' WHERE $s:.LeadSponsor.LeadSponsorName.vci() LIKE 'University%' ORDER BY LeadSponsorClass,LeadSponsorName LIMIT 10", "data-style": "nvp", "data-format": "json", "_comment": "LeadSponsor query" } }, { "query": { "sfsql": "SELECT $i:.FullStudy.Rank as 'Rank', $s:.FullStudy.Study.ProtocolSection.IdentificationModule.NCTId as 'NCTId' LIMIT 10", "data-format": "csv", "data-style": "colnames", "_comment": "Rank query" } }, { "query": { "sfsql": "SELECT $s:.FullStudy.Study.ProtocolSection.ArmsInterventionsModule.InterventionList.Intervention.InterventionType as 'InterventionType', COUNT($s:.FullStudy.Study.ProtocolSection.IdentificationModule.NCTId) as 'cnt' GROUP BY InterventionType ORDER BY cnt DESC", "data-format": "json", "data-style": "dataonly", "_comment": "Count by InterventionTypes" } }, { "showattrs": { "rootattr":"FullStudy" }, "_comment": "show complete clinical trials attribute structure." } ]
[ { "data": [ { "s:BriefTitle": "Comparative Effectiveness Analysis of Granulocyte Colony Stimulating Factor Originator Products Versus Biosimilars" }, { "s:BriefTitle": "The Influence of Simple, Low-Cost Chemistry Intervention Videos: A Randomized Trial of Children's Preferences for Sugar-Sweetened Beverages" }, { "s:BriefTitle": "Access Anti-HCV Assay European Union (EU) Clinical Trial Protocol" }, { "s:BriefTitle": "ACCESS HIV ANTIGEN/ANTIBODY COMBO ASSAY EUROPEAN UNION (EU) CLINICAL TRIAL PROTOCOL" }, { "s:BriefTitle": "1-month DAPT Plus 5-month Ticagrelor Monotherapy Versus 12-month DAPT in Patients With Drug-coated Balloon" }, { "s:BriefTitle": "Safety Study of 89Zr-NY001 PET Imaging in Patients" }, { "s:BriefTitle": "Alveolar Ridge Augmentation With Curcumin Combined With Xenograft" }, { "s:BriefTitle": "Safety, Tolerability, and Pharmacokinetics of a Single Intravenous Infusion of XTMAB-16 in Healthy Adult Participants" }, { "s:BriefTitle": "Impact of Passive Heat on Metabolic, Inflammatory and Vascular Health in Persons With Spinal Cord Injury" }, { "s:BriefTitle": "Liquid Biopsies for Improving the Pre-operative Diagnosis of Ovarian Cancer" } ], "success": 1, "cmdname": "query" }, { "data": [ { "LeadSponsorClass": "OTHER", "LeadSponsorName": "University Health Network, Toronto" }, { "LeadSponsorClass": "OTHER", "LeadSponsorName": "University of Alabama at Birmingham" }, { "LeadSponsorClass": "OTHER", "LeadSponsorName": "University of Alberta" }, { "LeadSponsorClass": "OTHER", "LeadSponsorName": "University of California, Irvine" }, { "LeadSponsorClass": "OTHER", "LeadSponsorName": "University of California, San Francisco" }, { "LeadSponsorClass": "OTHER", "LeadSponsorName": "University of Nebraska" }, { "LeadSponsorClass": "OTHER", "LeadSponsorName": "University of North Carolina, Chapel Hill" }, { "LeadSponsorClass": "OTHER", "LeadSponsorName": "University of Pennsylvania" }, { "LeadSponsorClass": "OTHER", "LeadSponsorName": "University of South Carolina" }, { "LeadSponsorClass": "OTHER", "LeadSponsorName": "University of Wisconsin, Madison" } ], "_comment": "LeadSponsor query", "success": 1, "data-style": "nvp", "cmdname": "query", "data-format": "json" }, { "data": "Rank,NCTId\r\n600,NCT04971304\r\n599,NCT04971317\r\n598,NCT04971330\r\n597,NCT04971343\r\n596,NCT04971356\r\n595,NCT04971369\r\n594,NCT04971382\r\n593,NCT04971395\r\n592,NCT04971408\r\n591,NCT04971421", "_comment": "rank query", "success": 1, "data-style": "colnames", "cmdname": "query", "data-format": "csv" }, { "data": [ [ "Drug", 61 ], [ "Other", 35 ], [ "Device", 19 ], [ "Procedure", 10 ], [ "Behavioral", 9 ], [ "Diagnostic Test", 9 ], [ "Dietary Supplement", 8 ], [ "Combination Product", 3 ], [ "Biological", 2 ] ], "_comment": "count by InterventionTypes", "success": 1, "data-style": "dataonly", "cmdname": "query", "data-format": "json" }, [ { "data": [ "o:FullStudy", "i:FullStudy.Rank", "o:FullStudy.Study", "o:FullStudy.Study.DerivedSection", "o:FullStudy.Study.DerivedSection.ConditionBrowseModule", "o:FullStudy.Study.DerivedSection.ConditionBrowseModule.ConditionAncestorList", "o:FullStudy.Study.DerivedSection.ConditionBrowseModule.ConditionAncestorList.ConditionAncestor", "s:FullStudy.Study.DerivedSection.ConditionBrowseModule.ConditionAncestorList.ConditionAncestor.ConditionAncestorId", "s:FullStudy.Study.DerivedSection.ConditionBrowseModule.ConditionAncestorList.ConditionAncestor.ConditionAncestorTerm", "o:FullStudy.Study.DerivedSection.ConditionBrowseModule.ConditionBrowseBranchList", "o:FullStudy.Study.DerivedSection.ConditionBrowseModule.ConditionBrowseBranchList.ConditionBrowseBranch", "s:FullStudy.Study.DerivedSection.ConditionBrowseModule.ConditionBrowseBranchList.ConditionBrowseBranch.ConditionBrowseBranchAbbrev", "s:FullStudy.Study.DerivedSection.ConditionBrowseModule.ConditionBrowseBranchList.ConditionBrowseBranch.ConditionBrowseBranchName", "o:FullStudy.Study.DerivedSection.ConditionBrowseModule.ConditionBrowseLeafList", "o:FullStudy.Study.DerivedSection.ConditionBrowseModule.ConditionBrowseLeafList.ConditionBrowseLeaf", "s:FullStudy.Study.DerivedSection.ConditionBrowseModule.ConditionBrowseLeafList.ConditionBrowseLeaf.ConditionBrowseLeafAsFound", "s:FullStudy.Study.DerivedSection.ConditionBrowseModule.ConditionBrowseLeafList.ConditionBrowseLeaf.ConditionBrowseLeafId", "s:FullStudy.Study.DerivedSection.ConditionBrowseModule.ConditionBrowseLeafList.ConditionBrowseLeaf.ConditionBrowseLeafName", "s:FullStudy.Study.DerivedSection.ConditionBrowseModule.ConditionBrowseLeafList.ConditionBrowseLeaf.ConditionBrowseLeafRelevance", "o:FullStudy.Study.DerivedSection.ConditionBrowseModule.ConditionMeshList", "o:FullStudy.Study.DerivedSection.ConditionBrowseModule.ConditionMeshList.ConditionMesh", "s:FullStudy.Study.DerivedSection.ConditionBrowseModule.ConditionMeshList.ConditionMesh.ConditionMeshId", "s:FullStudy.Study.DerivedSection.ConditionBrowseModule.ConditionMeshList.ConditionMesh.ConditionMeshTerm", "o:FullStudy.Study.DerivedSection.MiscInfoModule", "s:FullStudy.Study.DerivedSection.MiscInfoModule.VersionHolder", "o:FullStudy.Study.ProtocolSection", "o:FullStudy.Study.ProtocolSection.ArmsInterventionsModule", "o:FullStudy.Study.ProtocolSection.ArmsInterventionsModule.ArmGroupList", "o:FullStudy.Study.ProtocolSection.ArmsInterventionsModule.ArmGroupList.ArmGroup", "o:FullStudy.Study.ProtocolSection.ArmsInterventionsModule.ArmGroupList.ArmGroup.ArmGroupInterventionList", "s:FullStudy.Study.ProtocolSection.ArmsInterventionsModule.ArmGroupList.ArmGroup.ArmGroupInterventionList.ArmGroupInterventionName", "s:FullStudy.Study.ProtocolSection.ArmsInterventionsModule.ArmGroupList.ArmGroup.ArmGroupLabel", "o:FullStudy.Study.ProtocolSection.ArmsInterventionsModule.InterventionList", "o:FullStudy.Study.ProtocolSection.ArmsInterventionsModule.InterventionList.Intervention", "o:FullStudy.Study.ProtocolSection.ArmsInterventionsModule.InterventionList.Intervention.InterventionArmGroupLabelList", "s:FullStudy.Study.ProtocolSection.ArmsInterventionsModule.InterventionList.Intervention.InterventionArmGroupLabelList.InterventionArmGroupLabel", "s:FullStudy.Study.ProtocolSection.ArmsInterventionsModule.InterventionList.Intervention.InterventionDescription", "s:FullStudy.Study.ProtocolSection.ArmsInterventionsModule.InterventionList.Intervention.InterventionName", "s:FullStudy.Study.ProtocolSection.ArmsInterventionsModule.InterventionList.Intervention.InterventionType", "o:FullStudy.Study.ProtocolSection.ConditionsModule", "o:FullStudy.Study.ProtocolSection.ConditionsModule.ConditionList", "s:FullStudy.Study.ProtocolSection.ConditionsModule.ConditionList.Condition", "o:FullStudy.Study.ProtocolSection.ConditionsModule.KeywordList", "s:FullStudy.Study.ProtocolSection.ConditionsModule.KeywordList.Keyword", "o:FullStudy.Study.ProtocolSection.ContactsLocationsModule", "o:FullStudy.Study.ProtocolSection.ContactsLocationsModule.CentralContactList", "o:FullStudy.Study.ProtocolSection.ContactsLocationsModule.CentralContactList.CentralContact", "s:FullStudy.Study.ProtocolSection.ContactsLocationsModule.CentralContactList.CentralContact.CentralContactEMail", "s:FullStudy.Study.ProtocolSection.ContactsLocationsModule.CentralContactList.CentralContact.CentralContactName", "s:FullStudy.Study.ProtocolSection.ContactsLocationsModule.CentralContactList.CentralContact.CentralContactPhone", "s:FullStudy.Study.ProtocolSection.ContactsLocationsModule.CentralContactList.CentralContact.CentralContactRole", "o:FullStudy.Study.ProtocolSection.ContactsLocationsModule.LocationList", "o:FullStudy.Study.ProtocolSection.ContactsLocationsModule.LocationList.Location", "s:FullStudy.Study.ProtocolSection.ContactsLocationsModule.LocationList.Location.LocationCity", "s:FullStudy.Study.ProtocolSection.ContactsLocationsModule.LocationList.Location.LocationCountry", "s:FullStudy.Study.ProtocolSection.ContactsLocationsModule.LocationList.Location.LocationFacility", "s:FullStudy.Study.ProtocolSection.ContactsLocationsModule.LocationList.Location.LocationState", "s:FullStudy.Study.ProtocolSection.ContactsLocationsModule.LocationList.Location.LocationStatus", "s:FullStudy.Study.ProtocolSection.ContactsLocationsModule.LocationList.Location.LocationZip", "o:FullStudy.Study.ProtocolSection.DescriptionModule", "s:FullStudy.Study.ProtocolSection.DescriptionModule.BriefSummary", "o:FullStudy.Study.ProtocolSection.DesignModule", "o:FullStudy.Study.ProtocolSection.DesignModule.DesignInfo", "o:FullStudy.Study.ProtocolSection.DesignModule.DesignInfo.DesignObservationalModelList", "s:FullStudy.Study.ProtocolSection.DesignModule.DesignInfo.DesignObservationalModelList.DesignObservationalModel", "o:FullStudy.Study.ProtocolSection.DesignModule.DesignInfo.DesignTimePerspectiveList", "s:FullStudy.Study.ProtocolSection.DesignModule.DesignInfo.DesignTimePerspectiveList.DesignTimePerspective", "o:FullStudy.Study.ProtocolSection.DesignModule.EnrollmentInfo", "s:FullStudy.Study.ProtocolSection.DesignModule.EnrollmentInfo.EnrollmentCount", "s:FullStudy.Study.ProtocolSection.DesignModule.EnrollmentInfo.EnrollmentType", "s:FullStudy.Study.ProtocolSection.DesignModule.PatientRegistry", "s:FullStudy.Study.ProtocolSection.DesignModule.StudyType", "o:FullStudy.Study.ProtocolSection.EligibilityModule", "s:FullStudy.Study.ProtocolSection.EligibilityModule.EligibilityCriteria", "s:FullStudy.Study.ProtocolSection.EligibilityModule.Gender", "s:FullStudy.Study.ProtocolSection.EligibilityModule.HealthyVolunteers", "s:FullStudy.Study.ProtocolSection.EligibilityModule.MinimumAge", "s:FullStudy.Study.ProtocolSection.EligibilityModule.SamplingMethod", "o:FullStudy.Study.ProtocolSection.EligibilityModule.StdAgeList", "s:FullStudy.Study.ProtocolSection.EligibilityModule.StdAgeList.StdAge", "s:FullStudy.Study.ProtocolSection.EligibilityModule.StudyPopulation", "o:FullStudy.Study.ProtocolSection.IdentificationModule", "s:FullStudy.Study.ProtocolSection.IdentificationModule.BriefTitle", "s:FullStudy.Study.ProtocolSection.IdentificationModule.NCTId", "s:FullStudy.Study.ProtocolSection.IdentificationModule.OfficialTitle", "o:FullStudy.Study.ProtocolSection.IdentificationModule.Organization", "s:FullStudy.Study.ProtocolSection.IdentificationModule.Organization.OrgClass", "s:FullStudy.Study.ProtocolSection.IdentificationModule.Organization.OrgFullName", "o:FullStudy.Study.ProtocolSection.IdentificationModule.OrgStudyIdInfo", "s:FullStudy.Study.ProtocolSection.IdentificationModule.OrgStudyIdInfo.OrgStudyId", "o:FullStudy.Study.ProtocolSection.IPDSharingStatementModule", "s:FullStudy.Study.ProtocolSection.IPDSharingStatementModule.IPDSharing", "o:FullStudy.Study.ProtocolSection.OutcomesModule", "o:FullStudy.Study.ProtocolSection.OutcomesModule.PrimaryOutcomeList", "o:FullStudy.Study.ProtocolSection.OutcomesModule.PrimaryOutcomeList.PrimaryOutcome", "s:FullStudy.Study.ProtocolSection.OutcomesModule.PrimaryOutcomeList.PrimaryOutcome.PrimaryOutcomeDescription", "s:FullStudy.Study.ProtocolSection.OutcomesModule.PrimaryOutcomeList.PrimaryOutcome.PrimaryOutcomeMeasure", "s:FullStudy.Study.ProtocolSection.OutcomesModule.PrimaryOutcomeList.PrimaryOutcome.PrimaryOutcomeTimeFrame", "o:FullStudy.Study.ProtocolSection.OutcomesModule.SecondaryOutcomeList", "o:FullStudy.Study.ProtocolSection.OutcomesModule.SecondaryOutcomeList.SecondaryOutcome", "s:FullStudy.Study.ProtocolSection.OutcomesModule.SecondaryOutcomeList.SecondaryOutcome.SecondaryOutcomeDescription", "s:FullStudy.Study.ProtocolSection.OutcomesModule.SecondaryOutcomeList.SecondaryOutcome.SecondaryOutcomeMeasure", "s:FullStudy.Study.ProtocolSection.OutcomesModule.SecondaryOutcomeList.SecondaryOutcome.SecondaryOutcomeTimeFrame", "o:FullStudy.Study.ProtocolSection.OversightModule", "s:FullStudy.Study.ProtocolSection.OversightModule.IsFDARegulatedDevice", "s:FullStudy.Study.ProtocolSection.OversightModule.IsFDARegulatedDrug", "s:FullStudy.Study.ProtocolSection.OversightModule.OversightHasDMC", "o:FullStudy.Study.ProtocolSection.SponsorCollaboratorsModule", "o:FullStudy.Study.ProtocolSection.SponsorCollaboratorsModule.LeadSponsor", "s:FullStudy.Study.ProtocolSection.SponsorCollaboratorsModule.LeadSponsor.LeadSponsorClass", "s:FullStudy.Study.ProtocolSection.SponsorCollaboratorsModule.LeadSponsor.LeadSponsorName", "o:FullStudy.Study.ProtocolSection.SponsorCollaboratorsModule.ResponsibleParty", "s:FullStudy.Study.ProtocolSection.SponsorCollaboratorsModule.ResponsibleParty.ResponsiblePartyType", "o:FullStudy.Study.ProtocolSection.StatusModule", "o:FullStudy.Study.ProtocolSection.StatusModule.CompletionDateStruct", "s:FullStudy.Study.ProtocolSection.StatusModule.CompletionDateStruct.CompletionDate", "s:FullStudy.Study.ProtocolSection.StatusModule.CompletionDateStruct.CompletionDateType", "o:FullStudy.Study.ProtocolSection.StatusModule.ExpandedAccessInfo", "s:FullStudy.Study.ProtocolSection.StatusModule.ExpandedAccessInfo.HasExpandedAccess", "o:FullStudy.Study.ProtocolSection.StatusModule.LastUpdatePostDateStruct", "s:FullStudy.Study.ProtocolSection.StatusModule.LastUpdatePostDateStruct.LastUpdatePostDate", "s:FullStudy.Study.ProtocolSection.StatusModule.LastUpdatePostDateStruct.LastUpdatePostDateType", "s:FullStudy.Study.ProtocolSection.StatusModule.LastUpdateSubmitDate", "s:FullStudy.Study.ProtocolSection.StatusModule.OverallStatus", "o:FullStudy.Study.ProtocolSection.StatusModule.PrimaryCompletionDateStruct", "s:FullStudy.Study.ProtocolSection.StatusModule.PrimaryCompletionDateStruct.PrimaryCompletionDate", "s:FullStudy.Study.ProtocolSection.StatusModule.PrimaryCompletionDateStruct.PrimaryCompletionDateType", "o:FullStudy.Study.ProtocolSection.StatusModule.StartDateStruct", "s:FullStudy.Study.ProtocolSection.StatusModule.StartDateStruct.StartDate", "s:FullStudy.Study.ProtocolSection.StatusModule.StartDateStruct.StartDateType", "s:FullStudy.Study.ProtocolSection.StatusModule.StatusVerifiedDate", "o:FullStudy.Study.ProtocolSection.StatusModule.StudyFirstPostDateStruct", "s:FullStudy.Study.ProtocolSection.StatusModule.StudyFirstPostDateStruct.StudyFirstPostDate", "s:FullStudy.Study.ProtocolSection.StatusModule.StudyFirstPostDateStruct.StudyFirstPostDateType", "s:FullStudy.Study.ProtocolSection.StatusModule.StudyFirstSubmitDate", "s:FullStudy.Study.ProtocolSection.StatusModule.StudyFirstSubmitQCDate" ], "_comment": "show complete clinical trials attribute structure.", "success": 1, "cmdname": "showattrs" } ] ]
Maintain Full use of SQL Functions (currently MySQL)
  • Aggregate Functions
  • Date and Time Functions
  • Numeric Functions and Operators
  • String Functions and Operators
  • Cast Functions and Operators
  • Bit Functions and Operators
  • Encryption and Compression Functions
Example: Create a simple set of 2 Person objects with name and age.
Perform an aggregate query returning the CAST of the AVG of the CHAR_LENGTH of their names, the MAX of the their ages, and the COUNT of People.
[ { "modify": { "data": { "Directory": { "Person": [ { "name": "Sue", "age": 30 }, { "name": "Juniper", "age": 55 } ] } } } } ]
[ { "query": { "sfsql": "SELECT CAST( AVG( CHAR_LENGTH( $s:Directory.Person.name)) AS DECIMAL(5,2)) as 'average_name_length', MAX($i:Directory.Person.age) as 'max_age', COUNT(*) as 'person_count'" } } ]
[ { "data": [ { "max_age": 55, "average_name_length": 5.0, "person_count": 2 } ], "success": 1, "cmdname": "query" } ]
Import Datasets in Batch Mode (This currently requires SFSQL container to be self-hosted. Remote import and self-hosted SFSQL containers will be available soon )
  • Use for large datasets (e.g. PubMed, clinicaltrials.gov, etc)
  • Import raw JSON files without having to perform pre-processing on the files
  • Import from nested directories of JSON files using wildcard matching without having to write import scripts
  • Implemented through the modify command's "#include" subcommand
  • Auto-resume batch from last position processed
  • See the #include Sub-Command for details
Clinical Trial JSON files were downloaded from clinicaltrials.gov. Path to the downloaded JSON files is /var/downloads/samplect/. The SFSQL payload is using the #include subcommand to process the first batch of 100 downloaded source JSON files from this directory.
{ "FullStudy":{ "Rank":699, "Study":{ "ProtocolSection":{ "IdentificationModule":{ "NCTId":"NCT04970017", "OrgStudyIdInfo":{ "OrgStudyId":"17200538" }, "Organization":{ "OrgFullName":"Assiut University", "OrgClass":"OTHER" }, "BriefTitle":"Correlation Between Left Ventricular Global Strain Measured by Speckle Tracking Echocardiography and Scar Burden Measured by Cardiac Magnetic Resonance Imaging in Patients With Ischemic Heart Disease", "OfficialTitle":"Correlation Between Left Ventricular Global Strain Measured by Speckle Tracking Echocardiography and Scar Burden Measured by Cardiac Magnetic Resonance Imaging in Patients With Ischemic Heart Disease" }, "StatusModule":{ "StatusVerifiedDate":"February 2021", "OverallStatus":"Recruiting", "ExpandedAccessInfo":{ "HasExpandedAccess":"No" }, "StartDateStruct":{ "StartDate":"January 1, 2021", "StartDateType":"Actual" }, "PrimaryCompletionDateStruct":{ "PrimaryCompletionDate":"January 2022", "PrimaryCompletionDateType":"Anticipated" }, "CompletionDateStruct":{ "CompletionDate":"January 2022", "CompletionDateType":"Anticipated" }, "StudyFirstSubmitDate":"July 16, 2021", "StudyFirstSubmitQCDate":"July 16, 2021", "StudyFirstPostDateStruct":{ "StudyFirstPostDate":"July 21, 2021", "StudyFirstPostDateType":"Actual" }, "LastUpdateSubmitDate":"July 16, 2021", "LastUpdatePostDateStruct":{ "LastUpdatePostDate":"July 21, 2021", "LastUpdatePostDateType":"Actual" } }, "SponsorCollaboratorsModule":{ "ResponsibleParty":{ "ResponsiblePartyType":"Principal Investigator", "ResponsiblePartyInvestigatorFullName":"Mohamed Mahmoud Mohamed Abdellatif", "ResponsiblePartyInvestigatorTitle":"Dr", "ResponsiblePartyInvestigatorAffiliation":"Assiut University" }, "LeadSponsor":{ "LeadSponsorName":"Assiut University", "LeadSponsorClass":"OTHER" } }, "OversightModule":{ "IsFDARegulatedDrug":"No", "IsFDARegulatedDevice":"No" }, "DescriptionModule":{ "BriefSummary":"To verify whether GLS and LV mechanical dispersion, measured by two-dimensional speckle-tracking echocardiography (2D-STE) correlate with LV scar burden measured by cardiac MRI in patients with ischemic heart disease.", "DetailedDescription":"Evaluation of presence, localization, and extent of left ventricular(LV) scar tissue in patients with ischaemic heart disease (IHD) is of fundamental importance in clinical practice. It affects the decision making regarding revascularization and is a determinant of subsequent mortality. (Mele, Fiorencis et al. 2016) Late gadolinium contrast-enhanced cardiac magnetic resonance (LGE-CMR) is considered the current gold standard technique for assessment of the scar burden in IHD patients. However, it is not readily accessible in many areas due to availability and cost issues.(Abou, Prihadi et al. 2020), (Bendary, Afifi et al. 2019) Several recent studies suggested 2-D speckle tracking strain as a potential surrogate for cardiovascular magnetic resonance (CMR) late gadolinium enhancement (LGE) imaging. This would be useful in cases where CMR is not available, gadolinium contrast is contraindicated, or in patients at greater risk of adverse long term events. (Erley, Genovese et al. 2019) Among echocardiography derived strain measurements, global longitudinal strain (GLS) was shown to be superior to global circumferential strain (GCS) in its ability to detect subtle myocardial abnormalities due to better reproducibility (Erley et al, 2019). Left ventricular mechanical dispersion (LVMD) is also considered a valuable parameter that was associated with outcomes after myocardial infarction. (Abou, Prihadi et al. 2020).\n\nDespite the growing number of strain related studies in the literature, it is not clear whether the relationship of strain measurements with LGE is strong enough for strain to be considered as a surrogate. (Erley, Genovese et al. 2019). Also, the differences between GLS and LV MD among strain components in this context are not well established." }, "ConditionsModule":{ "ConditionList":{ "Condition":[ "Ischemic Heart Disease" ] } }, "DesignModule":{ "StudyType":"Observational", "PatientRegistry":"No", "DesignInfo":{ "DesignObservationalModelList":{ "DesignObservationalModel":[ "Case-Only" ] }, "DesignTimePerspectiveList":{ "DesignTimePerspective":[ "Cross-Sectional" ] } }, "EnrollmentInfo":{ "EnrollmentCount":"51", "EnrollmentType":"Anticipated" } }, "ArmsInterventionsModule":{ "InterventionList":{ "Intervention":[ { "InterventionType":"Radiation", "InterventionName":"Cardiac MRI, Echocardiography", "InterventionDescription":"Magnetic resonance imaging, Echocardiography" } ] } }, "OutcomesModule":{ "PrimaryOutcomeList":{ "PrimaryOutcome":[ { "PrimaryOutcomeMeasure":"Scare burden by echocardiography", "PrimaryOutcomeDescription":"To test the correlation between the scar burden measured by cardiac MRI and GLS measured by 2D STE", "PrimaryOutcomeTimeFrame":"One year" } ] }, "SecondaryOutcomeList":{ "SecondaryOutcome":[ { "SecondaryOutcomeMeasure":"Value of spickle tracking echocardiography in clinical practice", "SecondaryOutcomeDescription":"Predicting the scar burden in ICMP patients by GLS and mechanical dispersion.", "SecondaryOutcomeTimeFrame":"One year" } ] } }, "EligibilityModule":{ "EligibilityCriteria":"Inclusion Criteria:\n\n• Patients presenting for viability assessment in to cardiac MRI unit, Assiut university heart hospital, with a history of previous stemi, at least 3 months after the acute event ( scar stabilization) and up to one year\n\nExclusion Criteria:\n\nPatients with:\n\nContraindication to cardiac MRI (claustrophobia, Patients with eGFR below 30 mL/min/1.73 m2. and patients with metallic implants)\nNon-ischemic cardiomyopathy.\nValvular heart disease (VHD).", "Gender":"All", "StdAgeList":{ "StdAge":[ "Child", "Adult", "Older Adult" ] }, "StudyPopulation":"Patients presenting for viability assessment in to cardiac MRI unit, Assiut university heart hospital, with a history of previous stemi, at least 3 months after the acute event ( scar stabilization) and up to one year", "SamplingMethod":"Non-Probability Sample" }, "ContactsLocationsModule":{ "CentralContactList":{ "CentralContact":[ { "CentralContactName":"Mohamed Abdellatif, MSC", "CentralContactRole":"Contact", "CentralContactPhone":"+201001073747", "CentralContactEMail":"mohamed.abdellatief@med.au.edu.eg" },{ "CentralContactName":"Shimaa Sayed Khidr, PHD", "CentralContactRole":"Contact", "CentralContactPhone":"+201001346551", "CentralContactEMail":"S.khidr@aun.edu.eg" } ] }, "OverallOfficialList":{ "OverallOfficial":[ { "OverallOfficialName":"Mohamed Abdellatif", "OverallOfficialAffiliation":"Assiut University", "OverallOfficialRole":"Principal Investigator" } ] }, "LocationList":{ "Location":[ { "LocationFacility":"Assiut university", "LocationStatus":"Recruiting", "LocationCity":"Assiut", "LocationZip":"71511", "LocationCountry":"Egypt", "LocationContactList":{ "LocationContact":[ { "LocationContactName":"Mohamed Abdellatif", "LocationContactRole":"Contact", "LocationContactPhone":"00201001073747", "LocationContactEMail":"Mohamed.abdellatief@med.au.edu.eg" } ] } } ] } } }, "DerivedSection":{ "MiscInfoModule":{ "VersionHolder":"July 27, 2021" }, "ConditionBrowseModule":{ "ConditionMeshList":{ "ConditionMesh":[ { "ConditionMeshId":"D000006331", "ConditionMeshTerm":"Heart Diseases" },{ "ConditionMeshId":"D000017202", "ConditionMeshTerm":"Myocardial Ischemia" },{ "ConditionMeshId":"D000003324", "ConditionMeshTerm":"Coronary Artery Disease" },{ "ConditionMeshId":"D000007511", "ConditionMeshTerm":"Ischemia" } ] }, "ConditionAncestorList":{ "ConditionAncestor":[ { "ConditionAncestorId":"D000010335", "ConditionAncestorTerm":"Pathologic Processes" },{ "ConditionAncestorId":"D000002318", "ConditionAncestorTerm":"Cardiovascular Diseases" },{ "ConditionAncestorId":"D000014652", "ConditionAncestorTerm":"Vascular Diseases" },{ "ConditionAncestorId":"D000003327", "ConditionAncestorTerm":"Coronary Disease" },{ "ConditionAncestorId":"D000001161", "ConditionAncestorTerm":"Arteriosclerosis" },{ "ConditionAncestorId":"D000001157", "ConditionAncestorTerm":"Arterial Occlusive Diseases" } ] }, "ConditionBrowseLeafList":{ "ConditionBrowseLeaf":[ { "ConditionBrowseLeafId":"M9126", "ConditionBrowseLeafName":"Ischemia", "ConditionBrowseLeafAsFound":"Ischemic", "ConditionBrowseLeafRelevance":"high" },{ "ConditionBrowseLeafId":"M8002", "ConditionBrowseLeafName":"Heart Diseases", "ConditionBrowseLeafAsFound":"Heart Disease", "ConditionBrowseLeafRelevance":"high" },{ "ConditionBrowseLeafId":"M18089", "ConditionBrowseLeafName":"Myocardial Ischemia", "ConditionBrowseLeafAsFound":"Ischemic Heart Disease", "ConditionBrowseLeafRelevance":"high" },{ "ConditionBrowseLeafId":"M5129", "ConditionBrowseLeafName":"Coronary Artery Disease", "ConditionBrowseLeafAsFound":"Ischemic Heart Disease", "ConditionBrowseLeafRelevance":"high" },{ "ConditionBrowseLeafId":"M4743", "ConditionBrowseLeafName":"Cicatrix", "ConditionBrowseLeafRelevance":"low" },{ "ConditionBrowseLeafId":"M5132", "ConditionBrowseLeafName":"Coronary Disease", "ConditionBrowseLeafRelevance":"low" },{ "ConditionBrowseLeafId":"M15983", "ConditionBrowseLeafName":"Vascular Diseases", "ConditionBrowseLeafRelevance":"low" },{ "ConditionBrowseLeafId":"M3050", "ConditionBrowseLeafName":"Arteriosclerosis", "ConditionBrowseLeafRelevance":"low" },{ "ConditionBrowseLeafId":"M3046", "ConditionBrowseLeafName":"Arterial Occlusive Diseases", "ConditionBrowseLeafRelevance":"low" } ] }, "ConditionBrowseBranchList":{ "ConditionBrowseBranch":[ { "ConditionBrowseBranchAbbrev":"BC23", "ConditionBrowseBranchName":"Symptoms and General Pathology" },{ "ConditionBrowseBranchAbbrev":"All", "ConditionBrowseBranchName":"All Conditions" },{ "ConditionBrowseBranchAbbrev":"BC14", "ConditionBrowseBranchName":"Heart and Blood Diseases" } ] } } } } } }
[ { "modify": { "data": { "ctroot": { "ct": [ { "#set": {} }, { "#include": { "files": [ { "dir": "/var/downloads/samplect/", "fpat": "*/*.json" } ], "batch": { "id": "casestudies", "size": 100 } } } ] } } } } ]
A JSON-over-HTTP API
  • Access SFSQL from any language supporting HTTP and JSON
  • Receive results in Data Formats of JSON (default) and CSV
  • Receive results in Data Styles such as name-value-pairs(default), data-only, and headers-in-first-row (a result style of JSON-Graph is on the roadmap)
  • Issue Multiple Commands in one HTTPS request, reducing API round trips.
Use Cloud SFSQL by simply issuing a HTTPS POST to your dedicated endpoint.
The POST contains a SFSQL-compliant JSON payload (see examples throughout site).
SchemafreeSQL - Diagram
Is SFSQL right for you?
Great! Then learning how to query in a join-less yet deep fashion across object-related schemaless data will only take seconds. SchemafreeSQL's query language uses the SQL constructs you are already familiar with (e.g. GROUP BY, ORDER BY, LIMIT) but frees you from JOINS when querying across deep structure.
NoSQL databases typically provide features such as schemaless data storage, ease of use, and fast queries without (much) optimization. SchemafreeSQL provides all these NoSQL features and more.
It is! Many NoSQL solutions will not query at adequate speeds until indexes are created. The problem is that schemaless structures are by definition not defined until data is stored. You therefore need to "chase" your changing structures with manually-created indexes. Not only that, but choosing WHICH attributes to index must also be dealt with. See "Index Permutation Limitation" for more detail on this problem. SchemafreeSQL requires zero indexing regardless of the complexity of your structures.
References avoid redundancy and ease management. A capability outside of JSON column types and most NoSQL collections. SchemafreeSQL allows you to store a reference to any attribute under any attribute (e.g. Two spouses sharing the same address) without prior setup of schemas or foreign keys constraints.
SchemafreeSQL allows you to query your deeply-related schemaless data using SQL syntax - optionally including any of the built-in functions that your SQL database provides - but without having to write JOIN clauses. The relationships within your data structure are naturally maintained within your database by SchemafreeSQL.
Schemafree has many advantages, but as with most tech you must balance the pros and cons. For instance, Schemafree performs various referential data integrity checks on inserts and as such cannot be expected to perform inserts as fast as less feature-filled systems inserting flat data sets into big-data sized repositories. However, as SchemafreeSQL is implemented over next-gen scalable SQL databases, we may find that in many cases tradeoffs such as these are nullified.