Skip to content

Query

Jump to bottom
M ABD AZIZ ALFIAN edited this page Aug 25, 2021 · 29 revisions

Below here is the example how you can use your logic as similar when you created SQL Query with fly-json-odm.

Most of query in below example is case sensitive. To make it case insensitive you have to set false.
Ex: .where('address', 'like', 'a', false).
For more detail please see Comparison Operators.

Table of Contents

Example Data

Any queries below will use this example data json array.

var data1 = [
    {user_id:1,name:'budi',age:10},
    {user_id:5,name:'wawan',age:20},
    {user_id:3,name:'tono',age:30}
];

var data2 = [
    {id:1,address:'bandung',email:'[email protected]'},
    {id:2,address:'jakarta',email:'[email protected]'},
    {id:3,address:'solo',email:'[email protected]'},
    {id:4,address:'solo, balapan',email:'[email protected]'},
    {id:5,address:'surabaya',email:'[email protected]'}
];

var data3 = [
    {id:1,bio:'I was born in bandung',phone:'[email protected]'},
    {id:2,bio:'I was born in jakarta',phone:'[email protected]'},
    {id:3,bio:'I was born in solo',phone:'[email protected]'},
    {id:4,bio:'I was born in semarang',phone:'[email protected]'},
    {id:5,bio:'I was born in surabaya',phone:'[email protected]'}
];

var data4 = [
    {brand:'Audi',color:'black',stock:32},
    {brand:'Audi',color:'white',stock:76},
    {brand:'Ferarri',color:'red',stock:8},
    {brand:'Ford',color:'white',stock:49},
    {brand:'Peugot',color:'white',stock:23}
];

var rangedata = [
    {id:1,name:'AAA',created:'2019-10-01 00:02:33'},
    {id:2,name:'BBB',created:'2019-10-02 01:52:53'},
    {id:3,name:'CCC',created:'2019-10-03 02:42:43'},
    {id:4,name:'DDD',created:'2019-10-04 03:32:13'},
    {id:5,name:'EEE',created:'2019-10-05 04:22:13'},
    {id:6,name:'FFF',created:'2019-10-06 05:12:33'},
    {id:7,name:'GGG',created:'2019-10-07 06:02:03'}
];

var dataEmptyArray = [
  { id: 1, level: 'medium', group: [{ category: 'Arcade' }] },
  { id: 3, level: 'hard', group: [] },
  { id: 5, level: 'easy', group: [{ category: 'Strategy' }] }
];

var dataEmptyObject = [
  { id: 1, level: 'medium', group: { category: 'Arcade' } },
  { id: 3, level: 'hard', group: {} },
  { id: 5, level: 'easy', group: { category: 'Strategy' } }
];

Back to top

Basic Query

Select + Where

var data = nosql.set(data2)
  .select(['id','address'])
  .where('address','jakarta')
  .exec();
console.log(data);

similar in SQL Query

SELECT id, address 
FROM data2 
WHERE address = 'jakarta';

Back to top

Select + Where Between

var data = nosql.set(data1)
  .select(['user_id','name','age'])
  .where('age','>=','10')
  .where('age','<=','30')
  .exec();
console.log(data);

similar in SQL Query

SELECT user_id, name, age 
FROM data1 
WHERE age BETWEEN '10' AND '30';

Select + Where Date Between

It's recommended if your date is use number as Unix format. But if your date is string, you can make between with where + function like below this.

var startDate = new Date("2019-10-01");
var endDate = new Date("2019-10-04");
var data = nosql.set(rangedata)
  .select(['id','name','created'])
  .where('created','function',function(value) {
    aDate = new Date(value);
    return aDate >= startDate && aDate <= endDate;
  })
  .exec();
console.log(data);

similar in SQL Query

SELECT id, name, created 
FROM rangedata 
WHERE date(created) BETWEEN '2019-10-01' AND '2019-10-04';

