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 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.
- GroupDetail

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);
- Join Merge
  • Merge two data table
var data = nosql.set(data1).join('profile',data2)
  .merge('user_id','id')
  .exec();
console.log(data);
  • 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);
- Join On
  • Join on two data table
var data = nosql.set(data1).join('profile',data2).on('user_id','id').exec();
console.log(data);
  • 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);
  • 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
Clone this wiki locally