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
– Dai, Commented Nov 21, 2020 at 13:35

Mikkel · Accepted Answer · 2020-11-21 13:50:24Z

1

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

edited Nov 21, 2020 at 13:50

answered Nov 21, 2020 at 13:45

Mikkel

7,7773 gold badges20 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Shahnawaz Hossan · Accepted Answer · 2020-11-21 16:21:41Z

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, 2020 at 16:21

answered Nov 21, 2020 at 13:40

Shahnawaz Hossan

2,7062 gold badges15 silver badges24 bronze badges

2 Comments

Mikkel Over a year ago

This returns the matched keys, not the rows

Shahnawaz Hossan Over a year ago

@Mikkel, forgot to write names[name], updated. Thanks

pullidea-dev · Accepted Answer · 2020-11-21 13:53:49Z

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, 2020 at 13:53

answered Nov 21, 2020 at 13:44

pullidea-dev

1,7981 gold badge9 silver badges24 bronze badges

Comments

tim dowd · Accepted Answer · 2020-11-21 14:13:16Z

0

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
}, {})

answered Nov 21, 2020 at 14:13

tim dowd

1

Comments

3limin4t0r · Accepted Answer · 2020-11-21 14:51:14Z

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.

Collectives™ on Stack Overflow

JS: JSON Data query like SQL

5 Answers 5

Comments

2 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related