类似于Javascript对象的SQL“JOIN”?

基本上是这样的:

// first, build an easier lookup of author data:
var authormap = {};
authors.forEach(function(author) {authormap[author.id] = author;});

// now do the "join":
books.forEach(function(book) {
    book.author = authormap[book.author_id];
});

// now you can access:
alert(books[0].author.name);

你可以用 Alasql JavaScript SQL库:

var res = alasql('SELECT * FROM ? authors \
       LEFT JOIN ? books ON authors.id = books.author_id',[authors, books]);

尝试 this example 将数据保存在jsFiddle中。

此外,您还可以将CSV数据直接加载到SQL表达式中:

alasql('SELECT * FROM CSV("authors.csv", {headers:true}) authors \
            LEFT JOIN CSV("books.csv", {headers:true}) books \
            ON authors.id = books.author_id',[], function(res) {
      console.log(res);
 });

我也在寻找类似的东西,并使用了一些函数式编程来解决它。为了处理“NULL”情况,我冒昧地向初始数组中添加了几个对象。

var books = [
    {author_id: 1, title: 'Coloring for beginners'},
    {author_id: 1, title: 'Advanced coloring'},
    {author_id: 2, title: '50 Hikes in New England'},
    {author_id: 2, title: '50 Hikes in Illinois'},
    {author_id: 3, title: 'String Theory for Dummies'},
    {author_id: 5, title: 'Map-Reduce for Fun and Profit'}    
];
var authors = [
    {id: 1, name: 'adam'},
    {id: 2, name: 'bob'},
    {id: 3, name: 'charlie'},
    {id: 4, name: 'diane'}
];

所以现在你有一本没有作者的书,一个没有书的作者。我的解决方案如下:

var joined = books.map(function(e) {
    return Object.assign({}, e, authors.reduce(function(acc, val) {
        if (val.id == e.author_id) {
            return val
        } else {
            return acc
        }
    }, {}))
});

map方法遍历 books 使用 e 并返回其元素为的合并对象的数组 e ,其对应对象位于 authors 大堆 Object.assign({},a,b) 注意 merge 而不修改原始对象。

每个的对应对象 e 在里面 书 通过对 作者 大堆从空对象的初始值开始 {} (这是reduce的第二个论点-它也可能是空作者,例如 {id:'', name ''} )reduce方法包含以下元素 作者 使用 val 并返回以 acc 。当在两本书之间找到匹配项时 author_id 和作者的 id 整个匹配的author对象以 应收账款 最终由 authors.reduce(...) .

n.b.-使用reduce没有那么有效,因为 no way to break out of reduce loop 一旦找到匹配项,它将继续到数组的末尾

这是从@Niet的回答中得到的启发。

我遇到了重复数据的问题,所以我添加了之前在查找表中克隆记录的步骤 接合,接合 它与当前记录相匹配。

var authors = [{
    id: 1,
    name: 'adam'
}, {
    id: 2,
    name: 'bob'
}, {
    id: 3,
    name: 'charlie'
}];

var books = [{
    author_id: 1,
    title: 'Coloring for beginners'
}, {
    author_id: 1,
    title: 'Advanced coloring'
}, {
    author_id: 2,
    title: '50 Hikes in New England'
}, {
    author_id: 2,
    title: '50 Hikes in Illinois'
}, {
    author_id: 3,
    title: 'String Theory for Dummies'
}];

function joinTables(left, right, leftKey, rightKey) {

    rightKey = rightKey || leftKey;

    var lookupTable = {};
    var resultTable = [];
    var forEachLeftRecord = function (currentRecord) {
        lookupTable[currentRecord[leftKey]] = currentRecord;
    };

    var forEachRightRecord = function (currentRecord) {
        var joinedRecord = _.clone(lookupTable[currentRecord[rightKey]]); // using lodash clone
        _.extend(joinedRecord, currentRecord); // using lodash extend
        resultTable.push(joinedRecord);
    };

    left.forEach(forEachLeftRecord);
    right.forEach(forEachRightRecord);

    return resultTable;
}
var joinResult = joinTables(authors, books, 'id', 'author_id');
console.log(joinResult);

