none
how to create a nested json RRS feed

  • Question

  • Hi,

    i have a table as following shows

    create table test
    (
       country varchar(100),
       city varchar(100)
    )
    insert into test values
    ('countrya','citya1'),
    ('countrya','citya2'),
    ('countrya','citya3'),
    ('countryb','cityb1'),
    ('countryb','cityb2')

    I want to get a json like following shows

    [
        {
            country:"countrya",
            cities:["citya1","citya2","citya3"]
        }
        {
            country:"countryb",
            cities:["cityb1","citya2"]
        }
    ]

    How to achieve it by using JSON.NET?

    Thank you very much.

    Monday, May 8, 2017 4:11 AM

Answers

  • Hi CSharp _ Learner,

    We could use LINQ groupby method and Newtonsoft.Json to achieve it, The following code for your reference.

    var result = db.tests.GroupBy(t => t.country).Select(t => new {
                        country = t.Key,
                        cities = db.tests.Where(c=>c.country == t.Key).Select(c=>c.city).ToList()
                    }).ToList();
    
     var jsonValue = JsonConvert.SerializeObject(result);

    Best regards,

    Cole Wu


    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, May 8, 2017 9:33 AM
    Moderator