jsql

Overview

jsql is a JavaScript query language, along with a simple JavaScript objects (POJSO) reference implementation.

This is not intended to search the dom, or jQuery, or some specific database, nor is it intended to enable using SQL in a browser. jsql is intended to provide a native JSON query format for querying anything, although initially limited to JavaScript objects.

Reference Implementation

The reference implementation, searchjs, uses jsql to query a JS object, or an array of objects, and to return those results that match the query.

Syntax Definition

jsql syntax is defined as follows.

jsql always is a single JavaScript object: {} with properties that determine the parameters for the query.

There are three kinds of properties for a query:

• Primitives: Primitives match one or more fields on AND or OR, with or without a negation.
• Modifiers: Modifiers determine how the other properties are treated: negation, field join type, ranges and text searches.
• Composites: Composites join multiple primitives. The primitives are in an array in the field "terms". See example 7.

Primitives

A primitive is an object with properties that are matched.

• {name:"John"} - primitive that checks that the name field is equal to "John"

Multiple fields in a primitive are, by default, joined by logical AND. See under Modifiers to change this.

• {name:"John",age:30} - primitive that checks that the name field is equal to "John" AND that the age field is equal to 30

The name of a field in a primitive is always the name of the field to match in the record. The value can be one of:

• Basic types: string, number, date - will match directly against the value in the record. Case is ignored in string matches.
• Array: will match against any one of the values in the array. See below.
• Object: will look for a range. See below.

Primitives will search against individual values and against one or more matches in an array. So the search {name:"John"} will match against any of the following objects:

• {name:"John"}
• {name:["John","jim"]}
• {name:["jim","John"]}

Deep Searching

You are not limited to searching only at the top level. You also can do deep searching on an object of an object using dot-notation. So if you want to match on the object {city: {Montreal: true}} then you can search:

{"city.Montreal": true}

The above is a search primitive that checks that the field "city" has an object as its value, which in turn has a key "Montreal" with a value of true. You can go as deep as you want. The following is a completely valid deep-search primitive:

{"country.province.city.street":"Dorchester Blvd"}

Any modifiers that apply to simple primitives apply to deep fields as well.

Deep Searching Arrays

Deep searching is not limited to objects embedded in objects. You can have arrays of objects embedded in objects. You even can have arrays of objects embedded in arrays of objects embedded in... (you get the idea!).

Thus, the search primitive {"name.cars.hp":{from:200}} will match any of the following:

• {cars: {brand: 'porsche',hp:450}}
• {cars: [{brand: 'bmw',hp:250},{brand: 'lada',hp:10}]} matches the 'bmw' but not the 'lada', therefore the whole object matches

You can also deep-search for an object using nested primitives. Search primitive {cars: {brand: 'porsche',hp:450}} will match:

• {name: 'alice', cars:[{brand: 'porsche',hp:450}, {brand: 'bmw',hp:250}, {brand: 'lada',hp:10}]}

But will not match following:

• {name: 'alice', cars:[{brand: 'porsche',hp:250}, {brand: 'bmw',hp:450}, {brand: 'lada',hp:10}]}

Number of levels of deep-searching is not limited. The valid primitive may look like {cars: {brand: 'porsche',hp:450, color: {exterior: 'red', interior: {seats: 'beige', dashboard: 'black'}}}}

Property Search

If you are not sure in which level a specific property can be found you can use the propertySearch modifier. It checks on each level if a property exists and then checks if it matches.

The following search would find the item below:

{"name":"tom", _propertySearch:true}

Item:

{"level1":{"level2":{"level3":{name: "tom"}}}}

This works also in combination with Deep Search.

It is possible to omit any level in between. So all the following queries will match the above item.

{"name":"tom", "_propertySearch": true}
{"level1.name":"tom", "_propertySearch": true}
{"level1.level2.name":"tom", "_propertySearch": true}
{"level3.name":"tom", "_propertySearch": true}
{"level1.level3.name":"tom", "_propertySearch": true},
{"name":"tom", "_propertySearch": true, "_propertySearchDepth": 4}
{"level1.name":"tom", "_propertySearch": true, "_propertySearchDepth": 4}

