none
C# code to get a new Json unifying child items RRS feed

  • Question

  • Hi.

    I have the following response from Sql stored procedure

    I get this json 

    [
      {
        "idMedioDePago": 6,
        "medioDePago": "Tarjeta Credito (PayU)",
        "Franquicia": [
          {
            "id": 1,
            "nombre": "Visa"
          }
        ]
      },
      {
        "idMedioDePago": 6,
        "medioDePago": "Tarjeta Credito (PayU)",
        "Franquicia": [
          {
            "id": 2,
            "nombre": "Master Card"
          }
        ]
      },
      {
        "idMedioDePago": 6,
        "medioDePago": "Tarjeta Credito (PayU)",
        "Franquicia": [
          {
            "id": 3,
            "nombre": "American Express"
          }
        ]
      },
      {
        "idMedioDePago": 7,
        "medioDePago": "Pago Presencial",
        "MedioDePagoPresencial": [
          {
            "id": 1,
            "nombre": "Efecty"
          }
        ]
      },
      {
        "idMedioDePago": 7,
        "medioDePago": "Pago Presencial",
        "MedioDePagoPresencial": [
          {
            "id": 2,
            "nombre": "Baloto"
          }
        ]
      },
      {
        "idMedioDePago": 7,
        "medioDePago": "Pago Presencial",
        "MedioDePagoPresencial": [
          {
            "id": 3,
            "nombre": "Banco de Bogota"
          }
        ]
      },
      {
        "idMedioDePago": 7,
        "medioDePago": "Pago Presencial",
        "MedioDePagoPresencial": [
          {
            "id": 4,
            "nombre": "Bancolombia"
          }
        ]
      },
      {
        "idMedioDePago": 8,
        "medioDePago": "Tarjeta Debito (PSE)"
      }
    ]

    I get the upper json with the next code in c#:

     var mediosdepago = _context.RespuestaMedioDePagoDB.FromSqlRaw("EXECUTE Sp_ObtenerMedioDePago @IdCanalDePago={0}, @IdPais={1}", idCanalDePago, idPais).ToList();
    
                List<RespuestaMedioDePago> listRespuestaMedioDePago = new List<RespuestaMedioDePago>();
    
                mediosdepago.ForEach(o =>
                {
                    RespuestaMedioDePago objRespuestaMedioDePago = new RespuestaMedioDePago();
                    objRespuestaMedioDePago.idMedioDePago = o.IdMedioDePago;
                    objRespuestaMedioDePago.medioDePago = o.MedioDePago;
    
    
                    if (o.IdFranquicia != null)
                    {
                        Franquicia_MedioDePago objFranquicia_MedioDePago = new Franquicia_MedioDePago();
                        objFranquicia_MedioDePago.id = o.IdFranquicia;
                        objFranquicia_MedioDePago.nombre = o.Franquicia;
                        objRespuestaMedioDePago.Franquicia.Add(objFranquicia_MedioDePago);
                    }
    
                    if (o.IdMedioDePagoPresencial != null)
                    {
                        MedioDePagoPresencial_MedioDePago objMedioDePagoPresencial_MedioDePago = new MedioDePagoPresencial_MedioDePago();
                        objMedioDePagoPresencial_MedioDePago.id = o.IdMedioDePagoPresencial;
                        objMedioDePagoPresencial_MedioDePago.nombre = o.MedioDePagoPresencial;
                        objRespuestaMedioDePago.MedioDePagoPresencial.Add(objMedioDePagoPresencial_MedioDePago);
                    }
    
                    listRespuestaMedioDePago.Add(objRespuestaMedioDePago);
                });
    
                return JsonConvert.SerializeObject(listRespuestaMedioDePago, Formatting.Indented); 

    But I need the next Json:

    [
      {
        "idMedioDePago": 6,
        "medioDePago": "Tarjeta Credito (PayU)",
        "Franquicia": [
          {
            "id": 1,
            "nombre": "Visa"
          },
    	  {
            "id": 2,
            "nombre": "Master Card"
          },
    	  {
            "id": 3,
            "nombre": "American Express"
          }
        ]
      }
     
      {
        "idMedioDePago": 7,
        "medioDePago": "Pago Presencial",
        "MedioDePagoPresencial": [
          {
            "id": 1,
            "nombre": "Efecty"
          },
    	  {
            "id": 2,
            "nombre": "Baloto"
          },
    	  {
            "id": 3,
            "nombre": "Banco de Bogota"
          },
    	  {
            "id": 4,
            "nombre": "Bancolombia"
          }
        ]
      },
      {
        "idMedioDePago": 8,
        "medioDePago": "Tarjeta Debito (PSE)"
      }
    ]

    How can I do the conversion to unify the childs items? What could I do to fix the C# code?

    Thanks in advance.

    Thursday, February 13, 2020 4:24 PM

