AnsweredAssumed Answered

Cartesian Join of Database Cache in JSON Array

Question asked by trey.yeager826393 on Oct 19, 2018
Latest reply on Oct 20, 2018 by leif_jacobsen

I have the following data in 3 tables.

 

COMM

comm_cd
55555555

 

 

COMM_INCL

comm_cdincl_type_cd
55555555A
55555555B

 

COMM_LOC_DETAIL

comm_cdsite_loc_nosite_loc_name
555555551234Site_1234
555555555678Site_5678

 

I need to map this data to a Json profile that would look something like this:

{
   "comm_cd": "55555555",
   "includes": ["A", "B"],
   locationDetails: [
      {
         "site_loc_no": "1234",
         "site_loc_name": "Site_1234"
      },
      {
         "site_loc_no": "5678",
         "site_loc_name": "Site_5678"
      }
   ]
}

 

The main problem is that if I use a single database profile to join all of this data together it ends up doing a Cartesian join and I end up with duplicates in both the "includes" array and the "locationDetails" array.

 

What I have tried doing is having my database profile only join the COMM and the COMM_LOC_DETAIL tables together. I then do a separate lookup to the COMM_INCL table and store that in a cache which I then add to the source profile of my map. However, when I map that cache over that ends up giving me duplicates in the "includes" array.

 

So my question is how can I use the above tables to create the desired Json?

Outcomes