How do I access attribute matrices through SQL?

Question

Originally posted by Unknown

I have added an attribute matrix as an attribute to a person. How do I query the data from the matrix via SQL?

Answer 1

Originally posted by Unknown

Before diving into SQL, it would be helpful to understand how attributes matrices work. In this example, we have an attribute matrix template called "Name and Email List" that contains two attributes: Name and Email.

Also, I created a new person attribute called "Name and Emails" of field type Matrix and added two items to a person's record:

To help understand how this data is structured in the database, it is best to think of each Matrix as a grid of Matrix Items and Attributes. Also, note that each person would have their own Matrix which contains Matrix Items specific to them.

	Name (Attribute)	Email (Attribute)
Matrix Item 1	Test Person	test@person.com
Matrix Item 2	Test Person 2	test@person2.com

Now let's say we want to write a query where given a person, we want to return a list of names and emails. This is how I would write that query:

SELECT
     P.[Id] AS [PersonId]
    ,MAV1.[Value] AS [Name]
    ,MAV2.[Value] AS [Email]
FROM [Person] P

-- The person attribute and value
INNER JOIN [AttributeValue] AV ON AV.[EntityId] = P.[Id]
INNER JOIN [Attribute] A ON A.[Id] = AV.[AttributeId] AND A.[Key] = 'NamesandEmails'

-- The attribute matrix is connected to the person attribute by its guid.
INNER JOIN [AttributeMatrix] M ON M.[Guid] = TRY_CAST(AV.[Value] AS uniqueidentifier)
INNER JOIN [AttributeMatrixItem] MI ON MI.[AttributeMatrixId] = M.[Id]

-- the "name" attribute of the matrix. This is connected to the matrix item.  
INNER JOIN [AttributeValue] MAV1 ON MAV1.[EntityId] = MI.Id
INNER JOIN [Attribute] MA1 ON MA1.[Id] = MAV1.[AttributeId] AND MA1.[Key] = 'Name1'

-- The "email" attribute of the matrix. This is connected to the matrix item. 
INNER JOIN [AttributeValue] MAV2 ON MAV2.[EntityId] = MI.[Id]
INNER JOIN [Attribute] MA2 ON MA2.[Id] = MAV2.[AttributeId] AND MA2.[Key] = 'Email'

-- The person id of the person we want to view
WHERE P.[Id] = 1

Let me know if something is not clear and I will update the answer.

Comments

This is very helpful. Thank you!