How To: Create a Joined Hosted Feature Layer View Using ArcGIS API for Python

Summary

A joined hosted feature layer view uses combined data from two different sublayers. These sublayers can be from the same or different hosted feature layers or table layers based on a relationship between nonspatial attributes in each layer. Joined views are useful for dynamically updating data from two layers alongside the source layer. It is possible to use ArcGIS API for Python to programmatically create a joined hosted feature layer view without accessing the layer on Map Viewer. This applies to both ArcGIS Online and Portal for ArcGIS. This article demonstrates a 1:1 (one-to-one) join relationship configuration, which is achieved using the topFilter parameter in a standalone script or ArcGIS Notebook. The topFilter with topCount: 1 ensures that only one matching record from the join table is returned for each record in the source layer.

Procedure

Import the necessary modules and specify the credentials.

from arcgis.gis import GIS
from arcgis.features import FeatureLayer, Table, FeatureLayerCollection

#For ArcGIS Online
gis = GIS("ORGANIZATION_URL", "username", "password")

#For ArcGIS Notebook
gis = GIS("Home")

#For Portal for ArcGIS
gis = GIS("https://<machine>.<domain>.com/<web_adaptor_name>", "username", "password", verify_cert=False)

Specify the layers to be joined using the services URL.

featurel_url = "https://services.domain.com/<xxxxxxxxxxx>/arcgis/rest/services/<feature_layer>/FeatureServer/<feature_id>"
joinl_url = "https://services9.arcgis.com/<xxxxxxxxxxx>/arcgis/rest/services/<join_feature_layer>/FeatureServer/<feature_id>"

fl = FeatureLayer(fl_url, gis)
tbl = Table(tbl_url, gis)

Add an index on the join field to both services.

index_to_add = {"indexes":[
    {
        "name": "<field>_Index", 
        "fields": "<field>", 
        "isUnique": False, 
        "isAscending": True, 
        "description": "<field>_Index"
    }
]}

fl.manager.add_to_definition(index_to_add)
tbl.manager.add_to_definition(index_to_add)

Create a blank view service and initialize run the FeatureLayerCollection() function using the blank view as the input.

view_service = gis.content.create_service(name="joined_view", is_view=True)
view_flc = FeatureLayerCollection.fromitem(view_service)

Select the fields required to be included in the layer view.

sourceFeatureLayerFields = [
    {
        "name": "<field1>",
        "alias": "<field1_alias>",
        "source": "<field1>"
    },
    {
        "name": "<field2>",
        "alias": "<field2_alias>",
        "source": "<field1>"
    },
    {
        "name": "<field3>e",
        "alias": "<field3_alias>",
        "source": "<field3>"
    },
    {
        "name": "<field4>",
        "alias": "<field4_alias>",
        "source": "<field4>"
    }
]

sourceTableFields = [
    {
        "name": "<join_field1>",
        "alias": "<join_field1_alias>",
        "source": "<join_field1>"
    }
]

Create a definition from the specified information in Step 5 and generate the joined hosted feature layer view.

field_to_join_on = "<Field>"
view_lyr_name = "<view_layer_name>"
definition_to_add = {
  "layers": [
    {
      "name": view_lyr_name,
      "displayField": "",
      "description": "AttributeJoin",
      "adminLayerInfo": {
        "viewLayerDefinition": {
          "table": {
            "name": "sampleJoinedView",
            "sourceServiceName": fl.properties.name,
            "sourceLayerId": 0,
            "sourceLayerFields": sourceFeatureLayerFields,
            "relatedTables": [
              {
                "name": "testjoin",
                "sourceServiceName": tbl.properties.name,
                "sourceLayerId": 0,
                "sourceLayerFields": sourceTableFields,
                "type": "LEFT",
                "parentKeyFields": [
                  field_to_join_on
                ],
                "keyFields": [
                  field_to_join_on
                ],
                "topFilter": {
                  "groupByFields": field_to_join_on,
                  "orderByFields": "OBJECTID ASC",
                  "topCount": 1
                }
              }
            ],
            "materialized": False
          }
        },
        "geometryField": {
          "name": f"{view_lyr_name}.Shape"
        }
      }
    }
  ]
}

view_flc.manager.add_to_definition(definition_to_add)

The following is a sample script run on ArcGIS Online.

from arcgis.gis import GIS
from arcgis.features import FeatureLayer, Table, FeatureLayerCollection

gis = GIS("https://www.arcgis.com", "username123", "password123")
fl_url = "https://services9.arcgis.com/xxxxxxxxxx/arcgis/rest/services/TestCopySymbology2/FeatureServer/0"
tbl_url = "https://services9.arcgis.com/xxxxxxxxxxx/arcgis/rest/services/TestPointSymbologyRestTransfer/FeatureServer/0"

fl = FeatureLayer(fl_url, gis)
tbl = Table(tbl_url, gis)

index_to_add = {"indexes":[
    {
        "name": "TestJoin_Index", 
        "fields": "TestJoin", 
        "isUnique": False, 
        "isAscending": True, 
        "description": "TestJoin_Index"
    }
]}

fl.manager.add_to_definition(index_to_add)
tbl.manager.add_to_definition(index_to_add)

view_service = gis.content.create_service(name="joined_view", is_view=True)
view_flc = FeatureLayerCollection.fromitem(view_service)

sourceFeatureLayerFields = [
    {
        "name": "TestJoin",
        "alias": "TestJoin",
        "source": "TestJoin"
    }
]

sourceTableFields = [
    {
        "name": "TestJoin",
        "alias": "TestJoin",
        "source": "TestJoin"
    }
]

field_to_join_on = "TestJoin"
view_lyr_name = "sampleJoinedView"
definition_to_add = {
  "layers": [
    {
      "name": view_lyr_name,
      "displayField": "",
      "description": "AttributeJoin",
      "adminLayerInfo": {
        "viewLayerDefinition": {
          "table": {
            "name": "sampleJoinedView",
            "sourceServiceName": fl.properties.name,
            "sourceLayerId": 0,
            "sourceLayerFields": sourceFeatureLayerFields,
            "relatedTables": [
              {
                "name": "testjoin",
                "sourceServiceName": tbl.properties.name,
                "sourceLayerId": 0,
                "sourceLayerFields": sourceTableFields,
                "type": "LEFT",
                "parentKeyFields": [
                  field_to_join_on
                ],
                "keyFields": [
                  field_to_join_on
                ],
                "topFilter": {
                  "groupByFields": field_to_join_on,
                  "orderByFields": "OBJECTID ASC",
                  "topCount": 1
                }
              }
            ],
            "materialized": False
          }
        },
        "geometryField": {
          "name": f"{view_lyr_name}.Shape"
        }
      }
    }
  ]
}

view_flc.manager.add_to_definition(definition_to_add)

Note:
The topFilter parameter is commonly used to ensure a deterministic single related record is selected when multiple matches exist, resulting in effectively one-to-one behavior. Although the topFilter section can be omitted in an attempt to accommodate one-to-many (1:M) scenarios, joined hosted feature layer views do not reliably surface multiple related records for a single source feature, and the behavior in such cases may vary depending on how the join is evaluated at query time.

Article ID: 000032931

Software: