fordth/jinqjs

jinqJs provides a simple way to perform SQL like queries on javaScript arrays, collections and web services using LINQ expressions.

jinqJs

jinqJs provides a simple, leightweight & fast way to perform queries, updates and deletes on javaScript arrays, collections and web services using LINQ expressions. You can visit the jinqJs.com for more examples and demos.

TypeScript Support

Include the definition file jinqJs.d.ts

Unit Tested

All stable versions have had a 100% success rate with all 80+ unit tests.

Online Detailed API Documentation With Examples of Every Function

The full API documentation can be found here

Installing via the NuGet Package

You can start using jinqJs by simply using the NuGet package manager.

Install-Package jinqJs

Installing the jinq module by running:

npm install -g jinq

Installing jinqJs via Bower

bower install jinqjs

Extensibility

jinqJs has a simple plug-in architecture which allows you to simply add your own functions to the jinqJs library without modifying any of the base library code. View the online documentation Extensibility section for more information.

Node.js

As of version 1.3+ jinqJs is Node.js ready. You load the jinqJs module in Node.js via the following:

var jinqJs = require('jinq');

Angular 1.x Service

If angular 1.x is loaded then jinqJs is available as a service in the module 'angular-jinqjs':

angular.module('app', ['angular-jinqjs']).controller('demoCtrl', ['$jinqJs', function ($jinqJs) { 
    var result = $jinqJs.from(data).select();
}]);

Perform RUD Operations

Starting in version 1.5.1 you can perform update and delete operations.

Examples

Joining Results With A Web Service Response And A Collection

var people = [{Name: 'Tom', Age: 15, Location: 'Port Jefferson'},
                {Name: 'Jen', Age: 30, Location: 'Huntington'},
                {Name: 'Diana', Age: 5, Location: 'Huntington'}];
                
/* WEB SERVICE TO QUERY */
var weatherSvc = 'http://api.openweathermap.org/data/2.5/weather?q=Huntington,NY';
/* Sample use of a .select() predicate to flatten result */ 
/* Calling a web service to query the response           */
var weather = new jinqJs()
                .from(weatherSvc)
                .select( function(row){
                    return { Location: row.name, Condition: row.weather[0].description };
                });
                    
/* Performs a join on the web service response and local collection */
var result = new jinqJs()
                .from(people)
                .where( function(row) { return (row.Age > 3 && row.Location == 'Huntington'); } )
                .leftJoin(weather).on('Location')
                .groupBy('Huntington', 'Condition').avg('Age')
                .select([{field: 'Location'}, {field: 'Age', text: 'Average Age'}, {field: 'Condition'}]);

RESULT
|  Location  | Average Age |     Condition |
|:----------:|:-----------:|--------------:|
| Huntington |     17.5    | Partly Cloudy |

Performing An Asynchronous Web Service Call

new jinqJs()
    .from('http://www.telize.com/geoip',
        function(self) {
        var result = self
                        .top(1)
                        .select('city', 'region');
        }
    );

RESULT
|     city    |  region  |
|:-----------:|:--------:|
| Stony Brook | New York |

Using Predicates For Complex Outer Join & Where Condition

var people = [
              {Name: 'Jane', Age: 20, Location: 'Smithtown'},
              {Name: 'Ken', Age: 57, Location: 'Islip'},
              {Name: 'Tom', Age: 10, Location: 'Islip'}
            ];

var population = [
              {Location: 'Islip', People: 123},
              {Location: 'Melville', People: 332},
            ];
                                        
var result = new jinqJs()
    .from(people)
    .leftJoin(population)
      .on( function( left, right ) {
        return (left.Location === right.Location);
      } )
    .where( function(row) {
      return ( row.Age > 15);
    })
    .select('Name', 'Location', 'People');

RESULT
| Name |  Location | People |
|:----:|:---------:|--------|
| Jane | Smithtown |        |
| Ken  | Islip     |        |

Creating Calculated Columns

