LINQ Reporting Engine – How to Join Two Arrays in JSON Using a Common Key

Aspose.Words Product Family
1

Hi Aspose Team,

I’m using Aspose.Words with the LINQ Reporting Engine, and I have a JSON data source with two separate arrays: one contains person names and IDs, and the other contains addresses and person IDs. I want to join these two using the personId key.

Here’s a simplified version of my JSON:’

{
  "persons": [
    { "personId": 1, "name": "John Doe" },
    { "personId": 2, "name": "Jane Smith" }
  ],
  "addresses": [
    { "personId": 1, "address": "123 Main St" },
    { "personId": 2, "address": "456 Elm St" }
  ]
}

I’ve tried the following LINQ expression in my template:

<<foreach [p in persons]>>
Name: <<[p.name]>>
Address: <<[addresses.filter(a => a.personId == p.personId)[0].address]>>
<</foreach>>

But the address is not appearing in the output.

I also tried using .where() and .firstOrDefault() but still no luck.

Could you please guide me on the correct syntax to perform this join using the Reporting Engine?

Thank you!

2

@vikas_kaushal To achieve what you need you should specify data source name and use the following syntax:

<<foreach [p in data.persons]>>
Name: <<[p.name]>>
Address: <<[data.addresses.Where(a => a.personId == p.personId).First().address]>>
<</foreach>>

JsonDataSource ds = new JsonDataSource(@"C:\Temp\data.json");

Document doc = new Document(@"C:\Temp\in.docx");

ReportingEngine engine = new ReportingEngine();
engine.BuildReport(doc, ds, "data");

doc.Save(@"C:\Temp\out.docx");

data.zip (276 Bytes)
in.docx (14.2 KB)
out.docx (11.2 KB)

3

hi @alexey.noskov Thank you for the response.

However, the suggested solution doesn’t work in my case because I’m not using a separate JSON file as a data source. Instead, the data is passed to the engine as a string (inline JSON content), structured like this:

{
  "SourceData": "{\n\t\"program\": {\n\t\t\"text\": \"Lawyers Professional Liability\",\n\t\t\"value\": \"LPL\"\n\t},\n\t..."
}

So the data is embedded as a string value, not a directly navigable JSON object. As a result, the standard approach using JsonDataSource(filePath) or querying subcollections like data.persons or data.addresses.Where(...) does not work directly. can you suggest any other way.

4

@vikas_kaushal There is no difference from where data is loaded. For example the following code demonstrates how to load JSON from string:

string json = "{\r\n  \"SourceData\": \"{\n\t\"program\": {\n\t\t\"text\": \"Lawyers Professional Liability\",\n\t\t\"value\": \"LPL\"\n\t},\n\t...\"\r\n}";
JsonDataSource ds = new JsonDataSource(new MemoryStream(Encoding.UTF8.GetBytes(json)));
5

Could you please suggesst what i have to use then in data source.

6

@vikas_kaushal Could you please elaborate the question? Sample template, data and output are provided in my answer above.

7

can you tell me what i have to use as data source name?
I have tried yours, but its not working for me.

8

@vikas_kaushal You should specify data source name as specified in your template:

711×98 2.83 KB

9

hi @alexey.noskov we are not using any root in code

engine.BuildReport(
       doc,
       new object[] { jsonDataSource, systemContext },
       new[] { "", "SystemContext" }  // unnamed root, plus named helper
);
10

@vikas_kaushal Unfortunately, without root data source name there is no way to access data from root data source in foreach.