结果是

[
    {
        "id": 1,
        "name": "adam",
        "author_id": 1,
        "title": "Coloring for beginners"
    },
    {
        "id": 1,
        "name": "adam",
        "author_id": 1,
        "title": "Advanced coloring"
    },
    {
        "id": 2,
        "name": "bob",
        "author_id": 2,
        "title": "50 Hikes in New England"
    },
    {
        "id": 2,
        "name": "bob",
        "author_id": 2,
        "title": "50 Hikes in Illinois"
    },
    {
        "id": 3,
        "name": "charlie",
        "author_id": 3,
        "title": "String Theory for Dummies"
    }
] 

在这种情况下,我需要在两个数据集之间的一致键上连接两个数组,这一点略有不同。随着数据集的增长,JSON对象变得太笨重,因此将两个数组合并起来要容易得多:

 var data = new Array(["auth1","newbook1","pubdate1"],["auth2","newbook2","pubdate2"]);
 var currData = new Array(["auth1","newbook3","pubdate3"],["auth2","newbook3","pubdate4"]);
 var currDataMap = currData.map(function(a){return a[0];});
 var newdata = new Array();
 for(i=0;i<data.length;i++){
   if(currDataMap.indexOf(data[i][0])>-1){
     newdata[i] = data[i].concat(currData[currDataMap.indexOf(data[i][0])].slice(1));
  }
}

输出:

[
   [auth1, newbook1, pubdate1, newbook3, pubdate3], 
   [auth2, newbook2, pubdate2, newbook3, pubdate4]
]

在我的例子中,我还需要删除没有新数据的行,因此您可能需要排除条件。

这不是最优雅的代码,但我认为如果需要,遵循/破解非常简单。允许INNER、LEFT和RIGHT连接。

您只需将函数复制并粘贴到代码中即可获得正确的输出。示例位于底部

function remove_item_from_list(list_, remove_item, all = true) {
  /*
  Removes all occurrences of remove_item from list_
  */
    for (var i = list_.length; i--;) {
        if (list_[i] === remove_item) {
            list_.splice(i, 1);
        }
    }
    return list_
}

function add_null_keys(dict_, keys_to_add){
  /*
  This function will add the keys in the keys_to_add list to the dict_ object with the vall null

  ex: 
  dict_ = {'key_1': 1, 'key_2': 2}
  keys_to_add = ['a', 'b', 'c']

  output:
  {'key_1': 1, 'key_2': 2, 'a': NULL, 'b': NULL', 'c':'NULL'}
  */

  //get the current keys in the dict
  var current_keys = Object.keys(dict_)
  for (index in keys_to_add){
    key = keys_to_add[index]
    //if the dict doesnt have the key add the key as null
    if(current_keys.includes(key) === false){
      dict_[key] = null
    }
  }
  return dict_
}

function merge2(dict_1, dict_2, on, how_join){
  /*
  This function is where the actual comparison happens to see if two dictionaries share the same key

  We loop through the on_list to see if the various keys that we are joining on between the two dicts match.

  If all the keys match we combine the dictionaries.

  If the keys do not match and it is an inner join, an undefined object gets returned
  If the keys do not match and it is NOT an inner join, we add all the key values of the second dictionary as null to the first dictionary and return those
  */

  var join_dicts = true

  //loop through the join on key
  for (index in on){
    join_key = on[index]

    //if one of the join keys dont match, then we arent joining the dictionaries
    if (dict_1[join_key] != dict_2[join_key]){
      join_dicts = false
      break
    }
  }

  //check to see if we are still joining the dictionaries
  if (join_dicts === true){
    return Object.assign({}, dict_1, dict_2);
  }

  else{
    if (how_join !== 'inner'){
      //need to add null keys to dict_1, which is acting as the main side of the join
      var temp = add_null_keys(dict_1, Object.keys(dict_2))
      return temp
    }
  }
}

