I want to flatten my JSON with nested array object.

For example, my current JSON from Cosmos DB is:

[
    {
        "id": "",
        "name": "",
        "type": "",
        "Data": [
            {
                "id": "",
                "name": "aaa",
                "value": "100"
            },
            {
                "id": "",
                "name": "bbb",
                "value": "200"
            }
        ]
    }
]

I want to transform it to:

[
    {
        "id": "",
        "name": "",
        "type": "",
        "aaa": "100",
        "bbb": "200"
    }
]

Basically, I want to use values of "Data.name" as key and "Data.value" as value in root structure.

CodePudding user response：

Hi I’m Wayne Wang from the Microsoft for Founders Hub team!

I wrote this script using .net 5 function app with System.Text.Json 6.0.4 package

using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Text.Json.Nodes;
using Microsoft.Azure.Functions.Worker;
using Microsoft.Azure.Functions.Worker.Http;
using Microsoft.Extensions.Logging;

namespace FunctionApp2
{
    public static class Function1
    {
        [Function("Function1")]
        public static HttpResponseData Run([HttpTrigger(AuthorizationLevel.Function, "get", "post")] HttpRequestData req,
            FunctionContext executionContext)
        {
            var logger = executionContext.GetLogger("Function1");
            logger.LogInformation("C# HTTP trigger function processed a request.");




            var stringInput = @"[
    {
        ""id"": """",
        ""name"": """",
        ""type"": """",
        ""Data"": [
            {
                ""id"": """",
                ""name"": ""aaa"",
                ""value"": ""100""
            },
            {
                ""id"": """",
                ""name"": ""bbb"",
                ""value"": ""200""
            }
        ]
    }
]"; //or you can get it from post;

            var jr = JsonNode.Parse(stringInput);
            var jcol = jr.AsArray().Select(arrayItem =>
            {
                var obj = arrayItem.AsObject();
                var rval = new JsonObject();
                CopyValue(obj, rval, "id");
                CopyValue(obj, rval, "name");
                CopyValue(obj, rval, "type");
                if (obj.TryGetPropertyValue("Data", out var pnode))
                {
                    var dataArray = pnode.AsArray();
                    foreach (var itemDataObject in dataArray.Select(x => x.AsObject()))
                    {
                        if (itemDataObject.TryGetPropertyValue("name", out var namep))
                        {
                            if (itemDataObject.TryGetPropertyValue("value", out var valuep))
                            {
                                rval.Add(namep.GetValue<string>(), valuep.GetValue<string>());
                            }
                        }

                    }

                }
                return rval;
            });
            var newjr = new JsonArray(jcol.ToArray());


            var response = req.CreateResponse(HttpStatusCode.OK);
            response.Headers.Add("Content-Type", "text/plain; charset=utf-8");

            response.WriteString(newjr.ToJsonString());

            return response;
        }

    
        private static void CopyValue(JsonObject from, JsonObject to, string propName)
        {
            if (from.TryGetPropertyValue(propName, out var pnode))
            {
                to.Add(propName, pnode.GetValue<string>());
            }
        }
    }
}

CodePudding user response：

You can achieve this using parse JSON and compose connectors in logic apps. After reproducing below is the logic app that worked for me.

I have initialized the variable in order to retrieve the Data.name and Data.Value.

In the above step, I was trying to retrieve all Data.name and Data.value values present in the JSON file.

and then I'm finally building the whole flattened JSON using compose connector.

RESULTS