2

In a CDP datalake the profiles table features the identityMap field:

[IdentityType -> IdentityValue]

A sample value looks like the following:

["crmID" -> "{(NULL,crm123,NULL)}", 
 "loyaltyID" -> "{(NULL,loy456,NULL)}", 
 "accountID" -> "{(NULL,act789,NULL)}"]

To validate the ingestion and merging process of the IDs the client would like to run some checks on that map field such as:

  • How many keys are there for each row
  • How many values are there for each key

I used to be proficient on TSQL, but those maps structure are new to me and not sure how to explore those data.

Which kind of functions or syntax can be used to fetch the identityMap field? Please feel free to share just only references to public documentation.

Alex Ott
  • 80,552
  • 8
  • 87
  • 132
Alberto De Caro
  • 5,147
  • 9
  • 47
  • 73

1 Answers1

2

If you need to do that using SQL only, then you need to use square brackets [] to access values (see docs). They work with both arrays & maps, just for arrays you need to provide index, while for maps you need to provide value, like, column_name['crmID'].

P.S. You may also need to use functions like, map_keys, map_values, etc. - just follow the links from the bottom of the linked page

Alex Ott
  • 80,552
  • 8
  • 87
  • 132