Migrating Umbraco Content by XPath to MNTP or similar

Hi there :waving_hand:

I have upgraded a site from v13 to v17 - I’ve been an avid user of Contentment for many picker-related properties, and some of them were using the Umbraco Content by XPath datasource, which has now been removed and I need to use something else; the problem is of course that the proposed replacement (Umbraco Content supporting Dynamic Root) does not cut it for all my pickers, as that one can only pick direct children of a selected root node. The XPath one could select from any level.

I can use either the Examine Datasource in Contentment to get a similar editor experience, or I can use the trusty old Content Picker (aka: MNTP) which is also similar. But none of them will retain the already picked items when I change the property’s editor, which is crucial as there are many.

They’re storing almost the same data, just in a slightly different way, which is super frustrating, actually :smiling_face_with_tear: - one stores a JSON array of UUIDs, another stores a comma-separated string of UUIDs and the third one stores them as a comma-separated list of GUIDs… ¯\(ツ)/¯

What are my best options here? Have you had the same problem?

/Chriztian

I’d say that the examine datasource works pretty well. I’ve used it a lot. And you could always build your own data source if you needs are really specific :slight_smile:

You could use a propertyValueConvertor to retain the legacy data in the db and translate when asked for.. (different levels of cacheing for your requirements)
Or write a migrationplan to actually change the data in the propertyvaluetable

something along these lines..

valueConvertor

using Umbraco.Cms.Core.Models.PublishedContent;
using Umbraco.Cms.Core.PropertyEditors;
using Our.Umbraco.GMaps.Models;
using Our.Umbraco.GMaps.Models.Configuration;
using Our.Umbraco.GMaps.Configuration;
using Our.Umbraco.GMaps.Models.Legacy;
using Microsoft.Extensions.Options;
using System.Text.Json;

namespace Our.Umbraco.GMaps.PropertyValueConverter
{
    public class SingleMapPropertyValueConverter : PropertyValueConverterBase
    {
        private GoogleMaps googleMapsConfig;

        public SingleMapPropertyValueConverter(IOptionsMonitor<GoogleMaps> googleMapsConfig)
        {
            this.googleMapsConfig = googleMapsConfig.CurrentValue;
            googleMapsConfig.OnChange(config => this.googleMapsConfig = config);
        }
        public override bool IsConverter(IPublishedPropertyType propertyType)
            => propertyType.EditorAlias.Equals(PropertyEditors.GMapsSingleDataEditor.EditorAlias);

        public override Type GetPropertyValueType(IPublishedPropertyType propertyType) => typeof(Map);

        public override PropertyCacheLevel GetPropertyCacheLevel(IPublishedPropertyType propertyType) => PropertyCacheLevel.Element;

        public override object? ConvertIntermediateToObject(IPublishedElement owner, IPublishedPropertyType propertyType, PropertyCacheLevel referenceCacheLevel, object? inter, bool preview)
        {
            var interString = inter?.ToString();
            if (string.IsNullOrWhiteSpace(interString))
            {
                return default;
            }

            Map? model;
            // TODO: We really should create a package migration for legacy data and clean this up!
            // Handle pre v2.0.0 data (Removes the prefix 'google.maps.maptypeid.')
            interString = interString.Replace("google.maps.maptypeid.", string.Empty, StringComparison.InvariantCultureIgnoreCase);

            bool legacyData = interString.Contains("latlng", StringComparison.CurrentCultureIgnoreCase);
            if (legacyData)
            {
                var intermediate = JsonSerializer.Deserialize<LegacyMap>(interString);
                if (intermediate is null)
                {
                    return default;
                }
                model = new Map
                {
                    Address = intermediate.Address,
                    MapConfig = intermediate.MapConfig
                };

                // Map the LatLng property.
                model.Address.Coordinates = Location.Parse(intermediate.Address.LatLng);
                model.MapConfig.CenterCoordinates = Location.Parse(intermediate.MapConfig.MapCenter);
                if (model.MapConfig.Zoom == 0)
                {
                    model.MapConfig.Zoom = string.IsNullOrEmpty(intermediate.MapConfig.Zoom) ? 17 : Convert.ToInt32(intermediate.MapConfig.Zoom);
                }
                if (model.MapConfig.MapType == null)
                {
                    model.MapConfig.MapType = intermediate.MapConfig.MapType;
                }
            }
            else
            {
                model = JsonSerializer.Deserialize<Map>(interString);
            }

            if (model != null)
            {
                model.MapConfig.ApiKey = googleMapsConfig.ApiKey;

                // Get API key and mapStyle from configuration
                var config = propertyType.DataType.ConfigurationAs<Dictionary<string, object>>();

                if (config != null)
                {
                    if (config.TryGetValue("apikey", out var apiKey) && apiKey != null)
                    {
                        model.MapConfig.ApiKey = apiKey.ToString();
                    }

                    if (config.TryGetValue("mapstyle", out var mapStyle) && mapStyle is not null)
                    {
                        var style = JsonSerializer.Deserialize<MapStyle>(mapStyle.ToString()!);
                        model.MapConfig.Style = style?.Selectedstyle?.Json;
                    }
                }
            }

            return model;
        }
    }
}

