Where emails for List Manager are stored

Question

Sitecore 9.1

When I manually add an email address to a list, I am wondering where the list and xDB contact are associated. Looking at the code, it looks like it would be a contact facet. But when I pull the facets for my contact, nothing sticks out. Any idea where the list is assigned to the contact or the the contact is assigned to the list. I was the understanding it all happens on the xDB contact.

Update

It looks like the connection is in the contact facets as a facet named ListSubscriptions. But I have not been able to tie one back to a known contact yet.

SELECT TOP (1000) [ContactId]
      ,[FacetKey]
      ,[LastModified]
      ,[ConcurrencyToken]
      ,[FacetData]
  FROM [sitecore_Xdb.Collection.Shard0].[xdb_collection].[ContactFacets]
  WHERE FacetKey = 'ListSubscriptions'

Answer 1

Thanks to Pete Navarra, I was actually only searching one shard for the contact ID, for the user. In the end it was right where he thought it was. As a ListSubscriptions facet on the contact.

To get it you can use this SQL

// shard 0
SELECT TOP (1000) [ContactId]
      ,[FacetKey]
      ,[LastModified]
      ,[ConcurrencyToken]
      ,[FacetData]
  FROM [sitecore_Xdb.Collection.Shard0].[xdb_collection].[ContactFacets]
  WHERE FacetKey = 'ListSubscriptions'
    AND ContactId = '5C722EA3-A86C-0000-0000-0596BE30D17D'

// shard 1
SELECT TOP (1000) [ContactId]
      ,[FacetKey]
      ,[LastModified]
      ,[ConcurrencyToken]
      ,[FacetData]
  FROM [sitecore_Xdb.Collection.Shard1].[xdb_collection].[ContactFacets]
  WHERE FacetKey = 'ListSubscriptions'
    AND ContactId = '5C722EA3-A86C-0000-0000-0596BE30D17D'

This will output a facet that has the list ID in it.

{
   "@odata.type":"#Sitecore.XConnect.Collection.Model.ListSubscriptions",
   "Subscriptions":[
      {
         "Added":"2019-07-25T17:31:32.0116076Z",
         "IsActive":true,
         "ListDefinitionId":"a6ee7af9-7e3b-48af-beea-cfd3ce4526f9",
         "SourceDefinitionId":"eef0f298-b272-4e1b-b9e5-c0a7fc529b55"
      }
   ]
}

And that translates to this url in the sitecore list manager that fills the list of emails in the current list.

https://mysite/sitecore/api/lists/a6ee7af9-7e3b-48af-beea-cfd3ce4526f9/contacts?pageSize=20&pageIndex=0&payLoad=full&language=en&filter=&sc_lang=en

Answer 2

To map email to known contact you should assign existing identifier source that is used for email identifier in your solution.

You can find more information here: https://doc.sitecore.com/users/92/sitecore-experience-platform/en/add-a-contact-to-a-list.html.

For querying all contacts lists from xDb you can also use query:

SELECT ContactId, ListId, SourceId, IsActive, Added
  FROM [sitecore_Xdb.Collection.Shard0].[xdb_collection].[ContactFacets]

  CROSS  APPLY OPENJSON (FacetData, N'$.Subscriptions')
   with (
       ListId nvarchar(max) N'$.ListDefinitionId',
        SourceId nvarchar(max) N'$.SourceDefinitionId',
        IsActive bit N'$.IsActive',
        Added  nvarchar(max) N'$.Added'
    )
    where FacetKey = 'ListSubscriptions'

UNION  

SELECT ContactId, ListId, SourceId, IsActive, Added
  FROM [sitecore_Xdb.Collection.Shard1].[xdb_collection].[ContactFacets]

  CROSS  APPLY OPENJSON (FacetData, N'$.Subscriptions')
   with (
       ListId nvarchar(max) N'$.ListDefinitionId',
        SourceId nvarchar(max) N'$.SourceDefinitionId',
        IsActive bit N'$.IsActive',
        Added  nvarchar(max) N'$.Added'
    )
    where FacetKey = 'ListSubscriptions'