Web Service Source - Second Account Aggregation Operation Not Aggregating User Entitlements

[mcheek (Mark Cheek)]

I have two Account Aggregation operations and I’m trying to have the second populate a multi-valued entitlements attribute on my account schema. It doesn’t seem to be working. Here’s how it’s set up.

Account Schema

Entitlement Schema

Operation 1: Get Users
Here is a sample JSON output from this endpoint. It contains multiple user records within the $.data array

{
	"data": [{
			"last_login": "2022-02-24 22:22:45.606979",
			"status": "A",
			"user_id": 221,
			"full_name": "John Smith",
			"user_name": "John Smith",
			"flags": "E",
			"guarantee_license": 0,
			"update_ts": "2022-02-24 22:22:45.606979+00",
			"create_user": 221,
			"update_user": 1,
			"create_ts": "2021-07-03 14:44:09.592547+00",
			"email_address": "[email protected]",
			"payment_threshold": "0.00"
		},
		{
			"status": "A",
			"user_id": 199,
			"last_login": "2022-02-24 19:17:19.521672",
			"full_name": "Lisa Smith",
			"flags": "E",
			"user_name": "Lisa Smith",
			"update_ts": "2022-02-24 19:17:19.521672+00",
			"create_user": 199,
			"guarantee_license": 1,
			"payment_threshold": "0.00",
			"email_address": "[email protected]",
			"update_user": 1,
			"create_ts": "2020-10-06 19:59:24.906656+00"
		}
	],
	"header": {
		"data_rows": 2,
		"query_rows": 2,
		"primary_key": [
			"user_id"
		],
		"next_page": "https://preview-landview.peloton.com/api/v1/sys_user?%24page=2",
		,
		"sort_order": [
			"me.update_ts",
			"me.user_id"
		]
	}
}

Here is how it’s configured

Operation 2: Aggregate User Entitlements
It’s hard to see but the context URL is

/api/v1/user_group?user_id=$response.user_id$

Here is an example JSON response you get when you add the query parameter

?user_id=199

{
    "header": {
        "data_rows": 4,
        "relationships": [
            "SysGroup",
            "SysUser",
            "SysUser",
            "SysGroup",
            "SysRole",
            "SysGroupPermission"
        ],
        "primary_key": [
            "rel_id"
        ],
        "query_rows": 4,
        "sort_order": [
            "me.update_ts",
            "me.rel_id"
        ]
    },
    "data": [
        {
            "create_user": 227,
            "role_id": null,
            "update_ts": "2021-07-12 13:46:02.443743+00",
            "group_id": 123,
            "rel_id": 102,
            "create_ts": "2021-07-12 13:46:02.443743+00",
            "user_id": 199,
            "update_user": 227
        },
        {
            "user_id": 199,
            "create_ts": "2021-07-12 13:46:02.443743+00",
            "update_user": 227,
            "create_user": 227,
            "group_id": 125,
            "update_ts": "2021-07-12 13:46:02.443743+00",
            "role_id": null,
            "rel_id": 101
        },
        {
            "create_user": 227,
            "role_id": null,
            "update_ts": "2021-07-12 13:46:02.443743+00",
            "group_id": 120,
            "rel_id": 100,
            "create_ts": "2021-07-12 13:46:02.443743+00",
            "user_id": 199,
            "update_user": 227
        },
        {
            "rel_id": 99,
            "group_id": 128,
            "update_ts": "2021-07-12 13:46:02.443743+00",
            "role_id": null,
            "create_user": 227,
            "update_user": 227,
            "create_ts": "2021-07-12 13:46:02.443743+00",
            "user_id": 199
        }
    ]
}

This user_group table is essentially a join table between the sys_user and sys_group tables. sys_group is where my entitlements are pulled from.

In a post in this thread, @colin_mckibben suggests using this jsonpath tool with the Goessner output type selected.

When I use the path

$.data.[*].group_id

The output appears correct

[
   123,
   125,
   120,
   128
]

Here’s a screenshot of me checking that

This all appears that it should work, but just isn’t. Any suggestions?

[Sushantmrj (Sushant Maharjan)]

Hi @mcheek,

Could you please try the below path and see if it works for you?
$.data[:100].group_id

Thanks,
Sushant

[mcheek (Mark Cheek)]

Hi @Sushantmrj,

This sort of worked.

Here is the configuration

However, I did a spot check on a user who should have 4 entitlements, but I only see 1.

I am asking another team member if these entitlements were already assigned or of this was a result of my manual aggregation. I forgot to spot check the account I mentioned before aggregation.

[questjj (Jason Quest)]

Curious to see if you come to a resolution. I had to abandon the web service connector and bring the data down to my staging database and go jdbc connector/custom. I could never get it to agg more than 1 entitlement with source JSON of this format, just like you are reporting.

[mcheek (Mark Cheek)]

So far no success. I can’t even populate a multi-valued int field using this method. I do know that the parent-child operation structure works because I made a second call to the same table to grab an additional attribute on the account. I just can’t seem to get this to work.

[Sushantmrj (Sushant Maharjan)]

Hi @mcheek,

I created a mock service using the JSON that you posted above, and I could aggregate all group_id.

Thanks,
Sushant

[mcheek (Mark Cheek)]

@Sushantmrj can you show me your account schema?

Because it appears you are using a string value for your account ID. I am using an int. Are you able to do this using an int for account ID?

I’d like to see your entitlement schema for ‘group’ as well.

[mcheek (Mark Cheek)]

@Sushantmrj and @questjj I got this to work.

Look at my configuration for the “Aggregate User Entitlements” operation and make the following changes

1. Under Response Information, clear out the root path value

2. In response Mapping, change the attribute path to data[*].group_id