Flatten json content

Question

Hello I need to flatten the content of a json serialization result but I don't know how, I tried different ways with no success, this is an example:

(I'm using NET 5 records for simplicity is the same as using classes)

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text.Json;
namespace dicttest
{
    public class Program
    {
        public record inventory(int inventoryID, string name, string description);
        public record aditionals(int inventoryID, string name, string value);
        public static List<inventory> inventoryList = new();
        public static List<aditionals> inventoryAditionalList = new();

        public static void Main()
        {
            // populate
            inventoryList.Add(new inventory(1, "Inventory 1", "Inventory 1 cool description"));
            inventoryList.Add(new inventory(2, "Inventory 2", "Inventory 2 cool description"));
            inventoryList.Add(new inventory(3, "Inventory 3", "Inventory 3 cool description"));

            inventoryAditionalList.Add(new aditionals(1, "legs", "4"));
            inventoryAditionalList.Add(new aditionals(1, "screws", "20"));
            inventoryAditionalList.Add(new aditionals(2, "legs", "3"));
            inventoryAditionalList.Add(new aditionals(3, "screws", "50"));

            // join
            var result = inventoryList.Select(c => new
            {
                c.inventoryID,
                c.name,
                c.description,
                aditionals = inventoryAditionalList.Where(s => s.inventoryID == c.inventoryID).Select(d => new { d.name, d.value })
            });

            // show
            var json3 = JsonSerializer.Serialize(result, new JsonSerializerOptions { WriteIndented = true });
            Console.WriteLine(json3);
        }
    }
}

This is the result of that code:

[
  {
    "inventoryID": 1,
    "name": "Inventory 1",
    "description": "Inventory 1 cool description",
    "aditionals": [
      {
        "name": "legs",
        "value": "4"
      },
      {
        "name": "screws",
        "value": "20"
      }
    ]
  },
  {
    "inventoryID": 2,
    "name": "Inventory 2",
    "description": "Inventory 2 cool description",
    "aditionals": [
      {
        "name": "legs",
        "value": "3"
      }
    ]
  },
  {
    "inventoryID": 2,
    "name": "Inventory 3",
    "description": "Inventory 3 cool description",
    "aditionals": [
      {
        "name": "screws",
        "value": "50"
      }
    ]
  }
]

But this is what I want to obtain:

[
  {
    "inventoryID": 1,
    "name": "Inventory 1",
    "description": "Inventory 1 cool description",
    "legs": "4",
    "screws": "20"
  },
  {
    "inventoryID": 2,
    "name": "Inventory 2",
    "description": "Inventory 2 cool description",
    "legs": "3"
  },
  {
    "inventoryID": 3,
    "name": "Inventory 3",
    "description": "Inventory 3 cool description",
    "screws": "50"
  }
]

Any Idea? Thank you!

“Couldn’t do it” is not a specific description of the problem you are facing. — Riwen, Mar 08 '21 at 22:05
Looking closer, SelectMany won't do because this isn't standard flattening, but in fact denormalizing an EAV structure. That requires building a dynamic structure at runtime for which a dictionary will do, as shown below, or an ExpandoObject (which is also an IDictionary, the code would look very similar). — Gert Arnold, Mar 09 '21 at 15:33

score 3 · Accepted Answer · answered Mar 08 '21 at 23:54

Changing the output data type to Dictionary is the right way.

Here is the updated result construction part:

// join
var result = inventoryList.Select(c => 
    { 
        var d = new Dictionary<string,string>()
            { 
                {"inventoryID", c.inventoryID.ToString() },
                { "name", c.name },
                { "description", c.description } 
            };
        foreach (var additional in inventoryAditionalList.Where(s => s.inventoryID == c.inventoryID)) 
            d.Add(additional.name, additional.value.ToString());
        return d;
    }).ToList();

I'm not in the laptop right now but seems the solution! Tomorrow I'll test it and give you the accepted answer check — Santiago, Mar 09 '21 at 01:45

score -1 · Answer 2 · answered Mar 08 '21 at 22:49

If you are willing to change your C# data structures, you can get the JSON output by using a Dictionary<string,string> to hold your key-value data:

namespace dicttest
{
    public class Program
    {
        public record inventory(int inventoryID, string name, string description);
        public record aditionals(int inventoryID, Dictionary<string, string> values);
        public static List<inventory> inventoryList = new();
        public static List<aditionals> inventoryAditionalList = new();

        public static void Main()
        {
            // populate
            inventoryList.Add(new inventory(1, "Inventory 1", "Inventory 1 cool description"));
            inventoryList.Add(new inventory(2, "Inventory 2", "Inventory 2 cool description"));
            inventoryList.Add(new inventory(3, "Inventory 3", "Inventory 3 cool description"));

            var additional = new aditionals(1, new Dictionary<string, string>() { { "legs", "4" }, { "screws", "20" } });
            inventoryAditionalList.Add(additional);

            additional = new aditionals(2, new Dictionary<string, string>() { { "legs", "3" }});
            inventoryAditionalList.Add(additional);
            
            additional = new aditionals(3, new Dictionary<string, string>() { { "screws", "50" } });
            inventoryAditionalList.Add(additional);

            // join
            var result = inventoryList.Select(c => new
            {
                c.inventoryID,
                c.name,
                c.description,
                aditionals = inventoryAditionalList.Where(s => s.inventoryID == c.inventoryID).Select(d => d.values)
            });

            // show
            var json3 = JsonSerializer.Serialize(result, new JsonSerializerOptions { WriteIndented = true });
            Console.WriteLine(json3);
        }
    }
}
//
//[
//  {
//    "inventoryID": 1,
//    "name": "Inventory 1",
//    "description": "Inventory 1 cool description",
//    "aditionals": [
//      {
//        "legs": "4",
//        "screws": "20"
//      }
//    ]
//  },
//  {
//  "inventoryID": 2,
//    "name": "Inventory 2",
//    "description": "Inventory 2 cool description",
//    "aditionals": [
//
//    {
//      "legs": "3"
//
//    }
//    ]
//  },
//  {
//  "inventoryID": 3,
//    "name": "Inventory 3",
//    "description": "Inventory 3 cool description",
//    "aditionals": [
//
//    {
//      "screws": "50"
//
//    }
//    ]
//  }
//]

Please consider adding a comment if you think this answer can be improved. — Phillip Ngan, Mar 08 '21 at 22:57
The output you have is the same as the *actual* and is not the expected. — Orace, Mar 08 '21 at 22:58

Flatten json content

2 Answers2