Back to top

Select + Where to Remove Empty Array

Sometimes we want to get rid of any empty array.

var data = nosql.set(dataEmptyArray)
  .select(['id','level','group'])
  .where('group','function',function(value) {
    return !nosql.isEmptyArray(value);
  })
  .exec();
console.log(data);

Back to top

Select + Where to Remove Empty Object

Sometimes we want to get rid of any empty object.

var data = nosql.set(dataEmptyObject)
  .select(['id','level','group'])
  .where('group','function',function(value) {
    return !nosql.isEmptyObject(value);
  })
  .exec();
console.log(data);

Back to top

Select + Where + And

var data = nosql.set(data2)
  .select(['id','address'])
  .where('address','like','a')
  .where('address','like','ba')
  .exec();
console.log(data);

similar in SQL Query

SELECT id, address 
FROM data2 
WHERE address LIKE '%a%' 
AND address LIKE '%ba%';

Back to top

Select + Where + Or

var data = nosql.set(data2)
  .select(['id','address'])
  .begin()
  .where('address','like','u')
  .or()
  .where('address','===','solo')
  .end()
  .exec();
console.log(data);

similar in SQL Query

SELECT id, address 
FROM data2 
WHERE address LIKE '%u%' 
OR address = 'solo';

Back to top

Select + Where + Multiple Or

var data = nosql.set(data2)
  .select(['id','address','email'])
  .begin()
  .where('address','like','u')
  .or()
  .where('address','===','solo')
  .or()
  .where('email','like','@')
  .end()
  .distinct()
  .exec();
console.log(data);

similar in SQL Query

SELECT id, address 
FROM data2 
WHERE address LIKE '%u%' 
OR address = 'solo'
OR email LIKE '%@%';

Note:

  • Multiple OR in many different field could lead you many duplicate data result so you have to use .distinct().
  • Using .distinct() is slower because it will check all object values. If you know the unique field (ex. field id should be unique) then just use .distinct('id'). For more detail please see Main Method.

Back to top

Select + Where + Or + Order By

var data = nosql.set(data2)
  .select(['id','address'])
  .begin()
  .where('address','like','u')
  .or()
  .where('address','===','solo')
  .end()
  .orderBy('id',false)
  .exec();
console.log(data);

similar in SQL Query

SELECT id, address 
FROM data2 
WHERE address LIKE '%u%' 
OR address = 'solo' 
ORDER BY 'id' ASC;

Back to top

Select + Where + Or + Order By + Limit

var data = nosql.set(data2)
  .select(['id','address'])
  .begin()
  .where('address','like','u')
  .or()
  .where('address','===','solo')
  .end()
  .orderBy('id',false)
  .take(2)
  .exec();
console.log(data);

similar in SQL Query

SELECT id, address 
FROM data2 
WHERE address LIKE '%u%' 
OR address = 'solo' 
ORDER BY 'id' ASC 
LIMIT 2;

Back to top

Select + Where + Or + Order By + Limit + Offset

var data = nosql.set(data2)
  .select(['id','address'])
  .begin()
  .where('address','like','u')
  .or()
  .where('address','===','solo')
  .end()
  .orderBy('id',false)
  .skip(1)
  .take(2)
  .exec();
console.log(data);

similar in SQL Query

SELECT id, address 
FROM data2 
WHERE address LIKE '%u%' 
OR address = 'solo' 
ORDER BY 'id' ASC 
LIMIT 1,2;

Back to top

Select + Where + Or + Order By + Pagination

Limit and Offset mostly use for pagination, but we have more simpler way to make a pagination

var data = nosql.set(data2)
  .select(['id','address'])
  .begin()
  .where('address','like','u')
  .or()
  .where('address','===','solo')
  .end()
  .orderBy('id',false)
  .paginate(1,2)
  .exec();
console.log(data);

Back to top

Group Query

Group By or with SUM