Migration

using Microsoft.Extensions.Logging;
using NPoco;
using System.Text.Json;
using Umbraco.Cms.Core;
using Umbraco.Cms.Core.Composing;
using Umbraco.Cms.Core.DependencyInjection;
using Umbraco.Cms.Core.Events;
using Umbraco.Cms.Core.Migrations;
using Umbraco.Cms.Core.Notifications;
using Umbraco.Cms.Core.Scoping;
using Umbraco.Cms.Core.Services;
using Umbraco.Cms.Infrastructure.Migrations;
using Umbraco.Cms.Infrastructure.Migrations.Upgrade;
using Umbraco.Cms.Infrastructure.Persistence.Dtos;
using Umbraco.Extensions;

namespace YourProject.Migrations;

public class GMapMigrationComposer : IComposer
{
    public void Compose(IUmbracoBuilder builder) =>
        builder.AddNotificationAsyncHandler<UmbracoApplicationStartingNotification, RunLegacyGMapMigration>();
}

public class RunLegacyGMapMigration(
    ICoreScopeProvider coreScopeProvider,
    IMigrationPlanExecutor migrationPlanExecutor,
    IKeyValueService keyValueService,
    IRuntimeState runtimeState)
    : INotificationAsyncHandler<UmbracoApplicationStartingNotification>
{
    public async Task HandleAsync(UmbracoApplicationStartingNotification notification, CancellationToken cancellationToken)
    {
        // Only run if the database is in a state that can accept migrations
        if (runtimeState.Level < RuntimeLevel.Run) return;

        var migrationPlan = new MigrationPlan("Single-GMaps-Universal-Fix");

        migrationPlan.From(string.Empty).To<LegacySingleGmapMigration>("{legacy-latlng-mapcenter-migration}");

        var upgrader = new Upgrader(migrationPlan);
        await upgrader.ExecuteAsync(migrationPlanExecutor, coreScopeProvider, keyValueService);
    }
}

internal sealed class LegacySingleGmapMigration : AsyncMigrationBase
{
    private readonly ILogger<LegacySingleGmapMigration> _logger;

    public LegacySingleGmapMigration(IMigrationContext context, ILogger<LegacySingleGmapMigration> logger)
        : base(context) => _logger = logger;

