Azure Search Indexer for Complex Data Type

Question

We have been using Azure Search for a number of years and it has been working well. I just became aware of the new complex data types, and am very interested in implementing them. I can find good examples of how to create the index, but my challenge is with how to implement the indexer.

We use indexers to our SQL Server database, with the integrated change detection enabled to update our indexes automatically when we run the indexer. For this to work, the datasource must point to a table.

For our current implementation, we have flattened our data to place several multi-value fields into lists so that they can be brought into the index. It would be awesome if we could avoid having to do this flattening.

As an example, let's assume we are indexing song data, and that data includes a SongID, Title, AuthorIDs and AuthorNames. The source data comes from a Song table and an Author table, both of which include a SongID. Our current flattened table contains entries such as:

SongID: S9182
Title: My awesome new song
AuthorIDs: ["A12345","A67890"]
AuthorNames: ["John Smith","Sally Smith"]

Any pointers on how I could accomplish this? My only thought is to have multiple indexers. One for the base information, and others for each complex data type. But that does not seem very elegant.

Thanks!

How is your data stored before being flattened and stored in SQL databases? If your datasource and index are already set up for complex types, then the indexer should "just work". — 8163264128
I added more information to my question, but basically there is a base table "Song", and then separate tables, such as "Author". There can be more than one Author, but these tables are linked via a SongID. So are you suggesting that I have one indexer for the base table, and then a second indexer for the Authors? — Jim Carlson

Sophiac Sophiac · Accepted Answer · 2019-06-20T18:30:19

In order to index two tables, you would need to create two indexers (data source 1 for table 1, datasource 2 for table 2; indexer 1 for data source 1, indexer 2 for data source 2). You could create a view between the two tables, but then you'd lose the integrated change tracking.

Sounds like you have the complex index field part hashed out. But to be thorough and provide an example: If your data was store as JSON inside a SQL field, and the corresponding index field is set up as a complex type, the indexer would automatically index the fields inside the JSON. For example:

Source data:

{
    "SongID": "S9182",
    "Title": "My awesome new song",
    "AuthorIDs": ["A12345", "A67890"],
    "AuthorNames": ["John Smith", "Sally Smith"]
}

Index field definition:

{
    name: "fieldName",
    type: "Edm.ComplexType",
    fields:[
        {
            name: "songID",
            type: "Edm.String"
        },
        {
            name: "Title",
            type: "Edm.String"
        },
        {
            name: "AuthorIDs",
            type: "Collection(Edm.String)"
        },
        {
            name: "AuthorNames",
            type: "Collection(Edm.String)"
        }
    ]
}

With the data shape and index definition above, the indexer would automatically index the fields inside the JSON into the index.

Let me know if there are more questions.

Azure Search Indexer for Complex Data Type

1 Answers