function isChild(row) {
  return (row.Age < 18 ? 'Yes' : 'No');
}
var people = [
              {Name: 'Jane', Age: 20, Location: 'Smithtown'},
              {Name: 'Ken', Age: 57, Location: 'Islip'},
              {Name: 'Tom', Age: 10, Location: 'Islip'}
            ];
                        
var result = new jinqJs()
    .from(people)
    .orderBy('Age')
    .select([{field: 'Name'}, 
            {field: 'Age', text: 'Your Age'}, 
            {text: 'Is Child', value: isChild}]);
}]);

RESULT
| Name | Your Age | Is Child |
|:----:|:--------:|----------|
| Tom  |    10    |    Yes   |
| Jane |    20    |    No    |
| Ken  |    57    |    No    |

Folder Structure

jinqJs/
├── demo/           <-- Demo code using angularJs to demonstrate some LINQ queries
│   ├── index.html
│   ├── README.md
│   ├── script.js
├── versions/           <-- A history of versions
│   ├── v.0.1
│   └── v.x.x
├── tests/              <-- Unit test using jasmine JS
│   └── index.html      <-- Home page for unit test results
├── jinqjs-unstable.js  <-- Current work in progress version
├── jinqjs.js           <-- Will always be the latest stable version
├── jinsjs.min.js       <-- Latest minified stable version
├── LICENSE
└── README.md

Bugs and Feature Requests

For comments, bugs or feature requests you can open an issue at New Issue.

Copyright and License

Code is released under the MIT license

jinqjs's Issues

.delete().at() does not really clear the data

When i use .delete().at() to clear the entire database, it appears to work. If i do a .select(), it returns a empty array. So far, so good.
But if i try to insert new data with a new .from(data_array), the new data gets appended to the old data which was suposed to be deleted. The old and the new data are returned by .select() which before the .from() showed a empty list.
I looked in the code and the collections private variable is never cleared, and the new data is added with .push().
The only way i found to really clear the table was using .join([]);. It works, but its a quirky workaround.
I am not sure if this is working as intended and im doing something wrong or its something that can be improved.

Creating new jinqJs instance for different queries on same base

Hi,

First of all thank you for such great lib, I really appreciate it.

The only drawback i found during usage is:
Let's say i want to apply different "filters" on initially filtered data. jinqJs needs to be created and filtered on initial state for all of such types of cases.

ex:
Let's say we have an array with 100 elements (rows), with date field inside.

First of all, I want to filter by month and assign it to some variable:

var year = 2017;
var month = 12;
var filtered_by_month = new jinqJs().from(DATA).where(function(row) {
    var date = row.date;
    return date.getFullYear() === year && date.getMonth() === month;
});

Now if i want to get for example: SUM and COUNT, at first glance it's easy, because:

var sum = filtered_by_month.sum('amount').select();
var count = filtered_by_month.sum('amount').select();

But, as it appears, after the first usage (when we did get sum) of jinqJs instance - filtered_by_month , jinqJs instance appears to be empty and second expression did not evaluate anything, it became null.

This happens not only in case of SUM or COUNT, If i apply some GROUPING to it, I can't apply other GROUPING on filtered_by_month instance.

For that, unusual reason, I am creating new jinqJs instances every time, with same initial filter, to get desired results at the end.

I wish i could query my data like this:

var year = 2017;
var month = 12;
var filtered_by_month = new jinqJs().from(DATA).where(function(row) {
    var date = row.date;
    return date.getFullYear() === year && date.getMonth() === month;
});

var sum = filtered_by_month.groupBy('user').sum('amount').select();
var count = filtered_by_month.groupBy('branch').count('amount').select();
...

Thanks for creating such a great library again :)

distinct() from array not args

I like your lib, i used it for my project, but when use distinct() function, i want input array parameters (it is variable) not args, because sometimes it is dynamic variable.
so i think in line 859, we can change it is

