none
How to pull values from items in JSON without indexing? RRS feed

  • Question

  • I have the following code that extracts json elements values and outputs to csv:

    public static void Json_to_Csv(string jsonInputFile, string csvFile)
    {
        using (var p = new ChoJSONReader(jsonInputFile).WithJSONPath("$..readResults")) // "readResults": [
        {
            using (var w = new ChoCSVWriter(csvFile).WithFirstLineHeader())
            {
                w.Write(p
                    .Select(r1 =>
                    {
                        var lines = (dynamic[])r1.lines;
                        return new
                        {
                            FileName = jsonInputFile,
                            Page = r1.page,
                            PracticeName = lines[2].text,
                            OwnerFullName = lines[4].text,
                            OwnerEmail = lines[6].text,
                        };
                    }
            }
        }
    }

    csv output:

    File Name,Page,Practice Name,Owner Full Name,Owner Email
    file1.json,1,Some Practice Name,Bob Lee,Bob@gmail.com

    Currently there is no other contextual information on each item to reference them so the only way is by indexing, e.g. lines[2]

    This works for now but I may have other JSON files that have an extra field, therefore the values pulled will be wrong.

    In order to address this scenario, how can i pull the values contextually instead of indexing the lines?

    Ive tried 

    PracticeName = lines["Practice Name"].text

     but i get Cannot implicitly convert type string to int error

    file1.json sample:


    {
      "status": "succeeded",
      "createdDateTime": "2020-10-22T19:35:35Z",
      "lastUpdatedDateTime": "2020-10-22T19:35:36Z",
      "analyzeResult": {
        "version": "3.0.0",
        "readResults": [
          {
            "page": 1,
            "angle": 0,
            "width": 8.5,
            "height": 11,
            "unit": "inch",
            "lines": [          
              {
                "boundingBox": [
                  0.5016,
                  1.9141,
                  2.5726,
                  1.9141,
                  2.5726,
                  2.0741,
                  0.5016,
                  2.0741
                ],           
               "text": "Account Information",
                "words": [
                  {
                    "boundingBox": [
                      0.5016,
                      1.9345,
                      1.3399,
                      1.9345,
                      1.3399,
                      2.0741,
                      0.5016,
                      2.0741
                    ],
                    "text": "Account",
                    "confidence": 1
                  },
                  {
                    "boundingBox": [
                      1.3974,
                      1.9141,
                      2.5726,
                      1.9141,
                      2.5726,
                      2.0741,
                      1.3974,
                      2.0741
                    ],
                    "text": "Information",
                    "confidence": 1
                  }
                ]
              },
              {
                "boundingBox": [
                  1.7716,
                  2.4855,
                  2.8793,
                  2.4855,
                  2.8793,
                  2.6051,
                  1.7716,
                  2.6051
                ],
                "text": "Practice Name",
                "words": [
                  {
                    "boundingBox": [
                      1.7716,
                      2.4855,
                      2.3803,
                      2.4855,
                      2.3803,
                      2.6051,
                      1.7716,
                      2.6051
                    ],
                    "text": "Practice",
                    "confidence": 1
                  },
                  {
                    "boundingBox": [
                      2.4362,
                      2.4948,
                      2.8793,
                      2.4948,
                      2.8793,
                      2.6051,
                      2.4362,
                      2.6051
                    ],
                    "text": "Name",
                    "confidence": 1
                  }
                ]
              },
              {
                "boundingBox": [
                  2.9993,
                  2.5257,
                  4.7148,
                  2.5257,
                  4.7148,
                  2.714,
                  2.9993,
                  2.714
                ],
                "text": "Some Practice Name",
                "words": [
                  {
                    "boundingBox": [
                      3.0072,
                      2.5385,
                      3.6546,
                      2.5284,
                      3.6516,
                      2.7131,
                      3.0105,
                      2.712
                    ],
                    "text": "Some",
                    "confidence": 0.984
                  },
                  {
                    "boundingBox": [
                      3.6887,
                      2.5281,
                      4.2112,
                      2.5262,
                      4.2028,
                      2.7159,
                      3.6854,
                      2.7132
                    ],
                    "text": "Parctice",
                    "confidence": 0.986
                  },
                  {
                    "boundingBox": [
                      4.2453,
                      2.5263,
                      4.7223,
                      2.5297,
                      4.7091,
                      2.72,
                      4.2366,
                      2.7161
                    ],
                    "text": "Name",
                    "confidence": 0.986
                  }
                ]
              },
              {
                "boundingBox": [
                  1.6116,
                  2.9999,
                  2.8816,
                  2.9999,
                  2.8816,
                  3.1158,
                  1.6116,
                  3.1158
                ],
                "text": "Owner Full Name",
                "words": [
                  {
                    "boundingBox": [
                      1.6116,
                      3.0039,
                      2.1026,
                      3.0039,
                      2.1026,
                      3.1157,
                      1.6116,
                      3.1157
                    ],
                    "text": "Owner",
                    "confidence": 1
                  },
                  {
                    "boundingBox": [
                      2.1541,
                      2.9999,
                      2.3784,
                      2.9999,
                      2.3784,
                      3.1158,
                      2.1541,
                      3.1158
                    ],
                    "text": "Full",
                    "confidence": 1
                  },
                  {
                    "boundingBox": [
                      2.4384,
                      3.0052,
                      2.8816,
                      3.0052,
                      2.8816,
                      3.1155,
                      2.4384,
                      3.1155
                    ],
                    "text": "Name",
                    "confidence": 1
                  }
                ]
              },
              {
                "boundingBox": [
                  2.9993,
                  3.0242,
                  3.6966,
                  3.0242,
                  3.6966,
                  3.2125,
                  2.9993,
                  3.2014
                ],
                "text": "Bob Lee",
                "words": [
                  {
                    "boundingBox": [
                      3.0063,
                      3.0303,
                      3.3439,
                      3.0349,
                      3.3461,
                      3.2125,
                      3.007,
                      3.2081
                    ],
                    "text": "Bob",
                    "confidence": 0.987
                  },
                  {
                    "boundingBox": [
                      3.3788,
                      3.0349,
                      3.6931,
                      3.0326,
                      3.697,
                      3.2121,
                      3.3813,
                      3.2125
                    ],
                    "text": "Lee",
                    "confidence": 0.983
                  }
                ]
              },
              {
                "boundingBox": [
                  1.945,
                  3.5063,
                  2.8748,
                  3.5063,
                  2.8748,
                  3.6261,
                  1.945,
                  3.6261
                ],
                "text": "Owner Email",
                "words": [
                  {
                    "boundingBox": [
                      1.945,
                      3.5143,
                      2.4359,
                      3.5143,
                      2.4359,
                      3.6261,
                      1.945,
                      3.6261
                    ],
                    "text": "Owner",
                    "confidence": 1
                  },
                  {
                    "boundingBox": [
                      2.4874,
                      3.5063,
                      2.8748,
                      3.5063,
                      2.8748,
                      3.6259,
                      2.4874,
                      3.6259
                    ],
                    "text": "Email",
                    "confidence": 1
                  }
                ]
              },
              {
                "boundingBox": [
                  3.0104,
                  3.5005,
                  4.6042,
                  3.5005,
                  4.6042,
                  3.6888,
                  3.0104,
                  3.6777
                ],
                "text": "bob@gmail.com",
                "words": [
                  {
                    "boundingBox": [
                      3.0212,
                      3.5047,
                      4.5837,
                      3.5039,
                      4.5769,
                      3.6886,
                      3.0129,
                      3.6787
                    ],
                    "text": "bob@gmail.com",
                    "confidence": 0.951
                  }
                ]
              }
            ]
          }
        ]
      }
    }


    Friday, October 23, 2020 11:15 PM

Answers

  • Hi cataster,

    Judging from the results of my experiment, yes.

    Your initial code is the only way.

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by cataster Monday, October 26, 2020 2:59 AM
    Monday, October 26, 2020 2:41 AM

All replies

  • Hello,

    In this forum we don't support outside libraries such as Cinchoo (assuming this is what is being used)


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Friday, October 23, 2020 11:28 PM
    Moderator
  • Hello,

    In this forum we don't support outside libraries such as Cinchoo (assuming this is what is being used)


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Oh thanks for letting ,e know Karen. I guess if there is another way to read from the JSON file and generate the fields/values that would be great as well. 

    Friday, October 23, 2020 11:30 PM
  • Hi cataster,

    Thank you for posting here.

    I tried to solve this problem but found some problems with the existing JSON.

    Under normal circumstances, we will have a property, and then give it a value, like this:

            public string Test { get; set; } 
            public Program() 
            {
                Test = "Test";
            }

    Then we can get the value based on the property name in other places.

    But in this json, "Owner Full Name" and "Bob Lee" are not the relationship between property and value, but the values ​​of Text property in two unrelated objects, like this:

        public class Line
        {
            public float[] BoundingBox { get; set; }
            public string Text { get; set; }
            public Word[] Words { get; set; }
        }
       
        ***********************
    
        new Line() { Text = "Owner Full Name" };
        new Line() { Text = "Bob Lee" };

    We can't establish a connection between them, except to specify manually as in your original code.

    Therefore, please consider reconstructing a qualified JSON before attempting to import it into a csv file.

    Best Regards,

    Timon 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, October 26, 2020 2:20 AM
  • Hi cataster,

    Thank you for posting here.

    I tried to solve this problem but found some problems with the existing JSON.

    Under normal circumstances, we will have a property, and then give it a value, like this:

            public string Test { get; set; } 
            public Program() 
            {
                Test = "Test";
            }

    Then we can get the value based on the property name in other places.

    But in this json, "Owner Full Name" and "Bob Lee" are not the relationship between property and value, but the values ​​of Text property in two unrelated objects, like this:

        public class Line
        {
            public float[] BoundingBox { get; set; }
            public string Text { get; set; }
            public Word[] Words { get; set; }
        }
       
        ***********************
    
        new Line() { Text = "Owner Full Name" };
        new Line() { Text = "Bob Lee" };

    We can't establish a connection between them, except to specify manually as in your original code.

    Therefore, please consider reconstructing a qualified JSON before attempting to import it into a csv file.

    Best Regards,

    Timon 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Hi Timon

    Thank you for looking into this. 

    This json is not constructed by me.

    My program currently loops through a directory of pdf/image files and generates json files using the Azure computer vision REST API.

    Someone had recommended I should deserialize the json instead of  using chOJSONReader, so I was considering doing something like this,

    var res = JsonConvert.DeserializeObject< dynamic>(json);
    
    Response.Write(res.”Practice Name”);

    but from your response, it seems like even that wont work, right? Because the values ​​of Text property are in two unrelated objects right?


    • Edited by cataster Monday, October 26, 2020 2:35 AM
    Monday, October 26, 2020 2:34 AM
  • Hi cataster,

    Judging from the results of my experiment, yes.

    Your initial code is the only way.

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by cataster Monday, October 26, 2020 2:59 AM
    Monday, October 26, 2020 2:41 AM
  • Hi cataster,

    Judging from the results of my experiment, yes.

    Your initial code is the only way.

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thank you Timon!

    Monday, October 26, 2020 3:00 AM