0

I have JSON data which is structured as below. Intension is to look up a specific datapoint, e.g. annual profit, which is 5000.

I want to do this by finding the column by name, e.g. "profit", identify the column index (3 in the example), and then use the column index to select the nth (3rd) element in the second node ("annual") of the "data" array.

How can I do this using the findIndex() function in Javascript (see the key part of my code below)?

JSON data:

{
  "datatable": {
    "data": [
      [
        "AAPL",
        "quarterly",
        1000,
        2000
      ],
      [
        "AAPL",
        "annual",
        5000,
        10000
      ]
    ],
    "columns": [{
        "name": "ticker"
        "type": "String"
      },
      {
        "name": "timedim"
        "type": "String"
      },
      {
        "name": "profit",
        "type": "Integer"
      },
      {
        "name": "revenue",
        "type": "Integer"
      }
    ]
  }
}

JavaScript code:

  // daten contains the "data" array of the JSON dataset
  // spalten contains the "columns" array of the JSON dataset
  
  var i = spalten.findIndex(obj => obj.name == "profit");
  output += '<p>Annual profit AAPL: ' + daten[i] + '</p>';
  elroot.innerHTML += output;

Improve this question

4 Answers 4

Reset to default
1

You have 2-dimensional array, so, you need two indexes:

const json = {
  "datatable": {
    "data": [
      [
        "AAPL",
        "quarterly",
        1000,
        2000
      ],
      [
        "AAPL",
        "annual",
        5000,
        10000
      ]
    ],
    "columns": [{
        "name": "ticker",
        "type": "String"
      },
      {
        "name": "timedim",
        "type": "String"
      },
      {
        "name": "profit",
        "type": "Integer"
      },
      {
        "name": "revenue",
        "type": "Integer"
      }
    ]
  }
}
var profitIndex = json.datatable.columns.findIndex(item => item.name == 'profit');
var annualIndex = json.datatable.data.findIndex(array => array.indexOf('annual') > -1);
var annualProfit = json.datatable.data[annualIndex][profitIndex];

If you need a function, it could look like below:

var getValueFromJson = function (json, columnName, dataMarker) {
    var columnIndex = json.datatable.columns.findIndex(item => item.name == columnName);
    var dataMarkerIndex = json.datatable.data.findIndex(array => array.indexOf(dataMarker) > -1);
    if (columnIndex < 0 || dataMarkerIndex < 0) {
        return null;
    }
    return json.datatable.data[dataMarkerIndex][columnIndex];
}

console.log(getValueFromJson(json, 'profit', 'quarterly'));
console.log(getValueFromJson(json, 'profit', 'annual'));
console.log(getValueFromJson(json, 'revenue', 'quarterly'));
console.log(getValueFromJson(json, 'revenue', 'annual'));

Above code prints:

> 1000
> 5000
> 2000
> 10000
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

Based on the JSON structure you've given, the following will work. Writing a function would be good if you want to get specific profit based on parameters.

  var output = ""
  function getProfit(type="annual", column=2) {
    var arrForType = yourData.datatable.data.find(arr => arr.indexOf(type) !== -1);
    return arrForType[column];
  }

  var i = yourData.datatable.columns.findIndex(obj => obj.name == "profit");
  output += '<p>Annual profit AAPL: ' + getProfit("annual", i) + '</p>';
  document.body.innerHTML += output;
Improve this answer

2 Comments

the function should be changed slightly, to type only without the ="annual"part, right? So it would be flexible, and I could then also using it with "quarterly".
No need, it is default parameter in case if you don't pass any value for that parameter. So, if you pass for e.g., getProfit("quarterly", 3), it would still work. Check default parameters for info.
0

You don't need findIndex - just use find and includes like so:

const data = {
  "datatable": {
    "data": [
      [
        "AAPL",
        "quarterly",
        1000,
        2000
      ],
      [
        "AAPL",
        "annual",
        5000,
        10000
      ]
    ],
    "columns": [{
        "name": "ticker",
        "type": "String"
      },
      {
        "name": "timedim",
        "type": "String"
      },
      {
        "name": "profit",
        "type": "Integer"
      },
      {
        "name": "revenue",
        "type": "Integer"
      }
    ]
  }
};

function findValue(type) {
  return data.datatable.data.find(e => e.includes(type))[2];
}

console.log(findValue("annual"));
console.log(findValue("quarterly"));

Improve this answer

3 Comments

Thanks. Where does this solution select the "profit" column?
It doesn't select the column - it just goes straight to the number.
Hi Jack, thanks. Let me rephrase my question: what if I want to flexibly search for a string within the column names, and then return the corresponding datapoint in data, I would need to reference that string somewhere, wouldn't I? Thanks for clarifying.
0

This is the basic idea, then if you need to scale obviously you'll need to do this in a nicer way.

let output = '';

// Searches the desired index (refactor as needed)
const index = spalten.findIndex(obj => obj.name == "profit")

// Extract all the profits (if you dont need all just select the desired one)
daten.map(item => output += `<p>${item[1]} profit ${item[0]}: ${item[index]}</p>`)
Improve this answer

1 Comment

@Ignacia what does the second line do exactly and how can I implement it into my code? thanks.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.