Javascript rebuild a JSON array in another structure

Question

I have a JSON array result from an API get in this form:

var data = {
"metadata": [{
    "colIndex": 0,
    "colType": "String",
    "colName": "Territory"
}, {
    "colIndex": 1,
    "colType": "String",
    "colName": "Region"
}, {
    "colIndex": 2,
    "colType": "Numeric",
    "colName": "Market"
}, {
    "colIndex": 3,
    "colType": "Numeric",
    "colName": "Sales"
}],
"resultset": [
    ["Europe",    'France',     null,         100],        
    ["Europe",    'Germany',   150,       500],
    ["Europe",    'Austria',   250,       200],
    ["Europe",    'Poland',   220,      100],        
    ["Europe",    'Italy',      120,       200],
    ["Europe",    'Bulgaria',      40,      500],
    ["Europe",    'Romania',      450,      400]
]};

I want to transform this dataset in another structure:

var data = [{
"country": [
    ['France', null],
    ['Germany', 150],
    ['Austria', 250],
    ['Poland', 220],
    ['Italy', 120],
    ['Bulgaria', 40],
    [Romania', 450]
],
"name": "Market" // this is metadata.colName
},{
"country": [
    ['France', 100],
    ['Germany', 500],
    ['Austria', 200],
    ['Poland', 100],
    ['Italy', 200],
    ['Bulgaria', 500],
    [Romania', 400]
],
"name": "Sales" // this is metadata.colName
}]

I tried using some loop variation, but i gave up. Here is my last fiddle.

Thank you all for hints or answers.

Answer 1

You could use embedded .map() iterators to generate the outer Array from metadata, starting at the 3rd element, with each name and country from resultset within that:

var countryIndex = 1;
var startingIndex = 2;

// for each desired metadata, generate an object with name and
// a country collection of the respective resultset data
data = data.metadata.slice(startingIndex).map(function (meta) {
    return {
        country: data.resultset.map(function (result) {
            return [
                result[countryIndex],
                result[meta.colIndex]
            ];
        }),
        name: meta.colName
    };
});

console.log(data);

http://jsfiddle.net/n943m/

You can also create empty country Arrays first and use the iterator variables to find the country to .push() to:

var revised = [];
var countryIndex = 1;
var startingIndex = 2;

var dr = data.resultset;
var dm = data.metadata;

// initialize the name and country objects with an empty Array to fill
for (var j = startingIndex; j < dm.length; j++) {
    revised.push({
        country: [],
        name: dm[j].colName
    });
}

for (var i = 0; i < dr.length; i++) {
    // repeat the same loop used to initialize
    for (var j = startingIndex; j < dm.length; j++) {
        // subtract to start back at `0` for `revised`
        revised[j - startingIndex].country.push([
            dr[i][countryIndex],
            dr[i][ dm[j].colIndex ]
        ]);
    }
}

console.log(revised);

http://jsfiddle.net/krDXV/

Either should allow the collection to adjust to the number of columns included in metadata and resultset.

Answer 2

To convert your initial ajax data there are at least 2 ways:

1. Convert each resultset row to element with named properties and then convert to what you need.

2. Get collumn indexes of required collumns for extracting data from resultset and make new objects from these values.

Here is working example of second way:

var data = {
    "metadata": [{
        "colIndex": 0,
            "colType": "String",
            "colName": "Territory"
    }, {
        "colIndex": 1,
            "colType": "String",
            "colName": "Region"
    }, {
        "colIndex": 2,
            "colType": "Numeric",
            "colName": "Market"
    }, {
        "colIndex": 3,
            "colType": "Numeric",
            "colName": "Sales"
    }],
        "resultset": [
        ["Europe", 'France', null, 100],
        ["Europe", 'Germany', 150, 500],
        ["Europe", 'Austria', 250, 200],
        ["Europe", 'Poland', 220, 100],
        ["Europe", 'Italy', 120, 200],
        ["Europe", 'Bulgaria', 40, 500],
        ["Europe", 'Romania', 450, 400]
    ]
};


var rv = []; //Converted AJAX data.
var m_markets = [];
var m_sales = [];

var ind_region = 1;
var ind_market = 2; //Get index of "Market" collumn
var ind_sales = 3; //Get index of "Sales" collumn

if (Array.isArray(data.resultset)){
    data.resultset.forEach(function(e,i){
        m_markets.push([e[ind_region], e[ind_market]]);
        m_sales.push([e[ind_region], e[ind_sales]]);
    });
}

rv.push({
    country: m_markets,
    name: "Market"
});
rv.push({
    country: m_sales,
    name: "Sales"
});

console.log('Converted JSON: ', rv); // Look at console.

JsFiddle: http://jsfiddle.net/Cmbhx/1/