CODEWITHSUNDEEP
javascriptjson
Piyush Mahensaria
Piyush Mahensaria

Reputation: 303

Count no. of entries in a json object having specific values in multiple keys

Is there a way to fetch/search data from a json object, similar to firing a query, using javascript?

My scenario: I have a big json object (if you think of it like a database, it has 12 columns, and 1000 rows). I need to know how many rows have a specific value pair (i.e. number of rows having , 'USA' in column 1 and 'INDIA' in column 2).

Eg.(For example, I have created a small example json object)

json object:

{"export":
   [{"goods":"Wheat ", "from":"GHANA", "to":"AUSTRALIA"},    
    {"goods":"Wheat", "from":"USA", "to":"INDIA"},    
    {"goods":"Wheat", "from":"SINGAPORE", "to":"MALAYSIA"},    
    {"goods":"Wheat", "from":"USA", "to":"INDIA"},    
]}

In the example, since there are two rows where from is USA and to is INDIA.

It can be thought of similar to mysql query: 

select * from export where from = "USA" and to = "INDIA";

Can I do this on a json object using javascript ?

If not, can a similar thing be done from a csv file, I also have the csv file of that data?

Upvotes: 1

Views: 6130

Answers (5)

Nina Scholz
Nina Scholz

Reputation: 386540

This is a more detailed example for a structured query of structured data, with a bonus for sorting the result array.

It features a small function select(from, where, sort) with three parameters.

  • from: The data source as array, required.

  • where: An object with one ore more key/value pairs, if more than one is supplied, the conditions are with and chained. The parameter is ommitted if falsy.

  • sort: An array with key/value pair objects which sortes the result set with the key ascending with value 1, descending with value -1. The sort features a string sort. The parameter is ommitted if falsy.

The test case returns:

  1. Count of all sets with from = 'USA' and to = 'INDIA'.
  2. All sets with from = 'USA' and to = 'INDIA', not sorted.
  3. All sets sorted by from ascending.

var data = {
        "export": [
            { "goods": "Wheat ", "from": "GHANA", "to": "AUSTRALIA" },
            { "goods": "Wheat", "from": "USA", "to": "INDIA" },
            { "goods": "Wheat", "from": "SINGAPORE", "to": "MALAYSIA" },
            { "goods": "Wheat", "from": "USA", "to": "INDIA" }
        ]
    };

function select(from, where, sort) {
    var data = from.slice(0);
    where && Object.keys(where).forEach(function (k) {
        data = data.filter(function (d) {
            return d[k] === where[k];
        });
    });
    sort && data.sort(function (a, b) {
        var value = 0;
        sort.some(function (el) {
            var key = Object.keys(el)[0];
            value = ~el[key] ? a[key].localeCompare(b[key]) : b[key].localeCompare(a[key]);
            return value;
        });
        return value;
    });
    return data;
}

function print(o) {
    document.body.innerHTML += '<pre>' + JSON.stringify(o, 0, 4) + '</pre>';
}

print(select(data.export, { from: 'USA', to: 'INDIA' }).length);
print(select(data.export, { from: 'USA', to: 'INDIA' }));
print(select(data.export, {}, [{ from: 1 }]));

Upvotes: 0

Pranav C Balan
Pranav C Balan

Reputation: 115212

You can use filter() and length property. filter() will filter array based on our condition, for getting array count use length property.

var data = {
  "export": [{
    "goods": "Wheat ",
    "from": "GHANA",
    "to": "AUSTRALIA"
  }, {
    "goods": "Wheat",
    "from": "USA",
    "to": "INDIA"
  }, {
    "goods": "Wheat",
    "from": "SINGAPORE",
    "to": "MALAYSIA"
  }, {
    "goods": "Wheat",
    "from": "USA",
    "to": "INDIA"
  }, ]
};

var count = data.export.filter(function( v) {
  return v.from == 'USA' && v.to == 'INDIA'
}).length;

alert(count);

Upvotes: 0

Michael Rashkovsky
Michael Rashkovsky

Reputation: 156

This should work:

var a = {"export":
   [{"goods":"Wheat ", "from":"GHANA", "to":"AUSTRALIA"},    
    {"goods":"Wheat", "from":"USA", "to":"INDIA"},    
    {"goods":"Wheat", "from":"SINGAPORE", "to":"MALAYSIA"},    
    {"goods":"Wheat", "from":"USA", "to":"INDIA"},    
]};

var b = 0;

for(var i = 0; i < a.export.length; i++){
  if( a.export[i].from == 'USA' && a.export[i].to == 'INDIA'){
    b++;
  }
}

Upvotes: 0

Simon H
Simon H

Reputation: 21005

Yes, just filter the array to get the elements meeting your query, and count the length of what's left. Here's the code in a re-usuable functional form

function counter(f, t) {
  return json.export.filter(function(elem) {
    return elem.from===f && elem.to===t;
  }).length;
}

Upvotes: 1

dandavis
dandavis

Reputation: 16726

You can use filter to code an expression that will match more than one criteria:

o.export.filter(function(a){return a.from=="USA" && a.to=="INDIA"}).length

I like re-usable filter callbacks, which would let you change criteria at call time instead of code time:

function match(x){ return x[this[0]]===this[1]; }
//usage:
o.export.filter(match,["from", "USA"]).filter(match, ["to","INDIA"]).length;

the re-usable ones lets you set different columns and compare values without rewriting the function.

Upvotes: 0

Related Questions