Home > database >  How to rename key value pair within a nested json structure in C#
How to rename key value pair within a nested json structure in C#

Time:05-16

Hello everyone and thanks for helping me in advance. The following question might sound silly but I'm a beginner about it. Let's start! I have the following json that comes from a web api.

{
  "status": "OK",
  "cases": {
    "_id": "61f8126266221531009f8909"
    "nameLower": "at&t - v.1 - 31.01.2022",
    "updatedAt": "2022-04-01T15:06:32.483Z",
    "createdAt": "2022-01-31T16:46:26.139Z",
    "device": "56a0b485303163de1a64e894",
    "procedure": {
      "_id": "61e918809033533000bc1034",
      "advanced": {
        "simpleSearch": [],
        "title": [],
        "autoDocumentLabel": "AT&T - V.1"
      },
      "deleted": false,
      "versions": [
        "61e918809033533000bc1035",
        "61f8106266221531009f8908"
      ],
      "documents": [
        "61e933639033533000bc10d3",
        "61e93df69033533000bc10d6",
        "61e960909033533000bc1105"
      ],
      "categories": [
        "6184e92ec35d83211e721978",
        "618a3e35c35d8350a772d335"
      ],
      "users": [
        "619664488f8190280002cde9",
        "619663bc8f8190280002cde6"
      ],
      "__v": 46,
      "meta": {
        "documentNumberSeq": 0
      },
      "published": "61f8106266221531009f8908",
      "groups": []
    },
    "version": {
      "_id": "61f8106266221531009f8908",
      "updatedAt": "2022-01-31T16:37:54.193Z",
      "createdAt": "2022-01-31T16:37:54.193Z",
      "procedure": "61e918809033533000bc1034",
      "user": "619664488f8190280002cde9",
      "version": 44,
      "deleted": false,
      "forms": [
        {
          "active": true,
          "oldorder": 1,
          "type": "step",
          "order": 1,
          "properties": [],
          "dependencies": [],
          "description": "",
          "guid": "4d567977-246a-453a-8bef-914eb2a9b103",
          "label": "LOCATION_P1"
        },
        {
          "active": false,
          "oldorder": 2,
          "type": "step",
          "order": 2,
          "properties": [],
          "dependencies": [],
          "description": "",
          "guid": "9063b301-47d4-465d-842e-b8fa7d071d33",
          "label": "LOCATION_P2"
        },
        {
          "active": false,
          "oldorder": 3,
          "type": "step",
          "order": 3,
          "properties": [],
          "dependencies": [],
          "description": "",
          "guid": "7c3e2bb2-ca82-4f36-8e5f-6a5d202818fd",
          "label": "LOCATION"
        }
      ],
      "published": false,
      "__v": 0
    },
    "hash": "67a18a162d3ddb928be1e4c859ef88c8",
    "name": "AT&T - V.1 - 31.01.2022",
    "location": "NULL",
    "status": "closed",
    "utcOffset": 120,
    "user": {
      "_id": "619664488f8190280002cde9",
      "firstName": "Asd",
      "lastName": "Asd",
      "email": "[email protected]",
      "showInScheduler": false,
      "disableKeycloakLogin": false,
      "groups": [],
      "active": true,
      "allowADLogin": true
    },
    "deleted": false,
    "extendedTitle": [],
    "timeTracking": [],
    "workStart": "2022-01-31T16:37:58.948Z",
    "feSyncDate": "2022-04-01T15:06:30.204Z",
    "feVersion": "v3.7.1-20-g7d1a81035",
    "reopen": false,
    "originTime": "2022-01-31T16:38:28.573Z",
    "content": [
      {
        "field": "DATA",
        "value": "2021-11-23T15:37:59.386Z"
      },
      {
        "field": "Other",
        "value": ""
      },
      {
        "field": "Other",
        "value": ""
      },
      {
        "field": "SP",
        "value": "14042547"
      },
      {
        "field": "AVAILABLE CLIENT",
        "value": {
          "Label": "Yes",
          "Value": "Yes"
        }
      },
      {
        "field": "REFERENCE CLIENT",
        "value": "MR JOHN DOE"
      },
      {
        "field": "CABLE TYPE",
        "value": {
          "Label": "Rame",
          "Value": "Rame"
        }
      },
      {
        "field": "CABLES",
        "value": {
          "Label": "WALL",
          "Value": "WALL"
        }
      },
      {
        "field": "TERMINATION",
        "value": {
          "Label": "WALL",
          "Value": "WALL"
        }
      },
      {
        "field": "PLANT LOCATION",
        "value": {
          "Label": "PRIVATE PROPERTY",
          "Value": "PRIVATE PROPERTY"
        }
      },
      {
        "field": "TRIAL PERIOD",
        "value": ""
      },
      {
        "field": "EXECUTED BY:",
        "value": "JOHN DOE"
      }
    ],
    "__v": 1,
    "closedBy": "61405b68db0e4c2900642020",
    "edited": {
      "at": "2022-04-01T15:06:30.204Z",
      "by": "Name Surname"
    },
    "documents": [
      {
        "_id": "61e960909033533000bc1105",
        "label": "AT&T - V.1",
        "description": ""
      }
    ],
    "tasks": [],
    "hasTasks": false
  }
}