    protected override async Task MigrateAsync()
    {
        // 1. Resolve PropertyType IDs (The DocType Usage)
        var propertyTypeSql = Sql()
            .Select<LocalPropertyTypeDto>(x => x.Id)
            .From<LocalPropertyTypeDto>()
            .InnerJoin<DataTypeDto>().On<LocalPropertyTypeDto, DataTypeDto>((pt, dt) => pt.DataTypeId == dt.NodeId)
            .Where<DataTypeDto>(x => x.EditorAlias == "Our.Umbraco.GMaps.Single");

        var validPropertyTypeIds = (await Database.FetchAsync<int>(propertyTypeSql)).ToList();
        if (!validPropertyTypeIds.Any()) return;

        // 2. Fetch the actual Property Data
        var sql = Sql()
            .Select<GMapPropertyDataDto>()
            .From<GMapPropertyDataDto>()
            .Where<GMapPropertyDataDto>(x => validPropertyTypeIds.Contains(x.PropertyTypeId))
            // We use a broader LIKE check to ensure we catch all JSON formats (minified or pretty)
            .Where("textValue LIKE '%latlng%' OR textValue LIKE '%mapcenter%'");

        var propertyEntries = (await Database.FetchAsync<GMapPropertyDataDto>(sql)).ToList();
        if (!propertyEntries.Any()) return;

        // 3. Transform and Update
        var updateBatch = propertyEntries
            .Select(dto => UpdateBatch.For(dto, Database.StartSnapshot(dto)))
            .ToList();

        var skipList = new List<UpdateBatch<GMapPropertyDataDto>>();

        foreach (var update in updateBatch)
        {
            var dto = update.Poco;
            try
            {
                var upgradedJson = TransformJson(dto.TextValue);

                // Only mark for update if transformation returned new data and it differs from original
                if (!string.IsNullOrEmpty(upgradedJson) && upgradedJson != dto.TextValue)
                {
                    dto.TextValue = upgradedJson;
                }
                else
                {
                    skipList.Add(update);
                }
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Error transforming GMap row ID: {id}", dto.Id);
                skipList.Add(update);
            }
        }

        updateBatch.RemoveAll(x => skipList.Contains(x));

        if (updateBatch.Any())
        {
            _logger.LogInformation("Universal Migration: Updating {count} GMaps records.", updateBatch.Count);

            // Smaller batch size to maintain stability on production transaction logs
            Database.UpdateBatch(updateBatch, new BatchOptions { BatchSize = 50 });
        }
    }

    private static string? TransformJson(string? rawJson)
    {
        if (string.IsNullOrWhiteSpace(rawJson)) return null;
        try
        {
            using var doc = JsonDocument.Parse(rawJson);
            var root = doc.RootElement;

            // Idempotency check: if 'centerCoordinates' already exists, skip
            if (root.TryGetProperty("mapconfig", out var check) && check.TryGetProperty("centerCoordinates", out _))
                return null;

            string? latLngStr = null, fullAddress = null, pc = null, city = null, state = null, country = null, sn = null, st = null;

            if (root.TryGetProperty("address", out var addr))
            {
                if (addr.TryGetProperty("latlng", out var l)) latLngStr = l.GetString();
                if (addr.TryGetProperty("full_address", out var f)) fullAddress = f.GetString();
                if (addr.TryGetProperty("postalcode", out var p)) pc = p.GetString();
                if (addr.TryGetProperty("city", out var c)) city = c.GetString();
                if (addr.TryGetProperty("state", out var s)) state = s.GetString();
                if (addr.TryGetProperty("country", out var co)) country = co.GetString();
                if (addr.TryGetProperty("streetNumber", out var n)) sn = n.GetString();
                if (addr.TryGetProperty("street", out var str)) st = str.GetString();
            }

            string? centerStr = null; int zoom = 17; string mapType = "Roadmap";
            if (root.TryGetProperty("mapconfig", out var conf))
            {
                if (conf.TryGetProperty("mapcenter", out var m)) centerStr = m.GetString();
                if (conf.TryGetProperty("zoom", out var z))
                {
                    if (z.ValueKind == JsonValueKind.Number) zoom = z.GetInt32();
                    else if (int.TryParse(z.GetString(), out var zi)) zoom = zi;
                }
                if (conf.TryGetProperty("maptype", out var t)) mapType = t.GetString() ?? "Roadmap";
            }

            var coords = ParsePoint(latLngStr);
            var centerCoords = ParsePoint(centerStr) ?? coords;

            return JsonSerializer.Serialize(new
            {
                address = new { coordinates = coords, full_address = fullAddress, postalcode = pc, city, state, country, streetNumber = sn, street = st },
                mapconfig = new { zoom, maptype = mapType, centerCoordinates = centerCoords }
            });
        }
        catch { return null; }
    }

    private static object? ParsePoint(string? point)
    {
        if (string.IsNullOrWhiteSpace(point)) return null;
        var parts = point.Split(',');
        return parts.Length >= 2 && double.TryParse(parts[0], out var lat) && double.TryParse(parts[1], out var lng) ? new { lat, lng } : null;
    }

