javascript JSON 数据上的 SQL 样式 JOIN
声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow
原文地址: http://stackoverflow.com/questions/7251163/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL style JOIN on JSON data
提问by Luke The Obscure
Is there any way efficiently to join JSON data? Suppose we have two JSON datasets:
有什么方法可以有效地连接 JSON 数据?假设我们有两个 JSON 数据集:
{"COLORS":[[1,red],[2,yellow],[3,orange]]}
{"FRUITS":[[1,apple],[2,banana],[3,orange]]}
And I want to turn this into the following client side:
我想把它变成以下客户端:
{"NEW_FRUITS":[[1,apple,red],[2,banana,yellow],[3,orange,orange]]}
Keep in mind there will be thousands of records here with much more complex data structures. jQuery and vanilla javascript are both fine. Also keep in mind that there may be colors without fruits and fruits without colors.
请记住,这里将有数千条数据结构复杂得多的记录。jQuery 和 vanilla javascript 都很好。还要记住,可能有没有水果的颜色和没有颜色的水果。
NOTE: For the sake of simplicity, let's say that the two datasets are both in the same order, but the second dataset may have gaps.
注意:为简单起见,假设两个数据集的顺序相同,但第二个数据集可能有间隙。
采纳答案by ShankarSangoli
There is not a direct way, but you can write logic to get a combined object like this. Since "apple, red, banana...." are all strings, they should be wrapped in a single or double quote.
没有直接的方法,但是您可以编写逻辑来获得这样的组合对象。由于“apple、red、banana....”都是字符串,所以应该用单引号或双引号括起来。
If you can match the COLORS and FRUITS config array by adding null values for missing items then you can use this approach.
如果您可以通过为缺失项添加空值来匹配 COLORS 和 FRUITS 配置数组,那么您可以使用这种方法。
Working demo
工作演示
var colors = {"COLORS":[[1,'red'],[2,'yellow'],[3,'orange']]}
var fruits = {"FRUITS":[[1,'apple'],[2,'banana'],[3,'orange']]}
var newFruits = {"NEW_FRUITS": [] }
//Just to make sure both arrays are the same size, otherwise the logic will break
if(colors.COLORS.length == fruits.FRUITS.length){
var temp;
$.each(fruits.FRUITS, function(i){
temp = this;
temp.push(colors.COLORS[i][2]);
newFruits.NEW_FRUITS.push(temp);
});
}
Alternatively, if you can create colors
and fruits
configs as an array of objects, instead of an array of arrays, you can try this solution. The sequence of the elements is irrelevant here, but the array size should still match.
或者,如果您可以将创建colors
和fruits
配置为对象数组,而不是数组数组,则可以尝试此解决方案。元素的顺序在这里无关紧要,但数组大小仍应匹配。
Working demo
工作演示
var colors = {"COLORS":[ {"1": 'red'}, { "2": 'yellow'}, {"3":'orange'}]}
var fruits = {"FRUITS":[ {"1":'apple'}, { "2": 'banana'}, {"3":'orange'}]}
var newFruits = {"NEW_FRUITS": [] }
if(colors.COLORS.length == fruits.FRUITS.length){
var temp, first;
$.each(fruits.FRUITS, function(i){
for(first in this)break;
temp = {};
temp[first] = [];
temp[first].push(this[first]);
temp[first].push(colors.COLORS[i][first]);
newFruits.NEW_FRUITS.push(temp);
});
}
回答by Ray Toal
The fact that there will be thousands of inputs and the keys are not necessarily ordered means your best bet (at least for large objects) is to sort by key first. For objects of size less than about 5 or so, a brute-force n^2 approach should suffice.
将有数千个输入并且键不一定是有序的这一事实意味着您最好的选择(至少对于大对象)是首先按键排序。对于大小小于 5 左右的对象,蛮力 n^2 方法就足够了。
Then you can write out the result by walking through the two arrays in parallel, appending new "records" to your output as you go. This sort-then-merge idea is a relatively powerful one and is used frequently. If you do not want to sort first, you can add elements to a priority queue, merging as you go. The sort-then-merge approach is conceptually simpler to code perhaps; if performance matters you should do some profiling.
然后,您可以通过并行遍历两个数组来写出结果,并随时将新的“记录”附加到您的输出中。这种先排序然后合并的想法是一个比较强大的想法,并且经常被使用。如果您不想先排序,您可以将元素添加到优先级队列中,随时合并。排序然后合并的方法在概念上可能更易于编码;如果性能很重要,你应该做一些分析。
For colors-without-fruits and fruits-without-colors, I assume writing null
for the missing value is sufficient. If the same key appears more than once in either color or fruit, you can either choose one arbitrarily, or throw an exception.
对于没有水果的颜色和没有颜色的水果,我认为写入null
缺失值就足够了。如果相同的键在颜色或水果中出现多次,您可以任意选择一个,也可以抛出异常。
ADDENDUMI did a fiddle as well: http://jsfiddle.net/LuLMz/. It makes no assumptions on the order of the keys nor any assumptions on the relative lengths of the arrays. The only assumptions are the names of the fields and the fact that each subarray has two elements.
附录我也做了一个小提琴:http: //jsfiddle.net/LuLMz/。它不对键的顺序做任何假设,也不对数组的相对长度做任何假设。唯一的假设是字段的名称以及每个子数组有两个元素的事实。
回答by agershun
AlasqlJavaScript SQL library does exactly what you need in one line:
AlasqlJavaScript SQL 库在一行中完全满足您的需求:
<script src="alasql.min.js"></script>
<script>
var data = { COLORS: [[1,"red"],[2,"yellow"],[3,"orange"]],
FRUITS: [[1,"apple"],[2,"banana"],[3,"orange"]]};
data.NEW_FRUITS = alasql('SELECT MATRIX COLORS.[0], COLORS.[1], FRUITS.[1] AS [2] \
FROM ? AS COLORS JOIN ? AS FRUITS ON COLORS.[0] = FRUITS.[0]',
[data.COLORS, data.FRUITS]);
</script>
You can play with this examplein jsFiddle.
您可以在 jsFiddle 中使用此示例。
This is a SQL expression, where:
这是一个 SQL 表达式,其中:
- SELECT - select operator
- MATRIX - modifier, whci converts resultset from array of objects to array of arrays
- COLORS.[0] - first column of COLORS array, etc.
- FRUITS.1AS 2- the second column of array FRUITS will be stored as third column in resulting recordset
- FROM ? AS COLORS - data array from parameters named COLORS in SQL statement
- JOIN ? ON ... - join
- [data.COLORS, data.FRUITS] - parameters with data arrays