Skip to content

Query

Jump to bottom
M ABD AZIZ ALFIAN edited this page Nov 29, 2019 · 39 revisions

jsonql-totaljs will help you more easier to use your logic as SQL Query to create queries in NoSQL Embedded. If you want to learn more about NoSQL Embedded you can see the documentation from Total.js Framework at here.

< Back to home

CRUD function :

Query features :

select

Like in RDBMS, select is for selecting the data.

Required property for select :

  • from {string} : name of data table.

Example:

var query = [
  {
    select:{
      from:'data_user'
    }
  }
];

Back to CRUD

insert

Like in RDBMS, insert will add new data into the table.

Required property for insert :

  • into {string} : name of data table.
  • values {array} : object to insert into table.

Example

var query = [
  {
    insert:{
      into:'data_user',
      values:[
        // add new data here
      ] 
    }
  }
];

Back to CRUD

update

Like in RDBMS, update will updating for data but you have to carefully because this will replace all old object with the new object.

Required property for update :

  • from {string} : name of data table.
  • where {array} : where clause for update.
  • set {object} : new object for update.

Example:

var query = [
  {
    update: {
      from:'data_user',
      where:[
        ['name','==','aziz']
      ],
      set:{
        id:'1',
        name:'aziz alfian'
      }
    }
  }
];

Back to CRUD

modify

Modify is similar with update but the difference is, this will replace old object with the new object only.

Required property for modify :

  • from {string} : name of data table.
  • where {array} : where clause for update.
  • set {object} : new object for update.

Example:

var query = [
  {
    modify: {
      from:'data_user',
      where:[
        ['name','==','aziz']
      ],
      set:{
        name:'aziz alfian'
      }
    }
  }
];

Back to CRUD

delete

Like RDBMS, delete will remove the data depend on where clause.

  • from {string} : name of data table.
  • where {array} : where clause for delete.

Example:

var query = [
  {
    delete: {
      from:'data_user',
      where:[
        ['id','==','1']
      ]
    }
  }
];

Back to CRUD

fields

Choose fields to display spesific column database.

  • fields {array} : name of fields.

Note:

  • Only be used in select statement.

Example:

var query = [
  {
    select: {
      fields:['id','name','address'],
      from:'data_user',
      where:[
        ['id','==','1']
      ]
    }
  }
];

Back to Query

join

Join mutiple data table.

Required property for join:

  • name {string} : name for joined table.
  • from {string} : name of data table for join.
  • on {array} : identifier match fields.
  • first {bool} : this optional but will be required and set to true if you want to multiple join as nested.

Example Multiple Join:

var query = [
  {
    select: {
      from:'data1',
      join:[
        {
          name:'data2',
          from:'data2',
          on:['id','user_id'],
          first:true
        },
        {
          name:'data3',
          from:'data3',
          on:['id','user_id']
        }
      ]
    }
  }
];

Example Multiple Join Nested:

var query = [
  {
    select: {
      from:'data1',
      join:[
        {
          name:'data2',
          from:'data2',
          on:['id','user_id'],
          first:true,
          join:[
            {
               name:'data3',
               from:'data3',
               on:['id','id']
            }
          ]
        }
      ]
    }
  }
];

Example Multiple Join Nested Manually:

var query = [
  {
    select: {
      from:'data1',
      join:[
        {
          name:'data2',
          from:'data2',
          on:['id','user_id'],
          first:true
        },
        {
          name:'data3',
          from:'data3',
          on:['id','id'],
          first:true
        }
      ],
      nested:['data2','data3']
    }
  }
];

Back to Query

where

Get data by condition where.

  • where {array} : this is a multidimensional array, so you can set multiple where clause.

Array structure for where is ['name','operator','value'].

where operators:

  • Can contains "=", "==", "<=", "<", ">=", ">".

Example single:

var query = [
  {
    select: {
      from:'data_user',
      where:[
        ['id','==','1']
      ]
    }
  }
];

Example multiple:

var query = [
  {
    select: {
      from:'data_user',
      where:[
        ['id','==','1'],
        ['name','==','aziz']
      ]
    }
  }
];

Note:

  • Can't be used in insert statements.
  • Doesn't work in nested join.

Back to Query

between

Creates a filter for finding a value beween two numbers.

  • between {array} : this is a multidimensional array, so you can set multiple between clause.

Array structure for between is ['name','valueA','valueB'].

Example single:

var query = [
  {
    select: {
      from:'data_user',
      between:[
        ['created_at','2019-10-02 00:00:00','2019-10-03 23:59:59']
      ]
    }
  }
];

Example multiple:

