Flatten a Parent-child json

[vermapraveen]

hi @Courela

trying to find some nested loop kind of functionality to flatten below parent-child structure. Can you suggest how to proceed on this:

Input:

{
	"Path": "Type1",
	"Children": [
		{
			"Path": "Type1~Cat1",
			"Items": [ 
                                         { "Id": "66717101" },
				         { "Id": "66717102" }
			           ],
			"Children": [
				{
					"Path": "Type1~Cat1~Sku1",
					"Items": [ { "Id": "66717101" } ],
					"Children": [
						{
							"Path": "Type1~Cat1~Sku2~Model1",
							"Items": [ { "Id": "66717102" } ]
						},
						{
							"Path": "Type1~Cat1~Sku1~Model1",
							"Items": [ { "Id": "66717101" }, 
                                                                       { "Id": "66717102" } ]
						}
					]
				},
				{
					"Path": "Type1~Cat1~Sku2",
					"Items": [ { "Id": "66717101" } ],
					"Children": [
						{
							"Path": "Type1~Cat1~Sku2~Model1",
							"Items": [ { "Id": "66717722" } ]
						}
					]
				}
			]
		},
		{
			"Path": "Type1-Cat2",
			"Children": []
		},
		{
			"Path": "Type1-Cat3",
			"Children": []
		}
	],
	"Items": [ { "Id": "66717766" },
		       { "Id": "6828147" } ]
}

Output:

[
	{
		"Id": "66717101",
		"Path": [
			"Type1~Cat1",
			"Type1~Cat1~Sku1",
			"Type1~Cat1~Sku2~Model1",
			"Type1~Cat1~Sku2"
		]
	},
	{
		"Id": "66717102",
		"Path": [
			"Type1~Cat1",
			"Type1~Cat1~Sku2~Model1",
			"Type1~Cat1~Sku1~Model1"
		]
	},
	{
		"Id": "66717722",
		"Path": [
			"Type1~Cat1~Sku2~Model1"
		]
	},
	{
		"Id": "66717766",
		"Path": [
			"Type1"
		]
	},
	{
		"Id": "6828147",
		"Path": [
			"Type1"
		]
	}
]

[Courela]

Only with one transformation, that doesn't seem possible.
I used code from #267 and #239 pull requests combined and got something that is more similar to what you want, although not the same, but with some effort I believe the desired output can be obtained.
The output does not have the 'root' element/type (Type1), since it's not below a property named 'Children', but I think that should be more or less easy to accomplish.
After this grouping, using the #grouparrayby should give you the required output.

Transformer:

{
    "result": {
        "#transform($)": [{
                "Children": "#valueof($..Children)"
            }, {
                "intermediate": {
                    "#loop($.Children)": {
                        "Items": "#currentvalueatpath($[0].Items)",
                        "Path": "#currentvalueatpath($[0].Path)"
                    }
                }
            },
            "#valueof($.intermediate)"
        ]
    }
}

Output:

{
    "result": [{
            "Items": [{
                    "Id": "66717101"
                }, {
                    "Id": "66717102"
                }
            ],
            "Path": "Type1~Cat1"
        }, {
            "Items": [{
                    "Id": "66717101"
                }
            ],
            "Path": "Type1~Cat1~Sku1"
        }, {
            "Items": [{
                    "Id": "66717102"
                }
            ],
            "Path": "Type1~Cat1~Sku2~Model1"
        }, {
            "Items": [{
                    "Id": "66717722"
                }
            ],
            "Path": "Type1~Cat1~Sku2~Model1"
        }, {
            "Items": null,
            "Path": null
        }, {
            "Items": null,
            "Path": null
        }
    ]
}

[vikasagrawal]

We have created 2 custom fields to achieve the same thing. One function to flatten it by field path and other to identify jarray and flatten it recursively.

public static JArray FlattenNestedJson(JArray source, string fieldPath)
{
JArray array = new JArray();
foreach (JObject item in source)
{
if (item.SelectToken(fieldPath) != null &&
item.SelectToken(fieldPath) is JArray)
{
var tem = FlattenNestedJson(item.SelectToken(fieldPath).DeepClone() as JArray, fieldPath);
if (tem != null && tem.Any())
{
foreach (var child in tem.Children())
{
array.Add(child);
}
}
item.Remove(fieldPath);
}
array.Add(item);
}

        return array;
    }

    public static IDictionary<string, string> FlattenJson(object inputJson)
    {
        JToken parent = JToken.FromObject(inputJson);

        Dictionary<string, string> result = null;

        result = PopulateRecursively(parent, result);

        return result;
    }

    private static Dictionary<string, string> PopulateRecursively(JToken parent, Dictionary<string, string> result)
    {
        if (parent.HasValues)
        {
            foreach (JToken child in parent.Children())
            {
                if (child is JProperty)
                {
                    JProperty property = child as JProperty;

                    if (result == null)
                        result = new Dictionary<string, string>();

                    if (property.Value.HasValues)
                    {
                        PopulateRecursively(property.Value, result);
                    }
                    else
                        result.Add(property.Path, property.Value.ToString());
                }

                if (child is JArray)
                {
                    foreach (var gchild in child)
                    {
                        foreach (JToken child1 in gchild.Children())
                        {
                            if (child1 is JProperty)
                            {
                                JProperty property = child1 as JProperty;

                                if (result == null)
                                    result = new Dictionary<string, string>();

                                if (property.Value.HasValues)
                                {
                                    PopulateRecursively(property.Value, result);
                                }
                                else
                                    result.Add(property.Path, property.Value.ToString());
                            }
                        }
                    }
                }
            }
        }

        return result;
    }

See if this helps and @Courela let me know if you want me to add this function and create PR.

[vermapraveen]

I tried to achieve from solution provided by @vikasagrawal

{
	"MessageValue": "#valueof($.Value)",
	"_FlattenChildren": "#External::External.CustomFunction::FlattenNestedJson(#valueof($.MessageValue),Children)",
	"_FlattenItems": {
		"#loop($._FlattenChildren)": {
			"_Ids": {
				"#loopwithincontext($.Items)": {
					"Ids": "#tolower(#currentvalueatpath($.Id))"
				}
			}
		}
	},
	"_IdList": "#valueof($._FlattenItems.[*]._Ids[*])",
	"_GroupedIdList": "#grouparrayby($._IdList,Ids,all)",
	"Result": {
		"#loop($._GroupedIdList)": {
			"Id": ",#tolower(#currentvalueatpath($.Ids))",
			"Path": "#currentvaluesatpath($.all[*])"
		}
	},
	"#skipnode": "#valueof($.Result)",
	"#": [
		"#delete(MessageValue)",
		"#delete($._FlattenChildren)",
		"#delete(_FlattenItems)",
		"#delete($._IdList)",
		"#delete($._GroupedIdList)",
		"#delete($.Result)"
	]
}

I wonder if so many intermediate steps/ loops are OK to achieve it..

[Courela]

#loopwithincontext? Seems that you're using an old version.

[vermapraveen]

Sure, I'll check if we can upgrade to the latest version. If not, would the transformer mentioned above be suitable from a practical perspective?

[vermapraveen]

as of now going with above solution: #280 (comment)

Thanks @vikasagrawal @Courela