else {
                var argsDistinct = arguments;
                if (Array.isArray(arguments[0]))
                    argsDistinct = arguments[0];
                for (index = 0; index < len; index++) {
                    row = condenseToFields(result[index], argsDistinct);
                    for (var fieldIndex = 0; fieldIndex < argsDistinct.length; fieldIndex++) {

                        field = argsDistinct[fieldIndex];
                        if (!arrayItemFieldValueExists(collection, field, row[field])) {
                            collection.push(row);
                            break;
                        }
                    }
                }
            }

Thanks,
jinhduong

Problem function isObject, ...

Of course, thanks for your job !

When we want to check an object, it seems preferable to use "typeof".

isObject  = function (obj) {
  return (obj !== null && obj.constructor === Object);
}

to

isObject  = function (obj) {
  return (obj !== null && typeof obj === 'object');
}

aliases

This is a pretty cool library, looks really awesome.

I'm curious if there is any plan to support alias names? Right now data from multiple joins include the same field names, the result that is emitted for each row has everything clobbered together.

e.g.,

const data = {
  humans: [
    {id: 1, name: 'tyler'}
  ],
  dogs: [
    {dogId: 1, human: 1, name: 'sparky'},
    {dogId: 2, human: 1, name: 'spot'} 
  ]
}

new jinq()
  .from(data.humans)
  .join(data.dogs)
    .on((human, dog) => human.id === dog.human)
  .select()

// [
//   {id: 1, name: 'tyler', human: 1, dogId: 1}, 
//   {id: 1, name: 'tyler', human: 1, dogId: 2}
// ]

It would be really cool if I could do something like the following:

.select([
  {field: 'humans.name', text: 'human'},
  {field: 'dogs.name', text: 'dog'}   
])
// [
//   {human: 'tyler', dog: 'sparky'}
//   {human: 'tyler', dog: 'spot'}
// ]

Of course, if this was possible one should be able to reference these aliases in other clauses as well.

I'm not sure about the API, maybe something like this:

.from(data.humans).as('humans')
.join(data.dogs).as('dogs')

Looking through the code a bit, it wouldn't exactly be a trivial update to implement this, so feel free to close this ticket with extreme prejudice, but ... it would be pretty cool 😄

If not an Array of Objects

Hi,
context:

`
function GridRow(number, description) {
this.id = uuid();
this.number = number;
this.description = description;
///other properties not shown
}

var grid = [];
grid.push(new GridRow(12345, 'test desc'));

//This works
var result = new jinqJs()
.from(grid)
.select();
//This fails
var failedResult = new jinqJs()
.from(grid)
.select('id'); // or any combination of fields
`
It seems that it only accepts typeof 'object', not GridRow Object

Aggregate Functions (MAX, MIN) to mimic SQL

Code below not behaving like it does in SQL; inventory_date_time is created using javascript Date()
In SQL Date/Strings are able to do max/min aggregates
I do see that orderBy() works, maybe the sorting logic there could be applied in finding max/min with the groupBy()
return new jinqJs()
.from(springService.localmodelData.inventory)
.groupBy('company_sku_code', 'inventory_lot_key', 'inventory_uom')
.max('inventory_date_time')
.select()

By the way, is there a pattern that can be used to achieve the results I'm looking for?

Are those two fields need to transfer the position? rItem and lItem. jinqjs line544

jinqJs/jinqjs.js

Line 544 in 4fc27af

ret.push(mergeObjectsFields([rItem, lItem]));

Are those two fields need to transfer the places? rItem and lItem.
Here is a simple example

let items = [ { 'id': '8936b02f-e18c-4627-96e2-c61a568d98ac',
    'response': '' }];
let answers = [{"comment":"0000","id":"8936b02f-e18c-4627-96e2-c61a568d98ac","value":0}];
let ddd = new jinq().from(items).leftJoin(answers).on('id').select(question => {
                return {
                    id: question.id,
                    response: question.value,
                    comment: question.comment,
                    responseTime: question.currentTime
                };
            });

console.dir(ddd);
// [ { id: '8936b02f-e18c-4627-96e2-c61a568d98ac',
//     response: 0,
//     comment: '0000',
//     responseTime: undefined } ]

let items2 = [ { 'id': '8936b02f-e18c-4627-96e2-c61a568d98ac',
    'response': '',
    'comment': '3333' }];