var query = [
  {
    select: {
      from:'data_user',
      between:[
        ['created_at','2019-10-02 00:00:00','2019-10-03 23:59:59'],
        ['activated_at','2019-10-02 00:00:00','2019-10-03 23:59:59']
      ]
    }
  }
];

Note:

  • Can't be used in insert statements.
  • Doesn't work in nested join.

Back to Query

search

Create a text search condition. This is ignores case-sensitive.

  • search {array} : this is a multidimensional array, so you can set multiple search clause.

Array structure for search is ['name','value','operator'].

search operator:

  • [optional] Can contain "beg" (from begin), "end" (from end), "*" (everything).
  • Default operator is *

Example single:

var query = [
  {
    select: {
      from:'data_user',
      search:[
        ['name','wa']
      ]
    }
  }
];

Example multiple:

var query = [
  {
    select: {
      from:'data_user',
      search:[
        ['name','wa'],
        ['address','jak','beg']
      ]
    }
  }
];

Note:

  • Can't be used in insert statements.
  • Doesn't work in nested join.

Back to Query

fulltext

Create a fulltext search condition. This is ignores case-sensitive.

  • fulltext {array} : this is a multidimensional array, so you can set multiple fulltext clause.

Array structure for fulltext is ['name','value','weight'].

fulltext weight:

  • [optional] Weight in percentage. For example: if you try to find 10 words and weight will be 100% then each word must contain field. Weight 50% will require existing of 5 words in the value from 10 searched words. Weight 0 tries to find min. 1 word from searched words..
  • Default weight is 0

Example single:

var query = [
  {
    select: {
      from:'data_user',
      fulltext:[
        ['name','wa']
      ]
    }
  }
];

Example multiple:

var query = [
  {
    select: {
      from:'data_user',
      fulltext:[
        ['name','wa'],
        ['address','jak','80%']
      ]
    }
  }
];

Note:

  • Can't be used in insert statements.
  • Doesn't work in nested join.

Back to Query

regexp

Create a text search condition by regular expression. This is ignores case-sensitive.

  • regexp {array} : this is a multidimensional array, so you can set multiple regexp clause.

Array structure for regexp is ['name','value'].

Example single:

var query = [
  {
    select: {
      from:'data_user',
      regexp:[
        ['name',/aziz/]
      ]
    }
  }
];

Example multiple:

var query = [
  {
    select: {
      from:'data_user',
      regexp:[
        ['name',/aziz/],
        ['address',/solo/]
      ]
    }
  }
];

Note:

  • Can't be used in insert statements.
  • Doesn't work in nested join.

Back to Query

and

Creates and. This and is already default scope for all conditions.

  • and {object} : and clause is an object.

Example:

var query = [
  {
    select: {
      from:'data_user',
      and:{
        where:[
          ['name','==','aziz'],
          ['address','==','jakarta']
        ]
      }
    }
  }
];

Example mixed conditions:

var query = [
  {
    select: {
      from:'data_user',
      and:{
        between:[
          ['created_at','2019-10-02 00:00:00','2019-10-03 23:59:59']
        ],
        where:[
          ['name','==','aziz'],
          ['address','==','jakarta']
        ]
      }
    }
  }
];

Note:

  • Can't be used in insert statements.
  • Doesn't work in nested join.

Back to Query

or

Creates or.

  • or {object} : or clause is an object.

Example:

var query = [
  {
    select: {
      from:'data_user',
      or:{
        where:[
          ['name','==','aziz'],
          ['name','==','tika']
        ]
      }
    }
  }
];

Example mixed conditions:

var query = [
  {
    select: {
      from:'data_user',
      or:{
        between:[
          ['created_at','2019-10-02 00:00:00','2019-10-03 23:59:59']
        ],
        where:[
          ['name','==','aziz'],
          ['name','==','tika']
        ]
      }
    }
  }
];

Note:

  • Can't be used in insert statements.
  • Doesn't work in nested join.

Back to Query

sort

This will creates a sorting.

  • sort {array} : this is just a single array.

Array structure for sort is ['name','desc'].

  • [optional] desc is a boolean.
  • Default desc value is false.

Example sort ascending:

var query = [
  {
    select: {
      from:'data_user',
      sort:['user_id',false]
    }
  }
];

Example sort descending:

var query = [
  {
    select: {
      from:'data_user',
      sort:['user_id',true]
    }
  }
];

Note:

  • Only use in select statements.
  • Doesn't work in nested join.

Back to Query

take

This will takes a specific number of documents.

  • take {string|integer} : this value is number.

Example:

var query = [
  {
    select: {
      from:'data_user',
      take:2
    }
  }
];

Note:

  • Only use in select statements.
  • Doesn't work in nested join.

Back to Query

skip

This will skip a specific number of documents.

  • skip {string|integer} : this value is number.