At example above we are always use select(), but you are able to query without select() like this

var data = nosql.set(data4)
  .groupBy('brand')
  .orderBy('brand')
  .exec();
console.log(data);

similar in SQL Query

SELECT * FROM data4 
GROUP BY brand 
ORDER BY brand ASC;

Back to top

Select + Group By + Sum(stock) + Order By

var data = nosql.set(data4)
  .groupBy('brand',['stock'])
  .orderBy('brand')
  .select(['brand','stock','item_count','average_stock'])
  .exec();
console.log(data);

similar in SQL Query

SELECT brand, sum(stock) AS 'stock', item_count, average_stock 
FROM data4 
GROUP BY brand 
ORDER BY brand ASC;

Note:

  • select() method is optional, so you are able to put select() on first or tail before exec().
  • item_count and average_stock is added automatically when you are using groupBy with sumField.

Back to top

Group Detail Nested

This is for grouping data but the data detail will show as nested. This feature is not exists at mostly relation database engine, so I can not show you how the query is.

var data = nosql.set(data4).groupDetail('brand').exec();
console.log(data);

Back to top

Join Query

Join Merge Two Data Table

var data = nosql.set(data1).join('profile',data2)
  .merge('user_id','id')
  .exec();
console.log(data);

Back to top

Join Merge Multiple Data Table

var table = nosql.set(data1).join('profile',data2).merge('user_id','id').exec();
var data = nosql.set(table).join('bio',data3).merge('user_id','id').exec();
console.log(data);

Back to top

Join On Two Data Table

var data = nosql.set(data1).join('profile',data2).on('user_id','id').exec();
console.log(data);

Back to top

Join On Two Data Table As Array

var data = nosql.set(data1).join('profile',data2).on('user_id','id',false,false).exec();
console.log(data);

Note:

  • See at .on('user_id','id',false,false), the third argument is false which mean the joined data will return as array and the fourth argument is false which mean the joined data array will be filtered by value of 'user_id' and 'id' as case incensitive (fourth argument is work for array only). `
  • Join as Array is added since version 1.16.0.

Back to top

Join On Multiple Data Table

var profile = nosql.set(data1).join('profile',data2).on('user_id','id').exec();
var data = nosql.set(profile).join('bio',data3).on('user_id','id').exec();
console.log(data);

Back to top

Join On Multiple Nested Data Table

var bio = nosql.set(data2).join('bio',data3).on('id','id').exec();
var data = nosql.set(data1).join('data',bio).on('user_id','id').exec();
console.log(data);

Note:

  • JOIN in fly-json-odm will act like LEFT JOIN in SQL Query

Back to top

Advanced Query

We know that our query is limited, so for advanced query, you have to create your own custom function.
Below here is the very common case for query searching in a quite bit complicated json array.

const jsondata = [
  { id: 1, level: 'medium', team: [{ name: 'Giant' }, { name: 'suneo' }, { name: 'nobita' }] },
  { id: 3, level: 'hard', team: [{ name: 'giant2' }, { name: 'shizuka' }, { name: 'nobita' }] },
  { id: 5, level: 'easy', team: [{ name: 'nobita' }, { name: '2suneo' }] }
];

// We want to search team with name 'giant'.
// team value is an array with object, which is you can't just use IN LIKE or NOT IN LIKE.
const search = 'giant';

const nosql = new FlyJson();
const data = nosql.set(jsondata)
  .where('team', 'FUNCTION', function (value) {
    let found = false;
    for (let i = 0; i < value.length; i++) {
      if (value[i].name.toString().toLowerCase().indexOf(search) !== -1) {
        found = true;
      }
    }
    return found;
  })
  .exec();

Note:

  • You can not use IN LIKE or NOT IN LIKE query because that is not working if the array value is an object.
  • You can just use IN LIKE or NOT IN LIKE if the team is an array string or an object.

Back to top

Clone this wiki locally