let ddd2 = new jinq().from(items2).leftJoin(answers).on('id').select(question => {
                return {
                    id: question.id,
                    response: question.value,
                    comment: question.comment,
                    responseTime: question.currentTime
                };
            });

console.dir(ddd2);
// [ { id: '8936b02f-e18c-4627-96e2-c61a568d98ac',
//     response: 0,
//     comment: '3333',   //  <== here not changed
//     responseTime: undefined } ]

// expect result: 
// [ { id: '8936b02f-e18c-4627-96e2-c61a568d98ac',
//     response: 0,
//     comment: '0000',   //  <== expect change here
//     responseTime: undefined } ]

mergeObjectsFields method details:

        mergeObjectsFields = function (objects) {
            var obj = {};

            for (var index = 0; index < objects.length; index++) {
                for (var prop in objects[index]) {
                    obj[prop] = objects[index][prop];
                }
            }
            console.dir(obj);
            console.dir('_______________  finally _________________________');
            return obj;
        },

item change process:
rItem:

{ comment: '0000',
  id: '8936b02f-e18c-4627-96e2-c61a568d98ac',
  value: 0 }

lItem:
{ id: '8936b02f-e18c-4627-96e2-c61a568d98ac', response: '' }
mergeObjectsFields([rItem, lItem]): // <== line 544;

{ comment: '0000', // <== mergeObjectsFields method response
  id: '8936b02f-e18c-4627-96e2-c61a568d98ac',
  value: 0,
  response: '' }

item2 change process:
rItem:

{ comment: '0000',
  id: '8936b02f-e18c-4627-96e2-c61a568d98ac',
  value: 0 }

lItem:

{ id: '8936b02f-e18c-4627-96e2-c61a568d98ac',
  response: '',
  comment: '3333' }

mergeObjectsFields([rItem, lItem]): // <== line 544;

{ comment: '3333',   // <== mergeObjectsFields method response
  id: '8936b02f-e18c-4627-96e2-c61a568d98ac',
  value: 0,
  response: '' }

@fordth Could you please help me or any one explain this? Thanks.

Typings deployment

Is there a reason why Typescript typings are deployed in the same NuGet package as the JavaScript library and not through DefinitelyTyped?

Beside that, all DefinitelyTyped typings are installed in Scripts\typings\<library>\ folder and not in typings\<library>\.

For consistency with other libraries I would suggest to use also DefinitelyTyped for deploying the typing definitions and stick to their guidelines.

groupBy(), ignore case sensitivity

I think "groupBy()" should check if values are strings and if this is true then "case sensitivity" should be ignored.

Example:

[
{"country": "NL", value: 1},
{"country": "nl", value: 2},
{"country": "NL", value: 3},
]

at the moment there are "two results" because of "NL/nl"

isObject returning false on an object

I'm building an app with the Loopback and Angular libraries using a Mongo backend. When the client pulls datasets from the Loopback API there are multiple datasets with an "id" field. So I'm using jinqJs to (a) rename the id field so there isn't a collision and then (b) do a join on two datasets. The objects returned from the Loopback API are being retrieved using Loopbacks Angular service which effectively uses Angulars Resource object - so I have arrays of Resource objects.

When I pull the objects into jinqJs and use a .select() to rename the id field the isObject method returns false for the Resource objects so they aren't mapping correctly.

I changed the isObject code to use:

var type = typeof obj; return obj != null && (type == 'object' || type == 'function')

(Which I took from LoDash .isObject())

And now .isObject returns true as expected.

Ignore accents when ordering

Is it possible to have results ordered ignoring accented letters such as 'á'?

So instead of getting results like:
["ab", "h", "y", "áa"]

I want to get them ordered like this:
["áa", "ab", "h", "y"]

Bower support

Is Bower support in the Roadmap?

Removal of the Web Service feature