What I want to do is rename both the "field" key and its value, contained in the value of the "content" key, as follows:

          {
            "PLANT LOCATION":"PRIVATE PROPERTY"
          },
          {
            "TRIAL PERIOD":""
          },
          {
            "EXECUTED BY":"JOHN DOE"
          }

So changing the key "field" with its value and changing the value "field" with the value of "Value", as in this example.

I tried to look around for a way to change the keys but the result I get is not what I wanted.

This is my code:

    var caseInfo = JsonConvert.DeserializeObject<dynamic>(apiResponse);
                        JObject jObject = JObject.Parse((string)caseInfo.ToString());
                        var labelByGuid = jObject.SelectTokens("cases.version.forms[*].sections[*].rows[*].fields[?(@.guid && @.label)]")
                            .ToDictionary(t => t["guid"]!.ToString().ToUpperInvariant(),
                                            t => t["label"]!.ToString());
                        // Update entries.
                        foreach (var entry in jObject.SelectTokens("cases.content[?(@.field)]"))
                        {
                            var field = entry["field"]!.ToString().ToUpperInvariant();
                            if (!labelByGuid.TryGetValue(field, out var label))
                                continue;
                        
                            entry["field"] = label;

//=> I added this piece of code to try to do what I said before

                            if (entry["value"] != null)
                            {
                                if (entry["value"].HasValues)
                                {
                                    entry["field"] = entry["value"].SelectToken("Value");
                                }
                                else
                                {
                                    entry["field"] = entry["value"];
                                }
                                
                            }
                            else
                            {
                                entry["field"] = "";
                            }
    
                            //entry["field"] = label;
                            SetKey(jObject, entry.First, label);   <=
                        }

                        public void SetKey(JObject parent, JToken token, string newKey)
                        {
                            var tokenProp = token as JProperty;
                            var oldKeyName = tokenProp.Name;
                            parent[newKey] = tokenProp.Value;
                            parent.Remove(oldKeyName);
                        }

This is the result I get:

{
  "status": "OK",
  "cases": {
    "_id": "61f8126266221531009f8909"
    "nameLower": "at&t - v.1 - 31.01.2022",
    "updatedAt": "2022-04-01T15:06:32.483Z",
    "createdAt": "2022-01-31T16:46:26.139Z",
    "device": "56a0b485303163de1a64e894",
    "procedure": {
      "_id": "61e918809033533000bc1034",
      "advanced": {
        "simpleSearch": [],
        "title": [],
        "autoDocumentLabel": "AT&T - V.1"
      },
      "deleted": false,
      "versions": [
        "61e918809033533000bc1035",
        "61f8106266221531009f8908"
      ],
      "documents": [
        "61e933639033533000bc10d3",
        "61e93df69033533000bc10d6",
        "61e960909033533000bc1105"
      ],
      "categories": [
        "6184e92ec35d83211e721978",
        "618a3e35c35d8350a772d335"
      ],
      "users": [
        "619664488f8190280002cde9",
        "619663bc8f8190280002cde6"
      ],
      "__v": 46,
      "meta": {
        "documentNumberSeq": 0
      },
      "published": "61f8106266221531009f8908",
      "groups": []
    },
    "version": {
      "_id": "61f8106266221531009f8908",
      "updatedAt": "2022-01-31T16:37:54.193Z",
      "createdAt": "2022-01-31T16:37:54.193Z",
      "procedure": "61e918809033533000bc1034",
      "user": "619664488f8190280002cde9",
      "version": 44,
      "deleted": false,
      "forms": [
        {
          "active": true,
          "oldorder": 1,
          "type": "step",
          "order": 1,
          "properties": [],
          "dependencies": [],
          "description": "",
          "guid": "4d567977-246a-453a-8bef-914eb2a9b103",
          "label": "LOCATION_P1"
        },
        {
          "active": false,
          "oldorder": 2,
          "type": "step",
          "order": 2,
          "properties": [],
          "dependencies": [],
          "description": "",
          "guid": "9063b301-47d4-465d-842e-b8fa7d071d33",
          "label": "LOCATION_P2"
        },
        {
          "active": false,
          "oldorder": 3,
          "type": "step",
          "order": 3,
          "properties": [],
          "dependencies": [],
          "description": "",
          "guid": "7c3e2bb2-ca82-4f36-8e5f-6a5d202818fd",
          "label": "LOCATION"
        }
      ],
      "published": false,
      "__v": 0
    },
    "hash": "67a18a162d3ddb928be1e4c859ef88c8",
    "name": "AT&T - V.1 - 31.01.2022",
    "location": "NULL",
    "status": "closed",
    "utcOffset": 120,
    "user": {
      "_id": "619664488f8190280002cde9",
      "firstName": "Asd",
      "lastName": "Asd",
      "email": "[email protected]",
      "showInScheduler": false,
      "disableKeycloakLogin": false,
      "groups": [],
      "active": true,
      "allowADLogin": true
    },
    "deleted": false,
    "extendedTitle": [],
    "timeTracking": [],
    "workStart": "2022-01-31T16:37:58.948Z",
    "feSyncDate": "2022-04-01T15:06:30.204Z",
    "feVersion": "v3.7.1-20-g7d1a81035",
    "reopen": false,
    "originTime": "2022-01-31T16:38:28.573Z",
    "content": [
      {
        "field": "DATA",
        "value": "2021-11-23T15:37:59.386Z"
      },
      {
        "field": "Other",
        "value": ""
      },
      {
        "field": "Other",
        "value": ""
      },
      {
        "field": "SP",
        "value": "14042547"
      },
      {
        "field": "AVAILABLE CLIENT",
        "value": {
          "Label": "Yes",
          "Value": "Yes"
        }
      },
      {
        "field": "REFERENCE CLIENT",
        "value": "MR JOHN DOE"
      },
      {
        "field": "CABLE TYPE",
        "value": {
          "Label": "Rame",
          "Value": "Rame"
        }
      },
      {
        "field": "CABLES",
        "value": {
          "Label": "WALL",
          "Value": "WALL"
        }
      },
      {
        "field": "TERMINATION",
        "value": {
          "Label": "WALL",
          "Value": "WALL"
        }
      },
      {
        "field": "PLANT LOCATION",
        "value": {
          "Label": "PRIVATE PROPERTY",
          "Value": "PRIVATE PROPERTY"
        }
      },
      {
        "field": "TRIAL PERIOD",
        "value": ""
      },
      {
        "field": "EXECUTED BY:",
        "value": "JOHN DOE"
      }
    ],
    "__v": 1,
    "closedBy": "61405b68db0e4c2900642020",
    "edited": {
      "at": "2022-04-01T15:06:30.204Z",
      "by": "Name Surname"
    },
    "documents": [
      {
        "_id": "61e960909033533000bc1105",
        "label": "AT&T - V.1",
        "description": ""
      }
    ],
    "tasks": [],
    "hasTasks": false
  },
  "DATA": "2021-11-23T15:37:59.386Z",
  "OTHER": "",
  "SP": "14042547",
  "AVAILABBLE CLIENT": "Yes",
  "REFERENCE CLIENT": "MR JOHN DOE",
  "CABLE TYPE": "Rame",
  "CABLES": "WALL",
}