Answers

  • Hi,

    I modified the previous code, the new code is as follows:

            static void Main(string[] args)
            {
                string json = File.ReadAllText(@"d:\Test\a.json");
                List<Rootobject> rootobject = JsonConvert.DeserializeObject<List<Rootobject>>(json);
    
                var res = from obj in rootobject
                          group obj by obj.idMedioDePago into g
                          select new
                          {
                              idMedioDePago = g.Key,
                              medioDePago = g.Select(o => o.medioDePago).First(),
                              MedioDePagoPresencial = g.Select(o => o.MedioDePagoPresencial).First() == null ? null : g.Select(o => o.MedioDePagoPresencial),
                              Franquicia = g.Select(o => o.Franquicia).First() == null ? null : g.Select(o => o.Franquicia)
                          };
    
                var result = JsonConvert.SerializeObject(res,
                    new JsonSerializerSettings()
                    {
                        NullValueHandling = NullValueHandling.Ignore
                    });
                Console.WriteLine(result);
            }

    The result is exactly what you want.

    [{
    	"idMedioDePago": 6,
    	"medioDePago": "Tarjeta Credito (PayU)",
    	"Franquicia": [
    		[{
    			"id": 1,
    			"nombre": "Visa"
    		}],
    		[{
    			"id": 2,
    			"nombre": "Master Card"
    		}],
    		[{
    			"id": 3,
    			"nombre": "American Express"
    		}]
    	]
    }, {
    	"idMedioDePago": 7,
    	"medioDePago": "Pago Presencial",
    	"MedioDePagoPresencial": [
    		[{
    			"id": 1,
    			"nombre": "Efecty"
    		}],
    		[{
    			"id": 2,
    			"nombre": "Baloto"
    		}],
    		[{
    			"id": 3,
    			"nombre": "Banco de Bogota"
    		}],
    		[{
    			"id": 4,
    			"nombre": "Bancolombia"
    		}]
    	]
    }, {
    	"idMedioDePago": 8,
    	"medioDePago": "Tarjeta Debito (PSE)"
    }]

    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 diegodamage82 Monday, February 17, 2020 1:43 PM
    Monday, February 17, 2020 7:07 AM