As I'm working through the testing of the system and moving towards more flexibility with the testing as well as full coverage I'm looking hard at the Web Service feature within jinqJs. While in simple cases I can see the need for the service - in production applications I would imagine it wouldn't be used much if at all. The reason I'm saying this is because more often all service calls will be abstracted away in order to provide consistent handling. This means that using the jinqJs web service function would create an outlier within the code base of an application. Additionally, the current implementations of the web service calls do not implement graceful handling of error conditions - therefore the implementations need to be augmented to handle these cases. At that point, the implementation of those methods becomes opinionated and may preclude the entire library from being used within an application.

I believe it would be cleaner to remove the functionality and simply require that users provide valid data to the library for the library to manipulate.

Node: Cannot find module 'jinq'

I do a install of jinq sudo npm install -g jinq and npm install jinq in both
set the variable var jinqJs = require('jinq');
, when i run my app node returns

Error: Cannot find module 'jinq' at Function.Module._resolveFilename (module.js:339:15) at Function.Module._load (module.js:290:25) at Module.require (module.js:367:17) at require (internal/module.js:16:19) at Object.<anonymous> (/var/www/html/JobAdminApi/src/models/ymlGenerator.js:18:14) at Module._compile (module.js:413:34) at Object.Module._extensions..js (module.js:422:10) at Module.load (module.js:357:32) at Function.Module._load (module.js:314:12) at Module.require (module.js:367:17)

I create a clean project only with var jinqJs = require('jinq'); and returns the same error.
The version is "jinq": "1.5.9",

Angular modularization

Do you plan to release a version of this which can be injected with Angular's module dependency model? Similar to what this contributor has done for the *other JS Linq library? https://github.com/Angular-Public/angular-linq

As I understand it, a huge benefit of Angular is the dependency injection and the cleaning up of the global namespace.

multiple aggregations at once

Hi,

Based on your API document that the current version does not support multiple aggregations at once and that you need to use inner join of two queries statement, can you provide an example of inner join for two queries results? An example of the output as below:

Location Dollar
A 5
B 3
A 2
C 1

Location Count Sum Avg
A 2 7 3.5
B 1 3 3
C 1 1 1

Thanks for your help.

.where missing string check which have "Awanish Kumar"

.where we can not pass a string which have space like i can not find where name = "Awanish Kumar "

how fast?

The library advertises itself as being fast, so I am curious if you have done any speed comparisons with libraries like lodash or crossfilter that can also be used for filtering/aggregating json data?

Why JinqJs code isn't working in jsfiddle?

I came across this cool library, going through the example, I clicked on "Edit in JsFiddle" , the new tab opened but when I try to run it nothing happens?

I checked console window it says "angular.merge is not a function"

Select where value is 0

Hi,

In select function this line,
obj[dstFieldName] = (isSimple ? result[index] : (result[index][srcFieldName] || null) );
What is this "|| null" for? It returns null when value is 0.

For example, this query new jinqJs().from([[0],[0],[1],[0],[2]]).select([{field:0,text:0}])
returns [[null],[null],[1],[null],[2]] where expected result is [[0],[0],[1],[0],[2]]

OrderBy has unexpected behaviour

Hi,

I tried to use the jinqJs library, but was confused why sorting was not done correctly. It seems that sorting based on string sorting, but if I use a number column for the orderBy criteria it failed.

Here is a jsfiddle example

_min not handling values of zero properly

During building tests for coverage I updated and changed the data that is used by the tests. (More data coverage is needed but that's a different issue.) I've found that the _min function will not handle values that are exactly zero.

In the code...

if (!hasProperty(minValue, key)) { minValue[key] = 0; }

if (minValue[key] === 0 || rValue < minValue[key]) { 
  minValue[key] = rValue;
}

In the _min function, the test for minValue having the current key sets the key if it not found and sets it to zero (presumably as a flag) BUT if the real value of the data is a zero the next statement will effectively wipe out this potentially zero minimum value. I'm going to re-write this section as:

if (!hasProperty(minValue, key)) || rValue < minValue[key]) { 
  minValue[key] = rValue;
}

This will ensure that if no minimum value exists the first value retrieved is assigned and the full range of potential values is covered.

