Solved

Nested API Call with case / if statement in RSD file

  • 12 June 2023
  • 4 replies
  • 124 views

Userlevel 3
Badge +1

Hi community, 

I am trying to work with the LinkedIn API to retrieve details about UGC posts (i.e. engagements, clicks, etc.). This information is not part of the LinkedIn Marketing Solutions CDATA connector, so I am using the CDATA provider for REST 2022. 

I first made a call to the posts API to ‘Find Posts by Account’: Posts API - LinkedIn | Microsoft Learn. After that I need to pass each Post ID into the Organizational Entity Share Statistics API to ‘Retrieve Statistics for Specific UGC Posts’: Organization Share Statistics - LinkedIn | Microsoft Learn

The problem I am facing is that the Posts API returns two types of posts (which cannot be filtered in the API): shares and UcgPosts. Each of those types of posts needs a different URL format in the ‘Organizational Share Statistics’ API. So, I need a way to only pass one type of post into the second API. UCG posts are posts where the ID starts with 'urn:li:ugcPost:'

The file RSD 1.txt works, until the first ID from the Posts API is a UcgPost, instead of a share. The first Ucg post that gets passed into the second API returns the following error from the API: 

{"message":"Array parameter 'shares' value 'urn:li:ugcPost:7071823186039828482' is invalid. Reason: Deserializing output 'urn:li:ugcPost:7071823186039828482' failed","status":400}

So I tried to create a case statement in RSD 2.txt , but to no avail. It does not seem to be doing anything different: 

Does anyone have any experience with a filter or case statement between two tables in a nested API call like this? 

Kind regards,

Andrew - E-mergo 

icon

Best answer by Thomas Lind 17 October 2023, 15:45

View original

4 replies

Userlevel 6
Badge +5

Hi @andrew.gebhard 

You could change the setup of the file to be using a Query Slicer instead. There is a few posts about this and I mention it in the RSD guide.

Essentially you would then be able to create a Where clause that did the case statement or the query could be an or statement to select one or the other option.

SELECT * FROM LinkedInUGCPostStatistics WHERE IsUgc = true and ID IN (SELECT ID FROM Posts) OR IsUgc = false ID IN (Select ID From Posts)

 

Userlevel 3
Badge +1

Hi Thomas, 

I now have the following RSD files as attached and I have the following query table:

SELECT 
[organizationalEntity]
, [totalShareStatistics.clickCount]
, [totalShareStatistics.commentCount]
, [totalShareStatistics.engagement]
, [totalShareStatistics.impressionCount]
, [totalShareStatistics.likeCount]
, [totalShareStatistics.shareCount]
, [totalShareStatistics.uniqueImpressionsCount]
, [ugcPost]
FROM [REST].[LinkedInUGCPostStatistics CDATA]

WHERE [ugcPost] IN (SELECT [id] FROM [REST].[PostIdList] where left( [id],15)= 'urn:li:ugcPost:' )

However, I get the error:

2023-06-12T14:44:43.910+02:00    0    The parameter ugcPost is required to be set for this operation.

 

See full error file attached. Am I missing a parameter in my RSD?

 

Kind regards,

Andrew

Userlevel 6
Badge +5

Hi @andrew.gebhard 

I know this has been a bit dead for a while, but I actually think I know why you get this error.

So if the result of the query after the where clause is not a (1,2,3,4,5) or similar kind of list you will need to set in a = instead. It can’t handle only one value it seems or you will get the error message you see.

SELECT 
[organizationalEntity]
, [totalShareStatistics.clickCount]
, [totalShareStatistics.commentCount]
, [totalShareStatistics.engagement]
, [totalShareStatistics.impressionCount]
, [totalShareStatistics.likeCount]
, [totalShareStatistics.shareCount]
, [totalShareStatistics.uniqueImpressionsCount]
, [ugcPost]
FROM [REST].[LinkedInUGCPostStatistics CDATA]

WHERE [ugcPost] = (SELECT [id] FROM [REST].[PostIdList] where left( [id],15)= 'urn:li:ugcPost:' )
Userlevel 6
Badge +5

@andrew.gebhard please let us know if the answer above helps in resolving the issue you are experiencing

Reply