Skip to content

Query

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

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

Table of Contents

Example Data

All example at here 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}
];

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';

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 + 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 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

Clone this wiki locally