Json array with in Json object

Help StudioX
, ,
1

Hello all,

Seen few solution how to desterilize object
But i have object with some arrays inside

JObject(9) { JProperty(1) { [SOL-111-BC] }, JProperty(1) { [test2] }, JProperty(1) { [Corporate] }, JProperty(1) { [Low] }, JProperty(1) { [010] }, JProperty(1) { [HK] }, JProperty(1) { JArray(14) { JObject(1) { JProperty(1) { [AQ] } }, JObject(1) { JProperty(1) { [BF] } }, JObject(1) { JProperty(1) { [CK] } }, JObject(1) { JProperty(1) { [DK] } }, JObject(1) { JProperty(1) { [FM] } }, JObject(1) { JProperty(1) { [FO] } }, JObject(1) { JProperty(1) { [GP] } }, JObject(1) { JProperty(1) { [MX] } }, JObject(1) { JProperty(1) { [NG] } }, JObject(1) { JProperty(1) { [PN] } }, JObject(1) { JProperty(1) { [SK] } }, JObject(1) { JProperty(1) { [SZ] } }, JObject(1) { JProperty(1) { [TG] } }, JObject(1) { JProperty(1) { [WS] } } } }, JProperty(1) { JArray(5) { JObject(1) { JProperty(1) { [DM] } }, JObject(1) { JProperty(1) { [GQ] } }, JObject(1) { JProperty(1) { [HT] } }, JObject(1) { JProperty(1) { [SN] } }, JObject(1) { JProperty(1) { [YT] } } } }, JProperty(1) { JObject(3) { JProperty(1) { [0] }, JProperty(1) { [0] }, JProperty(1) { [0] } } } }

Im trying to achieve datatable with all array’s under same column from example array a under column a

Any tips

2 
@"{
    ""C_ID"": ""xxx-12312-xx"",
    ""CustomerName"": ""Test"",
    ""CustomerType"": ""Corporate"",
    ""RiskLevel"": ""Low"",
    ""BranchCode"": ""010"",
    ""CountryOfIncorporation"": ""HK"",
    ""IncomingTerritories"": [
        {
            ""CountryCode"": ""AQ""
        },
        {
            ""CountryCode"": ""BF""
        },
        {
            ""CountryCode"": ""CK""
        },
        {
            ""CountryCode"": ""DK""
        },
        {
            ""CountryCode"": ""FM""
        },
        {
            ""CountryCode"": ""FO""
        },
        {
            ""CountryCode"": ""GP""
        },
        {
            ""CountryCode"": ""MX""
        },
        {
            ""CountryCode"": ""NG""
        },
        {
            ""CountryCode"": ""PN""
        },
        {
            ""CountryCode"": ""SK""
        },
        {
            ""CountryCode"": ""SZ""
        },
        {
            ""CountryCode"": ""TG""
        },
        {
            ""CountryCode"": ""WS""
        }
    ],
    ""OutgoingTerritories"": [
        {
            ""CountryCode"": ""DM""
        },
        {
            ""CountryCode"": ""GQ""
        },
        {
            ""CountryCode"": ""HT""
        },
        {
            ""CountryCode"": ""SN""
        },
        {
            ""CountryCode"": ""YT""
        }
    ],
    ""PaymentForecast"": {
        ""MaxMonthlyPayments"": 0,
        ""MaxSinglePayment"": 0,
        ""VolumePerMonth"": 0
    }
}"
3

hi @Hazem_Saleh

You would have to use Nested loops to read the values from the IncomingTerriroties and OutgoingTerritories, and insert those into a single string type object and then add to data table.

regards,
Aditya

4

lets assume the json is deserialized to a JObject - myJObject

not complete clear, but maybe you are looking for this:
grafik
myJObject("IncomingTerritories").ToObject(Of DataTable)

5

He wants to convert all the above mentioned JSON Data in one go.

6

better he will give the expected output datatable sample, so the use case is clear defined

cannot mapped all above mentioned JSON Data to

So:
all incoming /outcomings countries we can do:
grafik

For bringing all JObject Properties to a 1-row datatable then details as PaymentForecast handling is incomplete defined as it is a JObject and not a JArray

7

i tried to do that but went very messy

Do you have small example?

Thanks

8

I want

C_ID CustomerName IncomingTerritories
xxx-12312-xx Test AQ
Bf
…

OR

C_ID CustomerName IncomingTerritories
xxx-12312-xx Test AQ
xxx-12312-xx Test Bf
…

Its not a problem to have duplicate row excepts where is array.

Thanks

9

Here is smaller version

{
  "SolutionId": "SOL-111-BC",
 
  "CountryOfIncorporation": "HK",
  "IncomingTerritories": [
    {
      "CountryCode": "AQ"
    },
    {
      "CountryCode": "BF"
    }
 
  ],
 
  "PaymentForecast": {
    "MaxMonthlyPayments": 0,
    "MaxSinglePayment": 0,
    "VolumePerMonth": 0
  }
}
10

please specify all needed columns.

When PaymentForecast is also PART of the Columnlist then please show us the entire expected output, especially on how different cardinalities and nested JObjects are expected in the definition

11

Hi @Hazem_Saleh

Check if this helps:
testdata.txt (1.4 KB)
JSONData.xaml (10.6 KB)
CustomerData.xlsx (7.5 KB)

Thanks,
Aditya

13

Hello
sorry this vertical table
image

If i manage the country code part I can apply it on all ,
@adiijaiin he’s helping out but couldn’t open it on my end

Regards

14

your solution worked perfect, im not very good in vb,
I will try to define it for all objects :smile:
Can we separate IncomingTerritories and outgoing?

Thanks again for the help

15

YEs you can separate them,

incomingCountryDT= JArray.FromObject(jObj(“IncomingTerritories”)).ToObject(Of DataTable)
outgoingCountryDT= JArray.FromObject(jObj(“OutgoingTerritories”)).ToObject(Of DataTable)

Loop them both separately.
In the buildData Table if you want the data to be separated in IncomingTerritories and outgoingTerritories something like this
image
Loop for incomingTerritories:
give input in Add Data Row as following:
image

Loop for outgoingTerritories:
give input in Add Data Row as following:
image

That should work for you.
And I hope you’re able to fetch other data that you need.

Chees! Happy Automation!

Please mark as Solution if that has helped!

Thanks,
Aditya

16

Worked like a charm,
Thanks alot :smiling_face_with_three_hearts:

1 Like
17

can you show me the format in c#

seems if i use same structurer its always says missing ; i changed it but no luck

Regards

18

Sorry i meant the add row to the table {ID,Name }

19

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.