    /// <summary>
    /// Minimal DTO to bridge the DataType to PropertyData gap, bypassing internal Umbraco DTOs.
    /// </summary>
    [TableName("cmsPropertyType")]
    [PrimaryKey("id")]
    [ExplicitColumns]
    public class LocalPropertyTypeDto
    {
        [Column("id")] public int Id { get; set; }
        [Column("dataTypeId")] public int DataTypeId { get; set; }
    }

    /// <summary>
    /// Represents the target data to be migrated.
    /// </summary>
    [TableName("umbracoPropertyData")]
    [PrimaryKey("id")]
    [ExplicitColumns]
    public class GMapPropertyDataDto
    {
        [Column("id")] public int Id { get; set; }
        [Column("textValue")] public string? TextValue { get; set; }
        [Column("propertyTypeId")] public int PropertyTypeId { get; set; }
    }
}

I’m not sure if the PropertyValueConverter is the best place to do that, but it depends on what you need. A property value converter is the gateway between data that is in the database and what goes into the cache. So it does not touch the original database values.

So if you have a legacy format in the database that needs to be updated (eventually) to a newer format, I don’t think the PropertyValueConver is the way to go. Because the data in the database will remain in the old format.

In that case a Property Editor Schema (with a DataValueEditor) is the gateway for data coming in and out the database. You can can actually change the data when it comes out of the database, so you can still support the old value, but also change the data to the new format before it goes into the database.

Hmm.. might be wrong but in my mind a propertyvalueconvertor can convert for an existing property editor (3rd party) which you can override and takes precedence over an existing converter (including 3rd-party ones)… (so the data is converted and cached from legacy db data into new format at point of consumption (IPublishedContent), by value/modelbuilder.. and no need to update the DB

PropertyEditor schema/with datavalueeditor is for if you are creating new property editor or you are the developer of the package for that property, but you can’t simply override for a 3rd party packge like you can with the propertyvalueconvertor…

But a migration is the best to change the uderlying db property data.. though be careful as once data migrated you lost the original!

:thinking:

I think I would have to agree with @Luuk - I would write a migration to convert the data to the format required when migrating. You will be drawing legacy technical debt into your site if you continue to support an older obsolete format which may cause confusion and issues later on.

?? think I said that too… :slight_smile:

I was just rumnating on propertyvalueconvertor vs propertyeditorSchema/DatavalueEditor.. in case I was wrong..

(migration is a little scary though, and depending on the use case, eg udi csv, vs array.. I think there are times when addressing the tech debt over a simple translate is un-warranted)
and for a large node setup.. point of consumption could get you there quicker..

Hi @mistyn8

Yes, you did - your points were valid too. :+1:

Yes a migration is the best way to do it, but also the most risky. For very small changes, the DataValueEditor would be able to take care of that and the data would naturally get updated over time.

My point was that the property value converter is not the best approach because it doesn’t solve the outdated data.

So can you override the DataValueEditor for 3rdparty packages?

Oh that’s a good one, I don’t know…

@greystate Would a custom (Contentment) data-source work better for you? Then it could be tailored exactly as you need. (I appreciate there is no real replacement for XPath).

…or @markusjoha has suggested recently whether the Umbraco Content data-source could support descendants. I haven’t looked into this yet, but could be worth continuing that discussion? or at least see if @markusjoha is willing to share his custom solution?

With regards to the Examine data-source. I’m hesitant to recommend it as a long term solution, as with the new Search feature in v19, there may be breaking-changes I need to make for upcoming Contentment v8, (which I’m still exploring / deciding on).

Thanks Lee :raising_hands:

This is definitely the way I’d like to go, as implementing a Custom Datasource is something I’ve had to do before and it preserves the niceness of using Contentment.

Allowing descendants in the Umbraco Content datasource would probably solve all of my needs.

I’ll check with Markus if that’s something he’d be willing to share.

And tanks to you all for chipping in – always fascinating to get a peek into other people’s interpretation of your problem, and how they’d solve it. All great answers!

/Chriztian