Generate JSON with nested objects keys

Question

I'm trying to generate a JSON string in C# (.Net Core) from some database records.

Data schema is flat with category (string), book Id (integer) and book name (string).

Data rows look like:

Category    BookId    BookName
---------------------------------------
Mystery     1         My first book
Biography   2         My second book
Mystery     3         My third book
Crime       4         My fourth book
Romance     5         My fifth book
Biography   6         My sixth book
Mystery     7         My seventh book
Romance     8         My eight book
SciFi       9         My ninth book
Poetry      10        My tenth book

I'm running a simple SQL query to fetch the records (up-to 5000) in a C# List collection:

SELECT Category, BookId, BookName from Books

I want to generate a JSON string (preferably using Newtonsoft Json.Net library) with books grouped by category, and including the category and book-id as the nested object keys:

{
  "Mystery": {
    "1": {
      "Name": "My first book"
    },
    "3": {
      "Name": "My third book"
    },
    "7": {
      "Name": "My seventh book"
    }
  },
  "Biography": {
    "2": {
      "Name": "My second book"
    },
    "6": {
      "Name": "My sixth book"
    }
  },
  "Crime": {
    "4": {
      "Name": "My fourth book"
    }
  }
  :
  :
}

I've tried Dictionary and Lookup (probably better than Dictionary due to less overhead with hash operations), but I can't get them to generate the format I'm after mainly for direct key-based access in the JS object.

Any suggestions for a performant approach are appreciated.

I would create a model (DTO) that represents the JSON that you want, map your database result to that and then just serialize the model. — Michael
– Michael, Commented May 25, 2020 at 10:11
Please, share the code what you've tried with Dictionary and Lookup. Btw, do you need everything in a single json object or it should be an array? — Pavel Anikhouski
– Pavel Anikhouski, Commented May 25, 2020 at 10:12

Guru Stron · Accepted Answer · 2020-05-25 10:33:02Z

1

Try to convert your collection to nested dictionaries:

// use yor actual type here
var collection = new[] 
{
    new {Category = "Mystery", BookId = 1 , BookName = "My first book"},
    new {Category = "Mystery", BookId = 2 , BookName = "My second book"}
};

var res = collection
    .GroupBy(arg => arg.Category)
    .ToDictionary(g => g.Key, g => g.ToDictionary(b => b.BookId, b => b.BookName));
JsonConvert.SerializeObject(res, Newtonsoft.Json.Formatting.Indented);

answered May 25, 2020 at 10:33

Guru Stron

150k11 gold badges186 silver badges231 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Nick Over a year ago

Thank you, this works. Dictionary is heavy though, specially for thousands of nested ones. I'll appreciate any suggestions to improve performance or an alternative approach?

Guru Stron Over a year ago

If performance is not good enough you can try to manually create Json via JObject and write grouping results there. But I suggest that you benchmark it first.

Nick Over a year ago

Ran some benchmarks. Turns out that ToDictionary is at-least twice as fast as looped JObject creation.

Collectives™ on Stack Overflow

Generate JSON with nested objects keys

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related