-1

I have 3 tables in a database (Records, Works and People) that I need to query and return as JSON. The data looks something like:

ID | Record | Work   | People
_____________________________
1  | Rec 1  | Work 1 | Mr A
1  | Rec 1  | Work 1 | Mrs A
1  | Rec 1  | Work 1 | Mr B
2  | Rec 2  | Work 2 | Mr C
3  | Rec 3  | Work 2 | Mr C

So a Record is linked to a Work and that can have multiple People against it. The resulting JSON should look something like:

[ { Record: "Rec 1"
  , Work: "Work 1"
  , People: [ { Name: Mr A },
              { Name: Mrs A },
              { Name: Mr B }
            ]
  },
  { Record: "Rec 2"
  , Work: "Work 2"
  , People: [ { Name: Mr C } ]
  } ,
  { Record: "Rec 3"
  , Work: "Work 2"
  , People: [ { Name: Mr C } ]
  }
]

My question is; in MuleSoft what would be the best way to achieve this?

  1. Have 3 query components and then use a Scatter/Gather to stitch the results together.
  2. Have 1 stored procedure call that returns the data as shown above and then use DataWeave to unpick it.
  3. Have 1 stored procedure call that does all the querying and builds the JSON payload for me.

This is related to performance mainly and then reduced complexity. The number of records could run into the millions, so I need the most efficient way to do it. I'm leaning towards letting the database do the work and return the pre-formatted JSON, but I'm not sure if there are any downsides to doing that.

DevTC
  • 1
  • 3
  • 1
    This kind of question tends to invite opinion based answers which are not appropriate for Stackoverflow. – aled Jan 12 '23 at 15:23
  • 1
    See Opinion Based at Help Center https://stackoverflow.com/help/closed-questions for details. – aled Jan 12 '23 at 18:02
  • are there 3 tables or 3 columns in 3 tables. The sample you shared looks like 3 column in 1 table – Harshank Bansal Jan 12 '23 at 20:09
  • @HarshankBansal It's 3 tables. You can see the sample as a database view that joins the 3 tables returning a column from each. I've simplified it for this example, but there are actually lots of columns from each table in my system. – DevTC Jan 12 '23 at 21:42

2 Answers2

0

Assuming this is an API you should only have 1 Query scope and update your procedure to gather all this data as part of a SQL Join function then use dataweave to filter and build your response structure.

TheElderlyProgrammer
  • 16
  • 1
  • Make sense, thanks. Do you know what Dataweave functionality I could look at to filter out the distinct data items? I would use 'map' to loop through the combined data, but for example, how would I separate out the 3 People on the same Record , so that it can be enclosed in a JSON array? – DevTC Jan 12 '23 at 16:23
  • Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Jan 12 '23 at 19:48
  • Sorry, that should have said "Make**s** sense"! – DevTC Jan 12 '23 at 21:45
  • You can use filter using the the distinctBy functionality in Dataweave. [See this article](https://developer.mulesoft.com/tutorials-and-howtos/dataweave/distinctBy-function/) or [the documentation](https://docs.mulesoft.com/dataweave/2.4/dw-core-functions-distinctby) – TheElderlyProgrammer Jan 13 '23 at 17:15
0

There is not a predetermined best way. Test the different methods and compare the results in speed and resource usage. Tune configurations to detect possible improvements. Specially with Mule 4 database queries with high volumes you'll want to disable repeatble streams for performance.

aled
  • 21,330
  • 3
  • 27
  • 34
  • Thanks, I was just hoping to tap in to others experiences on this. I don't really have to the time to implement several different ways and test which is the most performant. – DevTC Jan 12 '23 at 16:25
  • It will be very dependent on your particular environment. – aled Jan 12 '23 at 16:47