Finding a column value where columnID equals xxxx in JSON using C#

Question

I am using C# to grab values from a JSON file and put them into a database. I am new to JSON and so don't really know too much of how to work with it. I will do my best to explain the structure I have and what I am trying to do. But to sum up I am trying to call a variable from JSON in a similar way you would do in an SQL Where statement. So grab this value where this other value = x.

So the JSON format is as follows. Firstly it idenfitifies the columns.

"columns": [
    {
      "id": 8098453499733892,
      "index": 0,
      "title": "Task Name",
      "type": "TEXT_NUMBER",
      "format": ",,,,,,2,,,,,,,,,1",
      "primary": true,
      "width": 378
    },
    {
      "id": 780104105256836,
      "index": 1,
      "title": "KPI (RYG)",
      "type": "PICKLIST",
      "symbol": "RYG",
      "options": [
        "Red",
        "Yellow",
        "Green"
      ],.....

Then the rows and cells within that:

"id": 2157159933863812,
      "rowNumber": 2,
      "parentRowNumber": 1,
      "parentId": 7786659468076932,
      "expanded": false,
      "format": ",,1,,,,,,,22,,,,,,",
      "createdAt": "2015-03-04T15:58:28+13:00",
      "modifiedAt": "2015-03-04T15:58:32+13:00",
      "cells": [
        {
          "columnId": 8098453499733892,
          "type": "TEXT_NUMBER",
          "value": "GLH Toll MASTER FROM WEEK 47",
          "displayValue": "GLH Toll MASTER FROM WEEK 47",
          "format": ",,1,,,,2,,,22,,,,,,1"
        },
        {
          "columnId": 2750428942231428,
          "type": "CHECKBOX",
          "value": true,
          "format": ",,1,,,,,,,22,,,,,,"
        },

Ok so for an example. What I want to do is grab the columnID value in the cell, and then use that to find the title value in the column. So in the above example, I would want to find 'title' where id in column equals 8098453499733892, which would give me the result of 'Task Name'.

I don't even know if this is possible but have struggled to find a working example on the web. FYI I am using smartsheets.

You can Deserialize it to specific object then find the title by LINQ expression — Peyman, Mar 13 '15 at 02:37

score 1 · Answer 1 · answered Mar 13 '15 at 03:05

You need to create an equivalent object/class in your c# containing those properties in your JSON. In order to get the columnID column based on your JSON structure, you need to create the the class below:

class Class1 {
     public string id { get; set; }
     public int rowNumber { get; set; }
     public int parentId { get; set; }
     public bool expanded { get; set; }
     public string format{ get; set; }
     public List <Cells> cells {get; set;}
}

class Cells {
  public string columnId {get; set;}
  public string type{get; set;}
  public bool value{get; set;}
  public string format {get; set;}

}

You need to deserialize the JSON string from the API using the Class1 object and from there you can access whatever column that you need.

This was helpful but didn't really answer my question. I am trying to get the cell value where the column ID is equal to a specific ID. — Danrex, Mar 17 '15 at 01:49

score 0 · Accepted Answer · answered Mar 17 '15 at 04:13

For anyone looking I used the where clause in the select token. Here is the code that I used to find the column title based on an id value.

var test = jObject.SelectToken("columns").Where(t => t["id"].Value<Int64>() == 5283703732627332).FirstOrDefault()["title"].Value<string>();

Finding a column value where columnID equals xxxx in JSON using C#

2 Answers2