I'm fixing this in my fork of jinqJs which I should be finishing soon and then plan to submit a PR

website examples

Hey, looks like a cool project.

1. On the examples page of the website, under Creating Calculated Columns, }]); is on the last line, but I think you want to remove that here because it is an artifact of an angular $scope you have in the real code, and the opening braces are not part of the example.
2. Maybe you could add the jinqJs javascript file to the jinqJs website, so people can play around with the examples in the console?
3. The API doc looks nice; maybe that could be added as html at some point?

Thanks.

Allow for None to Single/Many Joining

Allow for None to Single/Many Joining to be outputted after join.

Say I run the sample code:

    var people = [{
        Name: 'Jane',
        Age: 20,
        Location: 'Smithtown'
    }, {
        Name: 'Ken',
        Age: 57,
        Location: 'Islip'
    }, {
        Name: 'Tom',
        Age: 10,
        Location: 'Islip'
    }];

    var population = [{
        Location: 'Islip',
        People: 123
    }, {
        Location: 'Melville',
        People: 332
    }, ];

    var result = jinqJs()
        .from(people)
        .leftJoin(population)
        .on(function(left, right) {
            return (left.Location === right.Location);
        })
        .select();

The output is:

[{
    "Name": "Jane",
    "Age": 20,
    "Location": "",
    "People": ""
}, {
    "Location": "Islip",
    "People": 123,
    "Name": "Ken",
    "Age": 57
}, {
    "Location": "Islip",
    "People": 123,
    "Name": "Tom",
    "Age": 10
}]

However, I expect the output to be a merge of data sets:

[{
    "Name": "Jane",
    "Age": 20,
    "Location": "",
    "People": ""
}, {
    "Location": "Islip",
    "People": 123,
    "Name": "Ken",
    "Age": 57
}, {
    "Location": "Islip",
    "People": 123,
    "Name": "Tom",
    "Age": 10
},{
    "Location": "Melville",
    "People": 332,
    "Name": "",
    "Age": ""
}]

I have it working with lodash, but the solution is inefficient for large datasets.

Changing the jinqJs repo for various reasons

I had an email discussion with @fordth about working on the structure of the jinqJs repo to make it more flexible for testing (including regression testing) as well as adding some build scripts. I've forked the repo to work on the issue I raised ( #21 ) and the fork repo is where I'll be doing the work.

If you are interested in letting me know your thoughts you can reply here or start a ticket within my fork.

Here are my goals:

• restructure the project folders for src and distribution
• implement testing structures to allow easy jinqJs testing for full coverage
  • testing of Node module
  • testing of Angular 1.x service
  • testing of TypeScript typings definition file
• implement a build process using TravisCI
• ensure repo is structured for easy development in a collaborative environment

Once done I'll submit the work done as a PR here.

Sort failed if data is an empty string

(function() { 
  angular.module('app', ['ui.grid'])
                   .controller('demoCtrl', ['$scope', function ($scope) {
    
    var data1 = [ {Name: 'Tom', Age: 'a', Location: 'Port Jeff', Sex: 'Male'},
              {Name: 'Jen', Age: '', Location: 'Port Jeff', Sex: 'Female'},
              {Name: 'Tom', Age: 'b', Location: 'Port Jeff', Sex: 'Male'},
              {Name: 'Diana', Age: '', Location: 'Port Jeff', Sex: 'Female'} ];

    $scope.data = new jinqJs()
               .from(data1)
               .orderBy([{field: 'Age', sort: 'desc'}])
               .select('Name', 'Age', 'Sex');
  }]);  
}());

result:

'a'
''
'b'
''

https://jsfiddle.net/rongxike/r7Lc22ta/

To fix this error:

orderByComplex = function (complexFields) {
...
lValue = (field === null ? first : (isNaN(first[firstField]) || first[firstField] == '' ? first[firstField] : Number(first[firstField])));
                    rValue = (field === null ? second : (isNaN(second[secondField]) || second[secondField] == '' ? second[secondField] : Number(second[secondField])));
...
}