One-to-many relationships and map item attributes

Map item attributes: RPI custom attributes

For information about map item attributes, refer to https://docs.redpointglobal.com/rpi/map-item-attribute-properties.

Map attributes are a Redpoint Interaction (RPI) feature that allows you to reference a field in a table which contains a simple JSON object. The object should only contain key-value pairs. If you have data in a one-to-many relationship and want to use data on the many side of the relationship to export or personalize with, you may want to consider using map items.

To use map items, you need to have a field in the database that is JSON formatted text with a set of key-value pairs. You can take the data that is in the table, which has many records, and create a new table that has two fields:

• The key that the relationship is based on between the existing tables

• A field that contains the JSON formatted text that will contain a set of key-value pairs representing the data of the multiple records on the many side of the relationship

Then you can use these keys and values for exporting and personalization.

They are limited to key-value pairs, so if there are more fields than a name and a value in the table, such as additional fields that need to be used from the many side of the relationship, then this may not be a good approach for you. There may be other ways to incorporate the additional fields into the key name to accommodate the additional fields, but if the data can’t be broken down into a key and value then map items will not be a good solution for you.

Map item example

Here is a simple illustration of how this approach would work.

Data related to example

Let’s say that we have a one-to-many (1:M) relationship between provider and links as this high-level entity relationship diagram (ERD) shows.

And we have a set of data in each of those tables to support this example. An illustration of the data in table format is listed below.

Provider table

Provider web links table

The provider web links table can have many records for a single provider_id and represents the many side of this example. There is an added column called link_id in this table that is not in the ERD above, but was added to make all of the rows unique. The reason is the last provider_id (3) has two link_names that are the same, but have different values. The expectation is when generating the JSON for the map data, which we will Illustrate next, you would enforce that only one unique link_name is added to the JSON. This row has been added to illustrate what happens when that restriction is not enforced.

Provider links JSON table

Based on the provider web links table, you will create a new table with two columns, which consists of a primary key/join field, which is provider_id in this example, and a second field that will store the JSON object, link_map in this example.

The data type of the field that will store the JSON object depends on the database technology for the data warehouse or aux db the map item is being configured for. The RPI documentation page on this feature should be referenced to get the latest detail on what databases and data types are supported.

In this example we are using MS SQL and a text field, specifically [nvarchar](max)), that will contain the JSON formatted text from the links table. In our example table, provider_links_json, the field containing the JSON is called link_map. The reference DDL is below.

Provider links JSON reference DDL

CREATE TABLE [dbo].[provider_links_json](
	[provider_id] [int] NULL,
	[link_map] [nvarchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Provider links JSON reference ERD

This is what the ERD looks like with the new provider links JSON table, which will be joined to the provider table one-to-one (1:1) on provider_id.

Provider links JSON table sample

Below is a example of what the provider_links_json table would look like based on the links table.

Below are examples of the JSON for two of the providers, formatted for easier readability.

JSON for provider 1:

{
  "Provider Photo Web Link  2": "a",
  "Leap Profile Image": "b",
  "About Me - Spanish": "c",
  "Web Page Link - Spanish": "d"
}

JSON for provider 3:

This example is valid JSON, but it is not recommended to have two keys with the same name. We want to use this example to show what happens if data is generated in this way.

{
  "Provider Photo Web Link  2": "3",
  "Provider Photo Web Link  2": "2"
}

Configure RPI to use data with map item

Now that we have the new table to support the use of map item, we will go into RPI and do the following:

1. Sync catalog.

2. Create join between provider table and new table (provider_links_json) on the key, which is provider_id in this case.

3. Create map item attributes using this new table, configure the map item, and configure additional map item attributes (refer to the next subsections for detailed instructions).

Create a map item attribute using new table

Now within RPI you need to create custom attributes, which are map items, to access the data within the JSON field.

• Create a new attribute and select the Item within a Map Column.

Configure the map item

1. Provide an Attribute Name.

2. Provide an Attribute Description (optional).

3. Select the Map Column that should be referenced in the database. In the example, the table is provider_links_json, and the field name is link_map.

4. Next set the Key Value to select with this custom attribute. Include the quotes with the key name: "Provider Photo Web Link 2" in this example.

5. Then set the Data Type of the value that will be returned. In this case we left it as String.

6. Save it in the appropriate location.

Configure additional map item attributes

Now that we have a Map Item Attribute created based on the provider_links_json table, we can use that attribute to create additional ones based on the same table and field.

1. Identify the previous created attribute in the file system, right-click it, and select copy.

2. Then enter the name of the new attribute you want to create, Provider Links Map Item - About Me -- Spanish in this example, and the location to save it.

3. Then you can right-click the new attribute in the file system and select Configure attribute.

4. Update the description, key value, and the data type to reference the additional key you want to reference in the JSON object.

5. Save the attribute, and then you are ready to use it.

You can create additional attributes in RPI this way if you know the Key Value and Data Type.

Illustrate the map item attribute behavior using an export template

Next, we will create an export template using the map item attributes to illustrate their behavior.

Configure an export template

1. Give the export a name and configure the resolution to align with your configuration, in our case that is provider. Set the additional options as you see fit.

2. Add Attributes to the export.

Review export template behavior

Now that we have created the export template, we can use a selection rule against the provider resolution and then use the data viewer tool with the export template to review the data.

Simple selection rule:

Data viewer:

1. There are null values for two of the records for “About Me - Spanish” because those two records do not have a key with that name in their JSON field.

2. For Provider ID 3 the result for “Provider Photo Web Link 2” is 3. We know that we set up that record to have duplicate keys. The value of the second key is a 2, but we got the value of 3 in the data viewer. The reason is that RPI will pick the value of the first key name it encounters within the JSON and return that. So it is recommended to prevent this when creating the JSON that is loaded to this field.

Additional considerations

1. Duplicate keys
   Having duplicate keys in your JSON field for the map attribute will not generate an error, but could generate various results. The map item attribute will return the value of the first key name it finds in the JSON. It is recommended not to allow duplicate keys when creating the JSON.

2. Performance
   There are various ways these attributes can be used within exports and personalization. For example, if the attributes are used in smart assets and if the content you are creating contains many different map attributes for a single piece of content. Using all the available features of RPI may result in variable performance. You should test this feature with your specific use case to ensure you get adequate performance. If you do not, then alternate options may need to be considered.

Reference links

• MS SQL - JSON Support with Text Fields as well as JSON objects where supported: https://learn.microsoft.com/en-us/sql/relational-databases/json/store-json-documents-in-sql-tables?view=sql-server-ver16