function dict_merge_loop_though(left_dict, right_dict, on, how_join){
  /*
  This function loops through the left_dict and compares everything in it to the right_dict

  it determines if a join happens, what the join is and returns the information

  Figuring out the left/right joins were difficult. I had to add a base_level dict to determine if there was no join
  or if there was a join...its complicated to explain
  */

  var master_list = []
  var index = 0

  //need to loop through what we are joining on 
  while(index < left_dict.length){
    //grab the left dictionary
    left_dict_ = left_dict[index]
    var index2 = 0

    //necessary for left/right join
    var remove_val = add_null_keys(left_dict_, Object.keys(right_dict[index2]))
    var temp_list = [remove_val]

    while (index2 < right_dict.length){
      //get the right dictionary so we can compete each dictionary to each other
      right_dict_ = right_dict[index2]

      //inner join the two dicts
      if (how_join === 'inner'){
        var temp_val = merge2(left_dict_, right_dict_, on, how_join)

        //if whats returned is a dict, add it to the master list
        if (temp_val != undefined){
          master_list.push(temp_val)
        }
      }

      //means we are right/left joining
      else{

        //left join the two dicts
        if (how_join === 'left'){
          var temp_val = merge2(left_dict_, right_dict_, on, how_join)
        }

        //right join the two dicts
        else if (how_join === 'right'){
          var temp_val = merge2(right_dict_, left_dict_, on, how_join)
        }
        temp_list.push(temp_val)
      }

      //increment this guy
      index2++
    }


    //Logic for left/right joins to for what to add to master list
    if (how_join !== 'inner'){
      // remove the remove val from the list. All that remains is what should be added
      //to the master return list. If the length of the list is 0 it means that there was no
      //join and that we should add the remove val (with the extra keys being null) to the master
      //return list
      temp_list = remove_item_from_list(temp_list, remove_val)

      if (temp_list.length == 0){
        master_list.push(remove_val)
      }
      else{
        master_list = master_list.concat(temp_list); 
      }
    }
    //increment to move onto the next thing
    index++

  }

  return master_list
}

function merge(left_dict, right_dict, on = [], how = 'inner'){
  /*
  This function will merge two dictionaries together
  You provide a left dictionary, a right dictionary, a list of what key to join on and 
  what type of join you would like to do (right, left, inner)

  a list of the merged dictionaries is returned
  */

  //get the join type and initialize the master list of dictionaries that will be returned
  var how_join = how.toLowerCase()
  var master_list = []

  //inner, right, and left joins are actually pretty similar in theory. The only major difference between
  //left and right joins is the order that the data is processed. So the only difference is we call the
  //merging function with the dictionaries in a different order
  if (how_join === 'inner'){
    master_list = dict_merge_loop_though(left_dict, right_dict, on, how_join)
  }

  else if (how_join === 'left'){
    master_list = dict_merge_loop_though(left_dict, right_dict, on, how_join)
  }

  else if (how_join === 'right'){
    master_list = dict_merge_loop_though(right_dict, left_dict, on, how_join)
  }

  else{
    console.log('---- ERROR ----')
    console.log('The "how" merge type is not correct. Please make sure it is either "inner", "left" or "right"')
    console.log('---- ERROR ----')
  }

  return master_list
} 

/*
-------------------- EXAMPLE --------------------
var arr1 = [
    {'id': 1, 'test': 2, 'text':"hello", 'oid': 2},
    {'id': 1, 'test': 1, 'text':"juhu", 'oid': 3},
    {'id': 3, 'test': 3, 'text':"wohoo", 'oid': 4},
    {'id': 4, 'test': 4, 'text':"yeehaw", 'oid': 1}
];

var arr2 = [
    {'id': 1,'test': 2, 'name':"yoda"},
    {'id': 1,'test': 1, 'name':"herbert"},
    {'id': 3, 'name':"john"},
    {'id': 4, 'name':"walter"},
    {'id': 5, 'name':"clint"}
];

var test = merge(arr1, arr2, on = ['id', 'test'], how = 'left')
for (index in test){
  dict_ = test[index]
  console.log(dict_)
}

*/