JS: JSON Data query like SQL

Question

I have the following JSON data

{
  "pebble" {
     "status" : "active"
   },
  "stone" {
     "status" : "active"
   },
  "stone_ny" {
     "status" : "active"
   },
  "stone_london" {
     "status" : "active"
   },
  "stone_tokyo" {
     "status" : "active"
   }
}

In JS, is there a way to get all rows that match stone_.* that returns the last 3 rows?

Not SQL, but something resembling relational-calculus (or is it _relational-algebra_? gah...) exists in Lodash. But you can do this in 2 lines with various `Array.prototype` functions like `filter` and `map` - and a `RegExp`. — Dai, Nov 21 '20 at 13:35
See also: [JavaScript: filter() for Objects](https://stackoverflow.com/questions/5072136/javascript-filter-for-objects) — 3limin4t0r, Nov 21 '20 at 14:56

Shahnawaz Hossan · Answer 1 · 2020-11-21T16:21:41.270

1

Just use a simple regex to match stone keys by using /stone_/gi in this way:

var names = {
    "pebble": {
        "status": "active"
    },
    "stone": {
        "status": "active"
    },
    "stone_ny": {
        "status": "active"
    },
    "stone_london": {
        "status": "active"
    },
    "stone_tokyo": {
        "status": "active"
    }
}

var matchedNames = {};

for (name in names) {
    if (/stone_/gi.test(name)) {
        matchedNames[name] = names[name];
    }
}

console.log(matchedNames);

Explanation of regex:

g = global, match all instances of the pattern in a string, not just one
i = case-insensitive (so, for example, /a/i will match the string "a" or "A".

edited Nov 21 '20 at 16:21

answered Nov 21 '20 at 13:40

Shahnawaz Hossan

2,695
2
13
24

This returns the matched keys, not the rows – Mikkel Nov 21 '20 at 13:48
@Mikkel, forgot to write `names[name]`, updated. Thanks – Shahnawaz Hossan Nov 21 '20 at 13:51

Mikkel · Answer 2 · 2020-11-21T13:50:24.357

It's not as scary as @suchislife makes out. JSON data is inherently flexible in ways that SQL can't even begin to do. That's the reason why it has become so ubiquitous.

Let's say your data is in a variable called data

var data = {
    "pebble": {
        "status": "active"
    },
    "stone": {
        "status": "active"
    },
    "stone_ny": {
        "status": "active"
    },
    "stone_london": {
        "status": "active"
    },
    "stone_tokyo": {
        "status": "active"
    }
}
const result = Object.keys(data)
  .filter(key => key.match(/^stone_/) // This does the filtering (the WHERE clause)
  .map(key => { return {[key]: data[key]}}) // This returns your selected rows

pullidea-dev · Answer 3 · 2020-11-21T13:53:49.403

0

source={
  "pebble": {
     "status" : "active"
   },
  "stone": {
     "status" : "active"
   },
  "stone_ny" {
     "status" : "active"
   },
  "stone_london": {
     "status" : "active"
   },
  "stone_tokyo": {
     "status" : "active"
   }
};

rows = [];

for(var k in source)
 if(k.substr(0,6)=="stone_")
  rows.push({[k]:source[k]});

edited Nov 21 '20 at 13:53

answered Nov 21 '20 at 13:44

pullidea-dev

1,768
1
7
23

score 0 · Answer 4 · answered Nov 21 '20 at 14:13

var data = {
    "pebble": {
        "status": "active"
    },
    "stone": {
        "status": "active"
    },
    "stone_ny": {
        "status": "active"
    },
    "stone_london": {
        "status": "active"
    },
    "stone_tokyo": {
        "status": "active"
    }
}

const query = Object.entries(data).reduce((acc, val)=> {
  return val[0].slice(0, 6) === 'stone_' ? {...acc,  [val[0]]: val[1]} : acc
}, {})

3limin4t0r · Answer 5 · 2020-11-21T14:51:14.227

You can loop through all enumerable string properties (including inherited once) with for...in. You can combine this with a regex match to check if the key meets your requirements.

const data = {
  "pebble"      : { "status": "active" },
  "stone"       : { "status": "active" },
  "stone_ny"    : { "status": "active" },
  "stone_london": { "status": "active" },
  "stone_tokyo" : { "status": "active" },
};

const result = {};
for (const key in data) {
  if (key.match(/^stone_/)) result[key] = data[key];
}

console.log(result);

However if you are currently already using a library you could check if there is a helper present. A common name would be pickBy() which accepts the object and predicate (test function).

In Lodash:

const result = _.pickBy(data, (_, key) => key.match(/^stone_/));

In Ramda:

const result = R.pickBy((_, key) => key.match(/^stone_/), data);
// or: const result = R.pickBy(R.flip(R.test(/^stone_/)), data);

Some libraries might also use filter() and return an object if the input was an object.

JS: JSON Data query like SQL

5 Answers5