A last question. Beyond that, how can I remove the keys and values ​​that don't interest me? Or rather, how can I only get the keys with the respective values ​​"id", "nameLower" and "content"?

Again, really thanks to everyone in advance and I hope I was clear :)

UPDATE 15/05

{
      "status": "OK",
      "cases": {
        "_id": "61f8126266221531009f8909"
        "nameLower": "at&t - v.1 - 31.01.2022",
        "content": [
        {
            "DATA": "2021-11-23T15:37:59.386Z",
            "OTHER": "",
            "SP": "14042547",
            "AVAILABBLE CLIENT": "Yes",
            "REFERENCE CLIENT": "MR JOHN DOE",
            "CABLE TYPE": "Rame",
            "CABLES": "WALL",
        }]
      }
      
    }

I would like to have this final json. How can I get only these keys?

CodePudding user response:

try this

    var data= JObject.Parse(json); 
    var content = (JArray)data["cases"]["content"];
    var jObj = new JObject();
    foreach (var item in content)
    {
        var value = item["value"] is JObject ? (string)item["value"]["Value"] : (string)item["value"];

        if (jObj.ContainsKey((string)item["field"])) jObj[(string)item["field"]] = value;
        else jObj.Add((string)item["field"], value);
    }
    var newData= new JObject();
    newData["status"] = data["status"];
    newData["cases"] = new JObject();
    newData["cases"]["id"] = data["cases"]["_id"];
    newData["cases"]["nameLower"] = data["cases"]["nameLower"];
    newData["cases"]["content"] = new JArray { jObj };
    var newJson = newData.ToString();

new json

{
  "status": "OK",
  "cases": {
    "id": "61f8126266221531009f8909",
    "nameLower": "at&t - v.1 - 31.01.2022",
    "content": [
      {
        "DATA": "11/23/2021 15:37:59",
        "Other": "",
        "SP": "14042547",
        "AVAILABLE CLIENT": "Yes",
        "REFERENCE CLIENT": "MR JOHN DOE",
        "CABLE TYPE": "Rame",
        "CABLES": "WALL",
        "TERMINATION": "WALL",
        "PLANT LOCATION": "PRIVATE PROPERTY",
        "TRIAL PERIOD": "",
        "EXECUTED BY:": "JOHN DOE"
      }
    ]
  }
}
  • Related