JSON payloads consist of an array of one or more Commands.
NOTE: Any command can be commented out by prefixing it with an '*' (asterix)
Example Payload with '...' shown in place of command contents:
A modify command followed by a query Command within the same payload.
[
{
"modify": {
...
}
},
{
"query": {
...
}
},
{
"*query": {
...
}
}
]
Additionally, one or more SubCommands may be specified for a Command.
Example of a SubCommand as first element of array:
A modify Command with the #append SubCommand specified on an array of person objects. In this example, 'Ian Redy' is appended to the array.
(#update tells SFSQL that it may update existing data but not insert new data).
Example of a SubCommand as first element of array:
A modify Command with the #append SubCommand specified on an array of person objects. In this example, 'Ian Redy' is appended to the array.
(#update tells SFSQL that it may update existing data but not insert new data).
[
{
"modify":{
"data": {
"o:person": [
{
"#append": {}
},
{
"s:firstname": "Ian",
"s:lastname": "Redy"
}
]
}
}
}
]
Example of a SubCommand included as an object attribute.
A modify Command with the #update SubCommand specified on a single person object. In this example the person matching the where parameter is updated.
A modify Command with the #update SubCommand specified on a single person object. In this example the person matching the where parameter is updated.
[
{
"modify": {
"data": {
"o:person": {
"#update": {"where":"$lastname='Redy' and $firstname='Ian'"},
"s:middlename": "Relly"
}
}
}
}
]
modify
Description
Modify simple objects and attributes or even complete graphs
Parameters
| data | Any valid JSON structure (using SFSQL-conforming names) |
Example: Simple
In this example, we set the value of text attribute "testRootPrimitive" under root to "test string"
[
{
"modify": {
"data": {
"testRootPrimitive": "test string"
}
}
}
]
Example: Nested Structures
[
{
"modify": {
"data": {
"company": {
"cname": "Company Inc.",
"phone": [
"111-111-1111",
"222-222-2222"
],
"address": {
"name": "Company Inc Headquarters",
"street": "123 Headquarter St.",
"city": "Rochester",
"state": "NY",
"pcode": "14605"
}
}
},
"_comment": "Example: Nested Structures"
}
}
]
Sub Commands
#ref
Link to an existing Object.
Example: link object attribute to an existing Object by Object ID
Example: Query to find Object ID.
Results in an error if more than one object matches.
Example: select object OID where fname is 'John' and lname is 'Thomas'
Example: link object attribute to an existing Object by Object ID
...
"o:emplyee": {
"#ref": 35
}
...
Link to an existing Object
Example: Query to find Object ID.
Results in an error if more than one object matches.
Example: select object OID where fname is 'John' and lname is 'Thomas'
...
"o:emplyee": {
"#ref": "SELECT $o:.Person.oid() WHERE $s:.Person.fname='John' AND $s:.Person.lname='Thomas'"
}
...
Attributes following the "#ref" attribute behave as an in a update command
...
"o:emplyee": {
"#ref": "SELECT $o:.Person.oid() WHERE $s:.Person.fname='John' AND $s:.Person.lname='Thomas'",
"email":"support@schemafreesql.com",
"age":55
}
...
#set
Update an existing attribute or add a new attribute.
Example: set the person object in array position 0 to Sue Shee
Parameters:
Example: set the person object in array position 0 to Sue Shee
...
"o:person": [
{"#set": {}},
{
"s:firstname": "Sue",
"s:lastname": "Shee",
"s:state": "NJ"
}
]
Parameters:
| where | OPTIONAL
The filter used to find the matching object which will be updated. Results in an error if more than one object matches. Example: set person's state to 'NY' where person's lastname is 'Shee' ...
"o:person": [
{"#set": {"where": "$s:lastname='Shee'"}},
{
"s:state": "NY"
}
]
...
|
#update
Update an existing attribute.
If the attribute does not already exist, no action is taken.
Example: update the person object in array position 0 to Sue Shee
Parameters:
If the attribute does not already exist, no action is taken.
Example: update the person object in array position 0 to Sue Shee
...
"o:person": [
{"#update": {}},
{
"s:firstname": "Sue",
"s:lastname": "Shee",
"s:state": "NJ"
}
]
Parameters:
| where | OPTIONAL
The filter used to find the matching object which will be updated. Results in an error if more than one object matches. Example: update person's state to 'NY' where person's lastname is 'Shee' ...
"o:person": [
{"#update": {"where": "$s:lastname='Shee'"}},
{
"s:state": "NY"
}
]
...
|
#append
Append new attributes to the end of an array.
Example: add the given person objects to the end of the person array
Example: add the given person objects to the end of the person array
...
"o:person": [
{"#append": {}},
{
"s:firstname": "yName"
},
{
"s:firstname": "zName"
}
]
#include
Include an external JSON source file or files
Example: add the person objects represented by the json files found in directory /var/persons/, stopping after processing 100 files.
Parameters:
Example: add the person objects represented by the json files found in directory /var/persons/, stopping after processing 100 files.
...
"o:person": [
{"#append": {} },
{
"#include": {
"files": [
{
"dir": "/var/persons/",
"fpat": "*.json"
}
],
"batch": {
"id": "personsAppend",
"size": 100
}
}
}
]
Parameters:
| files | The parameter section that defines the files to be included. |
dir |
The root directory where files will be included from. |
fpat |
The file pattern used to match files found in specified dir.
If directories are nested, a multi-layered pattern can be used to arrive at the final destination files. For example: a pattern of "*/*.json" would include dir/XYZ/person1.json but not dir/xyz/abc/person2.json |
| batch | The parameter section that defines the batch parameters |
id |
Provide a batch id unique to this include process.
For example, if we specify an id of 'personBatch' and only run the first 100 files (via the 'size' parm), then specifying the same 'personBatch" value for id will allow us to continue including files where we last left off, at file 101. |
size |
The batch size to use. |
startpos |
The start position to use within the batch.
This value is automatically kept track of per each batch 'id' (starting at position 0 the first time used and incremented to the position of the last included file +1 at batch completion). However, you can explicitly provide a value in order to for instance, restart your batch at 0. |
#insert
Insert new attributes into a specified position of an array.
TO BE IMPLEMENTED
TO BE IMPLEMENTED
query
Description
Perform a SFSQL query against your dataset
Parameters
| sfsql | SFSQL query |
| data-format | json (default)
csv |
| data-style | nvp (default) - name value pair (not allowed when using the 'csv' data-format)
colnames - column names returned in the first row dataonly - no column names. Only values are returned. |
Example:
Select People's first and last names along with the name of their Company
[
{
"query": {
"sfsql": "SELECT $s:.Company.Cname, $s:.Company.Person.fname, $s:.Company.Person.lname",
"data-format": "json",
"data-style": "nvp",
"_comment": "query .Company"
}
}
]
Response:
[
{
"data" : [
{
"s:Company.Person.fname" : "John",
"s:Company.Cname" : "Apple",
"s:Company.Person.lname" : "Thomas"
},
{
"s:Company.Person.fname" : "Larry",
"s:Company.Cname" : "Tesla",
"s:Company.Person.lname" : "Johnson"
},
{
"s:Company.Person.fname" : "Jane",
"s:Company.Cname" : "Google",
"s:Company.Person.lname" : "Tally"
}
],
"_comment" : "query .Company",
"success" : 1,
"cmdname" : "query"
}
]
delete
Description
The command 'delete' allows deletion of Object Attributes by using a SFSQL (SQL-like) Object filter.
It enables you to traverse and mark as 'deleted' the found Object and it's children.
After marking as deleted, you have the ability to either 'undelete' or 'purge' those marked Objects.
It enables you to traverse and mark as 'deleted' the found Object and it's children.
After marking as deleted, you have the ability to either 'undelete' or 'purge' those marked Objects.
Parameters
| objfilter | SFSQL Query to select which Objects to delete. The SELECT clause of the filter should use the system function attrset() against the desired object to delete. (e.g. attrset('delete') type: string MANDATORY |
| attrfilter | List of attributes to delete type: JSON Array of Strings representing Attribute Names NOT YET IMPLEMENTED |
Result Variables
| result.did | The Delete ID that the matched objects were marked with. type: int This can be saved to a global variable for use in subsequent commands within the same payload (e.g. "__did": "!@result.did@!" would save the did to __did which can be passed to a subsequent 'purge' command.) |
Example
Delete the person object with id 12345
Payload:
[
{
"delete": {
"objfilter": "SELECT $o:person.attrset('delete') WHERE $i:person.id=12345"
}
}
]
Response:
[
{
"cmdname" : "delete",
"objfilter" : "SELECT $o:person.attrset('delete') WHERE $i:person.id=12345",
"success" : 1,
"result.did" : "113"
}
]
undelete
Description
The command 'undelete' allows you to 'undo' what was performed during the 'delete' command. It un-marks objects that have been marked for deletion.
Parameters
| did | The Delete ID that objects were marked with during a previouly run 'delete' command. type: int MANDATORY |
Example
Undelete the person objects marked for deletion with did 113
Payload:
[
{
"undelete": {
"did": 113,
"_comment": "undelete person 12345 which was marked for deletion with did 113."
}
}
]
Response:
[
{
"cmdname" : "undelete",
"did" : "113",
"success" : 1
}
]
purge
Description
The 'purge' command permanently removes objects that have been marked for deletion.
Parameters
| did | The Delete ID that objects were marked with during a previously run 'delete' command. If supplied, only the objects marked with that specific did will be purged. If not supplied, then all objects marked for deletion - regardless of their did - will purged. type: int OPTIONAL |
Example
Purge the person objects marked for deletion with did 113
Payload:
[
{
"purge": {
"did": 113,
"_comment": "purge the person 12345 which was marked for deletion with did 113."
}
}
]
Response:
[
{
"cmdname" : "purge",
"success" : 1
}
]
showattrs
Description
The command 'showattrs' (Show Attributes) shows the current structure (attribute relationships) within your data.
As the name 'SchemafreeSQL' implies, SchemafreeSQL enables you to store schemaless data structures at runtime without having to predefine schemas in the database.
But when you want to examine the current state of your schemaless data, the simple-yet-powerful command 'showattrs' will provide you with the information you need.
Example
(Default format, no parameters supplied)
Payload:
Response:
[
{
"showattrs":{}
}
]
Response:
[
{
"data" : [
"o:rootnode",
"o:rootnode.childnode",
"o:rootnode.childnode.childsubobj",
"s:rootnode.childnode.childsubobj.stringattr"
],
"success" : 1,
"cmdname" : "showattrs"
}
]
Parameters
| rootattr | Restrict results to only attributes under specified named root attribute. type: string DEFAULT:""; no restriction; everything shown. |
| poid | The Parent OID. This is where the attribute listing will begin from in your hierarchy. 0 specifies the db root. Set to -1 or leave this parameter out to specify a parent-insensitive, global listing. type: integer DEFAULT:-1, no restriction to parent OID. |
| did | Restrict results to the given did (Delete ID), in other words, only show attributes that have been deleted within the specified 'did'. Set to 0 or leave this parameter out to show all attributes regardless of whether deleted or not. type: integer DEFAULT:0, no restriction; everything shown. |
| maxdepth | The maximum depth to recurse to. Zero-based, root level is 0. type: integer DEFAULT:1000 |
| include-type | Include the attribute type field. type: boolean DEFAULT:false |
| include-type-prefix | Prefix the attribute name with it's type-hint. type: boolean DEFAULT:true |
| include-depth | Include the recursive depth of the attribute in relation to the rootattr. type: boolean DEFAULT:false |
| include-name | Include the attribute name. type: boolean DEFAULT:false |
| include-path | Include the full dot-delimited path of each attribute. type: boolean DEFAULT:true |
| indent | Set the indent string which will be prepended 'depth' number of times to the attribute name. type: string DEFAULT:" " |
break
Description
The command 'break' causes processing of the request payload to cease, effectively 'breaking' out of the request.
The responses from the commands preceding the break will be returned.
The commands following the break will not be executed.
The responses from the commands preceding the break will be returned.
The commands following the break will not be executed.
Example
Payload:
[
{
"break":{}
}
]