All replies

  • What are talking about? Do you mean that you have a children collection of objects  for each parent object? 
    • Edited by DA924x Thursday, February 13, 2020 4:45 PM
    Thursday, February 13, 2020 4:44 PM
  • For example, The first parent is "medio de pago": "Tarjeta de credito". It is repeated three times but I need only one time and unify its childs called Franquicia with its parent "Tarjeta de credito" .

    The second parent is "medio de pago": "Pago Presencial". It is repeated four times but I need only one time and unify its childs called MedioDePagoPresencial with its parent "Pago presencial" .

    Thursday, February 13, 2020 5:10 PM
  • Well, you're not going to be able to do anything in one shot.  You're going to have to have two List<T>'s loaded with parent and children  objects and merger it all into one List<T> with some kind of routine. 
    Thursday, February 13, 2020 7:54 PM
  • Hi,

    Thank you for posting here.

    When we use json, we generally deserialize it to object then use it.

    So we can use linq to group them.

            static void Main(string[] args)
            {
                string json = File.ReadAllText(@"d:\Test\a.json");
                List<Rootobject> rootobject = JsonConvert.DeserializeObject<List<Rootobject>>(json);
    
                var res = from obj in rootobject
                          group obj by obj.idMedioDePago into g
                          select new 
                          {
                              idMedioDePago = g.Key,
                              medioDePago = g.Select(o=>o.medioDePago),
                              MedioDePagoPresencial = g.Select(o=>o.MedioDePagoPresencial),
                              Franquicia = g.Select(o=>o.Franquicia)
                          };
    
                string a =  JsonConvert.SerializeObject(res);
                Console.WriteLine(a);
            }

    This is the result, it is not perfect because the parent is different, you can adjust it according to your requirements.

    [{
    	"idMedioDePago": 6,
    	"medioDePago": ["Tarjeta Credito (PayU)", "Tarjeta Credito (PayU)", "Tarjeta Credito (PayU)"],
    	"MedioDePagoPresencial": [null, null, null],
    	"Franquicia": [
    		[{
    			"id": 1,
    			"nombre": "Visa"
    		}],
    		[{
    			"id": 2,
    			"nombre": "Master Card"
    		}],
    		[{
    			"id": 3,
    			"nombre": "American Express"
    		}]
    	]
    }, {
    	"idMedioDePago": 7,
    	"medioDePago": ["Pago Presencial", "Pago Presencial", "Pago Presencial", "Pago Presencial"],
    	"MedioDePagoPresencial": [
    		[{
    			"id": 1,
    			"nombre": "Efecty"
    		}],
    		[{
    			"id": 2,
    			"nombre": "Baloto"
    		}],
    		[{
    			"id": 3,
    			"nombre": "Banco de Bogota"
    		}],
    		[{
    			"id": 4,
    			"nombre": "Bancolombia"
    		}]
    	],
    	"Franquicia": [null, null, null, null]
    }, {
    	"idMedioDePago": 8,
    	"medioDePago": ["Tarjeta Debito (PSE)"],
    	"MedioDePagoPresencial": [null],
    	"Franquicia": [null]
    }]

    Hope this could be helpful.

    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.

    Friday, February 14, 2020 8:10 AM
  • Hi Timon.

    Great. With your help I am pretty close.

    This is the resulting code that I get with your help:

    var res = from obj in listRespuestaMedioDePago
                          group obj by obj.idMedioDePago into g
                          select new
                          {
                              idMedioDePago = g.Key,
                              medioDePago = g.Select(o => o.medioDePago).First(),
                              Franquicia = g.Select(o => o.Franquicia),
                              MedioDePagoPresencial = g.Select(o => o.MedioDePagoPresencial)
                          };


    With this code I get the following json:

    [
      {
        "idMedioDePago": 6,
        "medioDePago": "Tarjeta Credito (PayU)",
        "Franquicia": [
          [
            {
              "id": 1,
              "nombre": "Visa"
            }
          ],
          [
            {
              "id": 2,
              "nombre": "Master Card"
            }
          ],
          [
            {
              "id": 3,
              "nombre": "American Express"
            }
          ]
        ],
        "MedioDePagoPresencial": [
          [],
          [],
          []
        ]
      },
      {
        "idMedioDePago": 7,
        "medioDePago": "Pago Presencial",
        "Franquicia": [
          [],
          [],
          [],
          []
        ],
        "MedioDePagoPresencial": [
          [
            {
              "id": 1,
              "nombre": "Efecty"
            }
          ],
          [
            {
              "id": 2,
              "nombre": "Baloto"
            }
          ],
          [
            {
              "id": 3,
              "nombre": "Banco de Bogota"
            }
          ],
          [
            {
              "id": 4,
              "nombre": "Bancolombia"
            }
          ]
        ]
      },
      {
        "idMedioDePago": 8,
        "medioDePago": "Tarjeta Debito (PSE)",
        "Franquicia": [
          []
        ],
        "MedioDePagoPresencial": [
          []
        ]
      }
    ]

    This is pretty close to what I am looking for. The last thing I need is to remove the empty arrays. I mean remove:

     "MedioDePagoPresencial": [
          [],
          [],
          []
     "Franquicia": [
          [],
          [],
          [],
          []
    "Franquicia": [
          []
        ],
        "MedioDePagoPresencial": [
          []
    Thank you so much.

    Friday, February 14, 2020 1:29 PM
  • Isn't this the exact same question you already asked - https://social.msdn.microsoft.com/Forums/vstudio/en-US/d25ca30b-b808-4a63-8d13-8b5fd765d1b1/transform-json?forum=csharpgeneral#d25ca30b-b808-4a63-8d13-8b5fd765d1b1

    Please do not post the same question over and over again. Just follow up with the existing question.


    Michael Taylor http://www.michaeltaylorp3.net

    Friday, February 14, 2020 2:42 PM
    Moderator
  • Yes. But at the end it doesnt work. I need the code with lambda expression.
    Friday, February 14, 2020 2:47 PM
  • Hi,

    I modified the previous code, the new code is as follows:

            static void Main(string[] args)
            {
                string json = File.ReadAllText(@"d:\Test\a.json");
                List<Rootobject> rootobject = JsonConvert.DeserializeObject<List<Rootobject>>(json);
    
                var res = from obj in rootobject
                          group obj by obj.idMedioDePago into g
                          select new
                          {
                              idMedioDePago = g.Key,
                              medioDePago = g.Select(o => o.medioDePago).First(),
                              MedioDePagoPresencial = g.Select(o => o.MedioDePagoPresencial).First() == null ? null : g.Select(o => o.MedioDePagoPresencial),
                              Franquicia = g.Select(o => o.Franquicia).First() == null ? null : g.Select(o => o.Franquicia)
                          };
    
                var result = JsonConvert.SerializeObject(res,
                    new JsonSerializerSettings()
                    {
                        NullValueHandling = NullValueHandling.Ignore
                    });
                Console.WriteLine(result);
            }

    The result is exactly what you want.

    [{
    	"idMedioDePago": 6,
    	"medioDePago": "Tarjeta Credito (PayU)",
    	"Franquicia": [
    		[{
    			"id": 1,
    			"nombre": "Visa"
    		}],
    		[{
    			"id": 2,
    			"nombre": "Master Card"
    		}],
    		[{
    			"id": 3,
    			"nombre": "American Express"
    		}]
    	]
    }, {
    	"idMedioDePago": 7,
    	"medioDePago": "Pago Presencial",
    	"MedioDePagoPresencial": [
    		[{
    			"id": 1,
    			"nombre": "Efecty"
    		}],
    		[{
    			"id": 2,
    			"nombre": "Baloto"
    		}],
    		[{
    			"id": 3,
    			"nombre": "Banco de Bogota"
    		}],
    		[{
    			"id": 4,
    			"nombre": "Bancolombia"
    		}]
    	]
    }, {
    	"idMedioDePago": 8,
    	"medioDePago": "Tarjeta Debito (PSE)"
    }]

    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 diegodamage82 Monday, February 17, 2020 1:43 PM
    Monday, February 17, 2020 7:07 AM
  • Thank you so much Timon.

    You are a master :).

    It worked perfectly!!!


    Monday, February 17, 2020 1:45 PM