It is also possible and often recommended to limit the search depth. The following query would match the above item:

{"name": "tom", "_propertySearch": true, "_propertySearchDepth": 4}

However this one would not because it stops the search one level before:

{"name":"tom", "_propertySearch": true, "_propertySearchDepth": 3}

searchjs normally matches exactly the objects and depths you provide. With Property Searching, it is possible, especially on a large data set, to spend a lot of time (and CPU and memory) searching. We strongly recommend limiting the propertySearchDepth unless you know the data set with which you are working is limited.

Array Primitive

If the value of a field in a primitive is an array, then it will accept a match of any one of the array values.

{name:["John","Jack"]} // accepts any record where the name field matches 'John' or 'Jack'
{_join:"OR",terms:[{name:"John"},{name:"Jack"}]} // equivalent to the previous

Additionally, if the target record also has an array, it will accept a match if any one of the values in the array of the record matches any one of the values in the array of the search term.

{name:["John","Jack"]}

will match any of these:

{name:"John",phone:"+12125551212"}
{name:"Jack",location:"Canada"}
{name:["John","Jim"],company:"Hot Startup"}

Range

If the value of a field in a primitive is an object with "from", "to", "gt", "lt", "gte" or "lte" fields, then it will treat it as a range.

{age:{from:30}}  // accepts any age >=30
{age:{gte:30}}  // accepts any age >=30
{age:{gt:30}}  // accepts any age >30
{age:{to:80}}    // accepts any age <=80
{age:{lte:80}}    // accepts any age <=80
{age:{lt:80}}    // accepts any age <80
{age:{from:30,to:80}}  // accepts any age from 30 to 80 (inclusive)
{_not:true,age:{from:30}} // accepts any age <30
{age:{nothing:"foo"}}  // ignored

Accept values in to and from fields in a range are numbers and strings. The type of the target record's data must match the type of the value of from and to. If not, it is treated as unmatched. You cannot match {age:{from:30}} to a record {age:"veryold"}!

Note that "gte" and "from", and "lte" and "to", are interchangeable, while "gt" and "lt" are is equivalent to ">" and "<" respectively!

Modifiers

Modifiers change the search term of a primitive.

Negation

Negation just sets the opposite. Instead of checking if the "name" field equals "John", you can check if it does not equal "John":

{name:"John",_not:true}   // match all records in which name !== "John"

Just add the field _not to the primitive and set it to true. If the _not field does not exist, or is set to false or null, it will be ignored.

Join

Join determines how multiple fields are put together. Instead of checking if "name" equals "John" AND "age" equals 30, you can check if "name" equals "John" OR "age" equals 30:

{name:"John",age:30,_join:"OR"}   // match all records in which name === "John" || age === 30

Just add the field _join to the primitive and set it to "OR". If the _join field does not exist, or is set to "AND", it will join the field in "AND".

Text Searching

In general, if you search a field that is a string, and the search primitive is a string, then it will be an exact match, ignoring case.

{name:"davi"} will match a record whose content is {name:"davi"}, as well as one whose "name" field matches "Davi" and "DAVI", but not one whose content is {name:"david"} or even {name: "davi abc"}.

If you want a text search that can do partial matches, text searching is here to help!

There are two variants on text search that can expand your ability to search text fields:

1. substring: if you set the flag {_text: true} as part of your search, then it searches for your match as part of the field. In other words, if your search is {name:"davi", _text:true} then it will check if the field matches /davi/i.
2. word: if you set the flag {_word: true} as part of your search, then it search for your match as a complete word in the field. In other words, if your search is {name:"davi",_word:true} then it will check if the field matches /\bdavi\b/i.
3. start: if you set the flag {_start: true} as part of your search, then it search for your match as a part of begin of the field. In other words, if your search is {name:"davi",_start:true} then it will check if the field matches /^davi/i.
4. end: if you set the flag {_end: true} as part of your search, then it search for your match as a part end of the field. In other words, if your search is {name:"davi",_end:true} then it will check if the field matches /davi$/i.

The _text option will override the _word option if both exist.

Here are some examples of text searchin