Example:

var query = [
  {
    select: {
      from:'data_user',
      skip:2
    }
  }
];

Note:

  • Only use in select statements.
  • Doesn't work in nested join.

Back to Query

page

Sets skip and take automatically according to the arguments.

  • page {array} : this is just a single array.

Array structure for page is ['page','limit'].

Example:

var query = [
  {
    select: {
      from:'data_user',
      page:[1,2]
    }
  }
];

Note:

  • Only use in select statements.
  • Doesn't work in nested join.

Back to Query

paginate

Sets skip and take automatically according to the arguments. but the arguments can be string or integer.

  • paginate {array} : this is just a single array.

Array structure for paginate is ['page','limit'].

Example:

var query = [
  {
    select: {
      from:'data_user',
      paginate:['1','2']
    }
  }
];

Note:

  • Only use in select statements.
  • Doesn't work in nested join.

Back to Query

day

This will created a day filter.

  • day {array} : this is a multidimensional array, so you can set multiple day clause.

Array structure for day is ['name','operator','value'].

day operator:

  • Can contains "=", "==", "<=", "<", ">=", ">".

Example:

var query = [
  {
    select: {
      from:'data_user',
      day:[
        ['created_at','==','01']
      ]
    }
  }
];

Note:

  • Cant be used in insert statements.
  • Doesn't work in nested join.

Back to Query

month

This will created a month filter.

  • month {array} : this is a multidimensional array, so you can set multiple month clause.

Array structure for month is ['name','operator','value'].

month operator:

  • Can contains "=", "==", "<=", "<", ">=", ">".

Example:

var query = [
  {
    select: {
      from:'data_user',
      month:[
        ['created_at','==','10']
      ]
    }
  }
];

Note:

  • Cant be used in insert statements.
  • Doesn't work in nested join.

Back to Query

year

This will created a year filter.

  • year {array} : this is a multidimensional array, so you can set multiple year clause.

Array structure for year is ['name','operator','value'].

year operator:

  • Can contains "=", "==", "<=", "<", ">=", ">".

Example:

var query = [
  {
    select: {
      from:'data_user',
      year:[
        ['created_at','==','2019']
      ]
    }
  }
];

Note:

  • Cant be used in insert statements.
  • Doesn't work in nested join.

Back to Query

in

This will creates in conditions.

  • in {array} : this is a multidimensional array, so you can set multiple in clause.

Array structure for in is ['name','value'].

in value:

  • Can contain String Array, Number Array, Boolean Array and Date Array.

Example:

var query = [
  {
    select: {
      from:'data_user',
      in:[
        ['sosmed','twitter.com/ccc']
      ]
    }
  }
];

Note:

  • Cant be used in insert statements.
  • Doesn't work in nested join.

Back to Query

notin

This will creates notin conditions.

  • notin {array} : this is a multidimensional array, so you can set multiple notin clause.

Array structure for notin is ['name','value'].

notin value:

  • Can contain String Array, Number Array, Boolean Array and Date Array.

Example:

var query = [
  {
    select: {
      from:'data_user',
      notin:[
        ['sosmed','twitter.com/ccc']
      ]
    }
  }
];

Note:

  • Cant be used in insert statements.
  • Doesn't work in nested join.

Back to Query

scalar

Performs a scalar operation only in join scope. The method returns Number (for sum, min, max, avg and count) or Object (for group) in a response.

  • scalar {array} : this is just a single array.

Array structure for scalar is type, [field]

  • type : Scalar type: sum, min, max, avg, count or group.
  • field : [optional] A field name (optional for count).

Example:

var query = [
  {
    select: {
      from:'data_user',
      scalar:['count','id']
    }
  }
];

Example with join:

var query = [
  {
    select: {
      from:'data_user',
      join:[
        {
          name:'transaction',
          from:'transaction',
          on:['user_id','user_id'],
          scalar:['sum','transaction']
        }
      ]
    }
  }
];

Note:

  • Cant be used in insert statements.
  • Doesn't work in nested join.

Back to Query

query

Creates a simple condition which will be evaluated as a JS code. The code must return a Boolean value without return keyword.

  • query {string} : this value is like JS code in string.

Example:

var query = [
  {
    select: {
      from:'data_user',
      query:'doc.id===2 && doc.address=="jakarta"'
    }
  }
];

Note:

  • Cant be used in insert statements.
  • Doesn't work in nested join.

Back to Query

random

This will return a random items.

  • random {bool}: this value is a boolean.

Example:

var query = [
  {
    select: {
      from:'data_user',
      random:true
    }
  }
];

Note:

  • Cant be used in insert statements.
  • Doesn't work in nested join.

Back to Query

Clone this wiki locally