MySQL 5.7 Json Field Type Is Available Now

[yukozh]

Getting Started

① Adding pomelo myget feed into your NuGet.config which located in your solution root.

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <packageSources>
    <add key="nuget.org" value="https://www.nuget.org/api/v2/" />
    <add key="Pomelo" value="https://www.myget.org/F/pomelo/api/v2/" />
  </packageSources>
  <disabledPackageSources />
</configuration>

② Add Pomelo.Data.MySql and Pomelo.EntityFrameworkCore.MySql into your project.json. The versions of them are 1.0.0.

③ If you have already installed the pomelo packages: Pomelo.Data.MySql and Pomelo.EntityFrameworkCore.MySql, please remove them and restore again(dotnet restore --no-cache). The packages are located in C:\Users\YOURNAME\.nuget\packages.

④ To define json field in model with System.JsonObject<T> will store this field as a json column.

Sample

using System;
using System.Linq;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using Microsoft.EntityFrameworkCore;

namespace MySqlTest
{
    public class Blog
    {
        public Guid Id { get; set; }

        [MaxLength(32)]
        public string Title { get; set; }

        public string Content { get; set; }

        public JsonObject<string[]> Tags { get; set; } // Json storage
    }

    public class MyContext : DbContext
    {
        public DbSet<Blog> Blogs { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
            => optionsBuilder
                .UseMySql(@"Server=localhost;database=ef;uid=root;pwd=19931101;");
    }

    public class Program
    {
        public static void Main()
        {
            using (var context = new MyContext())
            {
                // Create database
                context.Database.EnsureCreated();

                // Init sample data
                var blog1 = new Blog {
                    Title = "Title #1",
                    Tags = new string[] { "ASP.NET Core", "MySQL", "Pomelo" }
                };
                context.Add(blog1);
                var blog2 = new Blog
                {
                    Title = "Title #2",
                    Tags = new string[] { "ASP.NET Core", "MySQL" }
                };
                context.Add(blog2);
                context.SaveChanges();

                // Detect changes test
                blog1.Title = "Changed Title #1";
                context.SaveChanges();

                // Output data
                var ret = context.Blogs
                    .Where(x => x.Tags.Object.Contains("Pomelo"))
                    .ToList();
                foreach (var x in ret)
                {
                    Console.WriteLine($"{ x.Id } { x.Title }");
                    Console.Write("[Tags]: ");
                    foreach(var y in x.Tags.Object)
                        Console.Write(y + " ");
                    Console.WriteLine();
                }
            }

            Console.Read();
        }
    }
}

[chinkan]

Hi @yukozh ,

Can I use JsonObject with dynamic object? I had tried to create a table contain JsonObject, I can insert data to database but I cannot query from database using linq. It said "An expression tree may not contain a dynamic operation". How can i do?

[mbrewerton]

@chinkan out of curiosity, why would you want to store a dynamic object as JSON and not use a strongly typed class? The simplest way around your issue would be to create a class and save that type.

[gouhan]

@mbrewerton ,hi
Why I can not found the JsonObject in the System namespace?
My project type is .netcore2.2

[xwy2258]

@mbrewerton ,hi
Why I can not found the JsonObject in the System namespace?
My project type is .netcore2.2
===
installed the pomelo packages: Pomelo.Data.MySql and Pomelo.EntityFrameworkCore.MySql

[chazt3n]

So can my Entity not be a strong type with a column type of Json?

[lauxjpn]

@chazt3n Sure it can:

using System;
using System.Diagnostics;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using Pomelo.EntityFrameworkCore.MySql.Storage;

namespace IssueConsoleTemplate
{
    public class IceCream
    {
        public int IceCreamId { get; set; }
        public string Name { get; set; }
        public JsonObject<Energy> Energy { get; set; }
        public JsonObject<string[]> Comments { get; set; }
    }

    public class Energy
    {
        public double Kilojoules { get; set; }
        public double Kilocalories { get; set; }
    }

    public class Context : DbContext
    {
        public virtual DbSet<IceCream> IceCreams { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder
                .UseMySql("server=127.0.0.1;port=3306;user=root;password=;database=Issue14",
                    b => b.ServerVersion(new ServerVersion("8.0.18-mysql")))
                .UseLoggerFactory(LoggerFactory.Create(b => b
                    .AddConsole()
                    .AddFilter(level => level >= LogLevel.Information)))
                .EnableSensitiveDataLogging()
                .EnableDetailedErrors();
        }
    }

    internal class Program
    {
        private static void Main()
        {
            using (var context = new Context())
            {
                context.Database.EnsureDeleted();
                context.Database.EnsureCreated();

                context.IceCreams.AddRange(
                    new IceCream
                    {
                        Name = "Vanilla",
                        Energy = new Energy
                        {
                            Kilojoules = 866.0,
                            Kilocalories = 207.0
                        },
                        Comments = new[]
                        {
                            "First!",
                            "Delicios!"
                        }
                    },
                    new IceCream
                    {
                        Name = "Chocolate",
                        Energy = new Energy
                        {
                            Kilojoules = 904.0,
                            Kilocalories = 216.0
                        },
                        Comments = new[]
                        {
                            "My husband linkes this one a lot."
                        }
                    });

                context.SaveChanges();
            }

            using (var context = new Context())
            {
                var result = context.IceCreams
                    .OrderBy(e => e.IceCreamId)
                    .ToList();

                Debug.Assert(result.Count == 2);

                Debug.Assert(result[0].Name == "Vanilla");
                Debug.Assert(result[0].Energy.Object.Kilojoules == 866.0);
                Debug.Assert(result[0].Comments.Object.Length == 2);
                Debug.Assert(result[0].Comments.Object[0] == "First!");
            }
        }
    }
}

In the example, IceCream is a strongly typed entity. It contains two properties, Energy and Coments, that will both be mapped to columns of the JSON MySQL store type.

[chazt3n]

@lauxjpn ok I see my misunderstanding now, thank you

 public **JsonObject<Energy>** Energy { get; set; }

if you know off the top of you head, is this preferable over a

public Energy Energy {get;set;}

with a json conversion in dbcontext? is there any reason to do the conversion with a regular object?

[mguinness]

AFAIK, POCO mapping as done in the Npgsql provider isn't supported. Ideally JsonObject should be retired and use System.Text.Json instead.

[chazt3n]

Today we use (I'll say POCOs though they have behaviour) and in the dbcontext we put .WithConversion( blah.ToJson() / blah.FromJson<T>())

This works fine, but probably takes more of a performance hit.

I'm not understanding where System.Text.Json comes into play here -> that would be a different wrapper type than JsonObject<T>?

[mguinness]

that would be a different wrapper type than JsonObject<T>?

Yes, changes would need to be made in the provider code when JSON handling is revisted.

[lauxjpn]

This works fine, but probably takes more of a performance hit.

That should actually be fine. Pomelo would need to do something similar when serializing/deserializing objects (and already does so today, when mapping JsonObject<T>).

I'm not understanding where System.Text.Json comes into play here -> that would be a different wrapper type than JsonObject?

No, the idea is that we could support both, Microsoft's Json implementation and Newtonsoft's Json implementation, both without any wrapper (though we will likely keep the JsonObject<T> wrapper around as well for backwards compatibility reasons).

[chazt3n]

Ok awesome thank you both for helping me understand

[chazt3n]

This issue is linked from https://kodummu.wordpress.com/2019/04/29/json-type-with-mysql-ef-core/

my question is how can I query on a property of a json column and have it processed on the MySQL Side vs Client Side

[mguinness]

You could try Raw SQL Queries, but you'll be better served by waiting for support like the Npgsql provider has for Querying JSON columns. FWIW, even SQL Server provider doesn't have JSON Mapping yet.

[lauxjpn]

@chazt3n I am implementing spatial types right now. After that, we will significantly extend our JSON support (as the last feature for the 3.x branch).

how can I query on a property of a json column and have it processed on the MySQL Side vs Client Side

Take a look at the sample code from #1039 (comment). It demonstrates multiple ways to accomplish this.

[chazt3n]

@lauxjpn will pomelo support lists/arrays of complex types?
e.g. public List<FoodAdditive> FoodAdditives { get; set; }

The examples in the link do not handle that case (unless I did something wrong)

[lauxjpn]

@lauxjpn will pomelo support lists/arrays of complex types?
e.g. public List<FoodAdditive> FoodAdditives { get; set; }

Yes, we will support lists/arrays of complex types out-of-the-box.

The examples in the link do not handle that case (unless I did something wrong)

Should work fine. I updated the sample code with an public List<IceCreamSupplierInformation> AllSupplierInformations { get; set; } property:

Updated Sample Code

using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Diagnostics.CodeAnalysis;
using System.Linq;
using System.Reflection;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Query;
using Microsoft.EntityFrameworkCore.Query.SqlExpressions;
using Microsoft.EntityFrameworkCore.Storage;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Logging;
using Newtonsoft.Json;
using Pomelo.EntityFrameworkCore.MySql.Infrastructure.Internal;
using Pomelo.EntityFrameworkCore.MySql.Query.Internal;
using Pomelo.EntityFrameworkCore.MySql.Storage.Internal;

namespace IssueConsoleTemplate
{
    // 
    // JSON EF.Functions Support:
    // 

    public static class MySqlJsonDbFunctionsExtensions
    {
        public static bool JsonContains(
            this DbFunctions _,
            object expression,
            object value,
            string path)
            => throw new InvalidOperationException();

        public static string JsonQuote(
            this DbFunctions _,
            string value)
            => @$"""{value.Replace(@"""", @"\""")}""";
    }

    public class JsonExtendedMethodCallTranslatorProvider : MySqlMethodCallTranslatorProvider
    {
        public JsonExtendedMethodCallTranslatorProvider(
            [NotNull] RelationalMethodCallTranslatorProviderDependencies dependencies,
            [NotNull] IMySqlOptions options)
            : base(dependencies, options)
        {
            var sqlExpressionFactory = dependencies.SqlExpressionFactory;

            AddTranslators(
                new IMethodCallTranslator[]
                {
                    new MySqlJsonDbFunctionsExtensionsMethodTranslator(sqlExpressionFactory),
                });
        }
    }

    public class MySqlJsonDbFunctionsExtensionsMethodTranslator : IMethodCallTranslator
    {
        private readonly MethodInfo _jsonContains = typeof(MySqlJsonDbFunctionsExtensions).GetRuntimeMethod(
            nameof(MySqlJsonDbFunctionsExtensions.JsonContains),
            new[] {typeof(DbFunctions), typeof(object), typeof(object), typeof(string)});

        private readonly ISqlExpressionFactory _sqlExpressionFactory;

        public MySqlJsonDbFunctionsExtensionsMethodTranslator(
            ISqlExpressionFactory sqlExpressionFactory)
            => _sqlExpressionFactory = sqlExpressionFactory;

        public virtual SqlExpression Translate(SqlExpression instance, MethodInfo method, IReadOnlyList<SqlExpression> arguments)
        {
            if (method == _jsonContains)
            {
                return _sqlExpressionFactory.Function(
                    "JSON_CONTAINS",
                    new[] {arguments[1], arguments[2], arguments[3]},
                    typeof(bool)
                );
            }

            return null;
        }
    }

    // 
    // JSON Column Type Support:
    // 

    public class JsonStringTypeMappingSourcePlugin : IRelationalTypeMappingSourcePlugin
    {
        private readonly MySqlStringTypeMapping _json;

        public JsonStringTypeMappingSourcePlugin(IMySqlOptions options)
            => _json = new MySqlStringTypeMapping("json", DbType.String, options);

        /// <summary>
        /// Return a String type mapping, if `JSON` has been specified explicitly as the column type.
        /// </summary>
        public RelationalTypeMapping FindMapping(in RelationalTypeMappingInfo mappingInfo)
            => string.Equals(mappingInfo.StoreTypeName, "json", StringComparison.OrdinalIgnoreCase)
                ? _json
                : null;
    }

    // 
    // Database Entities:
    // 

    public class IceCream
    {
        public int Id { get; set; }
        public string Name { get; set; }

        //
        // JSON properties:
        //

        public JsonObject<IceCreamProperties> Properties { get; set; }
        public IceCreamSupplierInformation PrimarySupplierInformation { get; set; }
        public List<string> FoodAdditives { get; set; }
        public string Tags { get; set; }
        public List<IceCreamSupplierInformation> AllSupplierInformations { get; set; }
    }

    // 
    // JSON Objects:
    // 

    public class IceCreamProperties
    {
        public int PopularityRank { get; set; }
        public bool InStock { get; set; }
    }

    public class IceCreamSupplierInformation
    {
        public string Name { get; set; }
        public double StandardHygiene { get; set; }
    }

    // 
    // DbContext:
    // 

    public class Context : DbContext
    {
        public DbSet<IceCream> IceCreams { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            // Register the custom type mapping plugin and our custom method call translator
            // provider.
            // Since this is a console program, we need to create our own ServiceCollection
            // for this.
            // In an ASP.NET Core application, the AddSingleton call can just be added to
            // the general service configuration method.
            var serviceProvider = new ServiceCollection()
                .AddEntityFrameworkMySql()
                .AddSingleton<IRelationalTypeMappingSourcePlugin, JsonStringTypeMappingSourcePlugin>()
                .AddSingleton<IMethodCallTranslatorProvider, JsonExtendedMethodCallTranslatorProvider>()
                .AddScoped(
                    s => LoggerFactory.Create(
                        b => b
                            .AddConsole()
                            .AddFilter(level => level >= LogLevel.Information)))
                .BuildServiceProvider();

            optionsBuilder
                .UseInternalServiceProvider(serviceProvider) // <-- use our ServiceProvider
                .UseMySql(
                    "server=127.0.0.1;port=3306;user=root;password=;database=Issue14",
                    b => b.ServerVersion("8.0.20-mysql"))
                .EnableSensitiveDataLogging()
                .EnableDetailedErrors();
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<IceCream>(
                entity =>
                {
                    // 
                    // Force JSON as the column type and define custom value conversions:
                    // 

                    entity.Property(e => e.Properties)
                        .HasConversion(
                            v => v.Json,
                            v => new JsonObject<IceCreamProperties>(v))
                        .HasColumnType("json");
                    
                    entity.Property(e => e.PrimarySupplierInformation)
                        .HasConversion(
                            v => JsonConvert.SerializeObject(v),
                            v => JsonConvert.DeserializeObject<IceCreamSupplierInformation>(v))
                        .HasColumnType("json");
                    
                    entity.Property(e => e.FoodAdditives)
                        .HasConversion(
                            v => JsonConvert.SerializeObject(v),
                            v => JsonConvert.DeserializeObject<List<string>>(v))
                        .HasColumnType("json");
                    
                    entity.Property(e => e.Tags)
                        .HasColumnType("json");
                    
                    entity.Property(e => e.AllSupplierInformations)
                        .HasConversion(
                            v => JsonConvert.SerializeObject(v),
                            v => JsonConvert.DeserializeObject<List<IceCreamSupplierInformation>>(v))
                        .HasColumnType("json");

                    //
                    // Sample Data:
                    //

                    entity.HasData(
                        new IceCream
                        {
                            Id = 1,
                            Name = "Vanilla",
                            Properties = new JsonObject<IceCreamProperties>(
                                new IceCreamProperties
                                {
                                    PopularityRank = 1,
                                    InStock = true,
                                }),
                            PrimarySupplierInformation = new IceCreamSupplierInformation
                            {
                                Name = "Fasssst Dilivery",
                                StandardHygiene = 0.45,
                            },
                            FoodAdditives = new List<string> {"E102"},
                            Tags = @"[""fluffy"", ""white"", ""yellow""]",
                            AllSupplierInformations = new List<IceCreamSupplierInformation>
                            {
                                new IceCreamSupplierInformation
                                {
                                    Name = "Fasssst Dilivery",
                                    StandardHygiene = 0.45,
                                },
                                new IceCreamSupplierInformation
                                {
                                    Name = "Fast Fooood",
                                    StandardHygiene = 0.61,
                                },
                            },
                        },
                        new IceCream
                        {
                            Id = 2,
                            Name = "Chocolate",
                            Properties = new JsonObject<IceCreamProperties>(
                                new IceCreamProperties
                                {
                                    PopularityRank = 2,
                                    InStock = true,
                                }),
                            PrimarySupplierInformation = new IceCreamSupplierInformation
                            {
                                Name = "Sweet Dilivery",
                                StandardHygiene = 0.65,
                            },
                            FoodAdditives = new List<string> {"E124","E155"},
                            Tags = @"[""creamy"", ""brown""]",
                            AllSupplierInformations = new List<IceCreamSupplierInformation>
                            {
                                new IceCreamSupplierInformation
                                {
                                    Name = "Sweet Dilivery",
                                    StandardHygiene = 0.65,
                                },
                            }
                        },
                        new IceCream
                        {
                            Id = 3,
                            Name = "Strawberry",
                            Properties = new JsonObject<IceCreamProperties>(
                                new IceCreamProperties
                                {
                                    PopularityRank = 3,
                                    InStock = false,
                                }),
                            PrimarySupplierInformation = new IceCreamSupplierInformation
                            {
                                Name = "Fresh Dilivery",
                                StandardHygiene = 0.85,
                            },
                            FoodAdditives = new List<string> {"E124"},
                            Tags = @"[""sweet"", ""red""]",
                            AllSupplierInformations = new List<IceCreamSupplierInformation>
                            {
                                new IceCreamSupplierInformation
                                {
                                    Name = "Fresh Dilivery",
                                    StandardHygiene = 0.85,
                                },
                            }
                        },
                        new IceCream
                        {
                            Id = 4,
                            Name = "Matcha",
                            Properties = new JsonObject<IceCreamProperties>(
                                new IceCreamProperties
                                {
                                    PopularityRank = 42,
                                    InStock = false,
                                }),
                            PrimarySupplierInformation = new IceCreamSupplierInformation
                            {
                                Name = "Fine Dine",
                                StandardHygiene = 0.98,
                            },
                            FoodAdditives = new List<string> {"E102", "E142"},
                            Tags = @"[""bitter"", ""green""]",
                            AllSupplierInformations = new List<IceCreamSupplierInformation>
                            {
                                new IceCreamSupplierInformation
                                {
                                    Name = "Fine Dine",
                                    StandardHygiene = 0.98,
                                },
                            }
                        }
                    );
                });
        }
    }

    internal class Program
    {
        private static void Main()
        {
            using var context = new Context();

            context.Database.EnsureDeleted();
            context.Database.EnsureCreated();

            // Query all ice creams.
            // Needs:
            //   - JsonStringTypeMappingSourcePlugin
            var iceCreams = context.IceCreams
                .OrderBy(i => i.Id)
                .ToList();

            Debug.Assert(iceCreams.Count == 4);
            Debug.Assert(iceCreams[3].PrimarySupplierInformation.Name == "Fine Dine");
            Debug.Assert(iceCreams[0].AllSupplierInformations.Count == 2);
            Debug.Assert(iceCreams[0].AllSupplierInformations[1].Name == "Fast Fooood");

            // Query ice creams by handling the JSON column as plain text and using a simple LIKE clause. 
            // Needs:
            //   - JsonStringTypeMappingSourcePlugin
            var yellowTaggedIceCreams = context.IceCreams
                .Where(i => i.Tags.Contains("yellow"))
                .ToList();

            Debug.Assert(yellowTaggedIceCreams.Count == 1);

            // Query ice creams by using a sql query and using the MySQL JSON_CONTAINS() function.
            // Needs:
            //   - JsonStringTypeMappingSourcePlugin
            var yellowFoodColoringENumber = "E102";
            var iceCreamsWithYellowFoodColoring = context.IceCreams
                .FromSqlInterpolated($"select * from `IceCreams` where json_contains(`FoodAdditives`, json_quote({yellowFoodColoringENumber}), '$') <> 0")
                .ToList();

            Debug.Assert(iceCreamsWithYellowFoodColoring.Count == 2);

            // Query ice creams by using EF functions.
            // Needs:
            //   - JsonStringTypeMappingSourcePlugin,
            //   - MySqlJsonDbFunctionsExtensions
            //   - JsonExtendedMethodCallTranslatorProvider
            //   - MySqlJsonDbFunctionsExtensionsMethodTranslator
            var redFoodColoringENumber = "E124";
            var iceCreamsWithRedFoodColoring = context.IceCreams
                .Where(i => EF.Functions.JsonContains(i.FoodAdditives, EF.Functions.JsonQuote(redFoodColoringENumber), "$"))
                .ToList();

            Debug.Assert(iceCreamsWithRedFoodColoring.Count == 2);
        }
    }
}

[chazt3n]

are you able to do something like

      var iceCreams = context.IceCreams
                .Where(i => i.AllSupplierInformations.Any(supplierInfo => supplierInfo.Name == "Fine Dine"))
                .OrderBy(i => i.Id)
                .ToList();

the code I have looks identical but this use case throws an exception regarding unable to translate

just getting the data works great, it's filtering where the pain is

[lauxjpn]

are you able to do something like

var iceCreams = context.IceCreams
    .Where(i => i.AllSupplierInformations.Any(supplierInfo => supplierInfo.Name == "Fine Dine"))
    .OrderBy(i => i.Id)
    .ToList();

No, that is not going to work just like that. The sample code is (by definition) really just a starting point to implement your own needed custom behavior.

You need to implement the JSON_EXTRACT() MySQL function in a similar fashion as I have demonstrated with the JSON_CONTAINS() function.

See 11.5 The JSON Data Type: Searching and Modifying JSON Values for usage examples in the official docs.

If you can't get it to work, get back to me again. I will add the JSON_EXTRACT() function to the sample code then as well.

[chazt3n]

You're the man thank you - that makes sense I was afraid it was supposed to work and I horked something else. I intend to give it a shot ASAP

[chazt3n]

@lauxjpn hey hey! does #1102 add support for the query I pasted above?

I'm so sorry I fell off the wagon here :(

var iceCreams = context.IceCreams
    .Where(i => i.AllSupplierInformations.Any(supplierInfo => supplierInfo.Name == "Fine Dine"))
    .OrderBy(i => i.Id)
    .ToList();

[mguinness]

@chazt3n Try it out and let us know.

[chazt3n]

@mguinness ok I'm excited to - one question I have is how should I set up the models nowadays?

is there a new type I need to be using to get the full functionality?

//property
        public ICollection<Phone> PhoneNumbers { get; set; }


//config
                builder.Property(c => c.PhoneNumbers)
                    .HasConversion(v => JsonConvert.SerializeObject(v,
                                            new JsonSerializerSettings { NullValueHandling = NullValueHandling.Ignore }),
                                   v => JsonConvert.DeserializeObject<ICollection<Phone>>(v,
                                            new JsonSerializerSettings { NullValueHandling = NullValueHandling.Ignore }))
                    .HasColumnType("json");

[mguinness]

Take a look at the test cases in JsonPocoQueryTestBase for examples.

[chazt3n]

Ok I've followed the advice to no avail :( I have an entity like:

public class Contact
{
     [Column(TypeName = "json")]
     public Address Address {get; set;}
}

where address looks like you'd expect, it does not have a PK. (I don't want it to have an Id, but can live with that concession if necessary)

First, it yelled at me for not having a PK and I tried to add

public class EntityConfiguration : IEntityTypeConfiguration<Address>
        {
            public void Configure(EntityTypeBuilder<Address> builder)
            {
                builder.HasNoKey();
            }
        }

I then got this error:

System.InvalidOperationException : Unable to determine the relationship represented by navigation property 'Contact.Address' of type 'Address'. Either manually configure the relationship, or ignore this property using the '[NotMapped]' attribute or by using 'EntityTypeBuilder.Ignore' in 'OnModelCreating'.

packages:

    <PackageReference Include="Microsoft.EntityFrameworkCore" Version="3.1.10" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="3.1.10">
    <PackageReference Include="Pomelo.EntityFrameworkCore.MySql" Version="3.2.4" />
    <PackageReference Include="Pomelo.EntityFrameworkCore.MySql.Json.Microsoft" Version="3.2.4" />
    <PackageReference Include="MySqlConnector" Version="0.69.10" />

 services.AddDbContextPool<ContactDataContext>((srv, builder) =>
            {
                if (HostingEnvironment.IsDevelopment())
                {
                    builder.EnableDetailedErrors();
                    builder.EnableSensitiveDataLogging();
                }
                var conn = Configuration["ConnectionString"];
                builder.UseMySql(conn, options => options.ServerVersion(new Version(5,7), ServerType.MySql));
            });

[lauxjpn]

@chazt3n You are missing the UseMicrosoftJson() options call. The method has again its own options parameter, where you can set one of the common change tracking options, if you want to track changes in your JSON entities, instead of just tracking the reference to the entity changing (which is the default, because its the fasted).

Once you have added the UseMicrosoftJson() call, remove the configuration for the Address class. It is not an EF Core entity, but a class that is being serialized to JSON.

[...] it does not have a PK. (I don't want it to have an Id, but can live with that concession if necessary)

The Address class does not need an ID, because it is not an EF Core entity.

---

I recently posted a full sample program on StackOverflow (see https://stackoverflow.com/a/64742647/2618319), demonstrating the JSON support.

[chazt3n]

You're right! I had just added that from looking at the source, I'm still getting the Contact.Address issue - getting detail now

UPDATE: My test project had a .UseMySql() call... updated that, and I'm moving forward. Thank you very much for your reply <3

[lauxjpn]

@chazt3n Here is the sample code I used to test the latest JSON code update of the 3.2.4 release for some real-world scenarios a couple of weeks ago.
It demonstrates different ways to map, configure and query JSON data.

JSON mapping and query scenarios

using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Text.Json;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using Pomelo.EntityFrameworkCore.MySql.Infrastructure;

namespace IssueConsoleTemplate
{
    // 
    // Database Entities:
    // 

    public class IceCream
    {
        public int Id { get; set; }
        public string Name { get; set; }

        //
        // JSON properties:
        //

        public IceCreamProperties Properties { get; set; }
        public JsonDocument PrimarySupplierInformation { get; set; }
        public List<string> FoodAdditives { get; set; }
        public string Tags { get; set; }
        public List<IceCreamSupplierInformation> AllSupplierInformations { get; set; }
    }

    // 
    // JSON Objects:
    // 

    public class IceCreamProperties
    {
        public int PopularityRank { get; set; }
        public bool InStock { get; set; }
    }

    public class IceCreamSupplierInformation
    {
        public string Name { get; set; }
        public double StandardHygiene { get; set; }
    }

    // 
    // DbContext:
    // 

    public class Context : DbContext
    {
        public DbSet<IceCream> IceCreams { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder
                .UseMySql(
                    "server=127.0.0.1;port=3306;user=root;password=;database=Issue14_01",
                    b => b.ServerVersion("8.0.21-mysql")
                          .CharSetBehavior(CharSetBehavior.NeverAppend)
                          .UseMicrosoftJson(MySqlCommonJsonChangeTrackingOptions.FullHierarchyOptimizedFast))
                .UseLoggerFactory(
                    LoggerFactory.Create(
                        b => b
                            .AddConsole()
                            .AddFilter(level => level >= LogLevel.Information)))
                .EnableSensitiveDataLogging()
                .EnableDetailedErrors();
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<IceCream>(
                entity =>
                {
                    // 
                    // Force JSON as the column type:
                    // 

                    entity.Property(e => e.Properties)
                        .HasColumnType("json");

                    // This is not needed, because DOM classes like JsonDocument are being serialized as JSON
                    // by default.
                    // entity.Property(e => e.PrimarySupplierInformation)
                    //     .HasColumnType("json");
                    
                    entity.Property(e => e.FoodAdditives)
                        .HasColumnType("json");
                    
                    entity.Property(e => e.Tags)
                        .HasColumnType("json");
                    
                    // Demonstrates how to override the default JSON change tracking options previously defined via the
                    // UseMicrosoftJson(options: ) parameter.
                    entity.Property(e => e.AllSupplierInformations)
                        .HasColumnType("json")
                        .UseJsonChangeTrackingOptions(MySqlCommonJsonChangeTrackingOptions.RootPropertyOnly);

                    //
                    // Sample Data:
                    //

                    entity.HasData(
                        new IceCream
                        {
                            Id = 1,
                            Name = "Vanilla",
                            Properties = new IceCreamProperties
                            {
                                PopularityRank = 1,
                                InStock = true,
                            },
                            PrimarySupplierInformation = JsonDocument.Parse(
                                JsonSerializer.Serialize(
                                    new IceCreamSupplierInformation
                                    {
                                        Name = "Fasssst Dilivery",
                                        StandardHygiene = 0.45,
                                    })),
                            FoodAdditives = new List<string> {"E102"},
                            Tags = @"[""fluffy"", ""white"", ""yellow""]",
                            AllSupplierInformations = new List<IceCreamSupplierInformation>
                            {
                                new IceCreamSupplierInformation
                                {
                                    Name = "Fasssst Dilivery",
                                    StandardHygiene = 0.45,
                                },
                                new IceCreamSupplierInformation
                                {
                                    Name = "Fast Fooood",
                                    StandardHygiene = 0.61,
                                },
                            },
                        },
                        new IceCream
                        {
                            Id = 2,
                            Name = "Chocolate",
                            Properties = new IceCreamProperties
                            {
                                PopularityRank = 2,
                                InStock = true,
                            },
                            PrimarySupplierInformation = JsonDocument.Parse(
                                JsonSerializer.Serialize(
                                    new IceCreamSupplierInformation
                                    {
                                        Name = "Sweet Dilivery",
                                        StandardHygiene = 0.65,
                                    })),
                            FoodAdditives = new List<string> {"E124","E155"},
                            Tags = @"[""creamy"", ""brown""]",
                            AllSupplierInformations = new List<IceCreamSupplierInformation>
                            {
                                new IceCreamSupplierInformation
                                {
                                    Name = "Sweet Dilivery",
                                    StandardHygiene = 0.65,
                                },
                            }
                        },
                        new IceCream
                        {
                            Id = 3,
                            Name = "Strawberry",
                            Properties = new IceCreamProperties
                            {
                                PopularityRank = 3,
                                InStock = false,
                            },
                            PrimarySupplierInformation = JsonDocument.Parse(
                                JsonSerializer.Serialize(
                                    new IceCreamSupplierInformation
                                    {
                                        Name = "Fresh Dilivery",
                                        StandardHygiene = 0.85,
                                    })),
                            FoodAdditives = new List<string> {"E124"},
                            Tags = @"[""sweet"", ""red""]",
                            AllSupplierInformations = new List<IceCreamSupplierInformation>
                            {
                                new IceCreamSupplierInformation
                                {
                                    Name = "Fresh Dilivery",
                                    StandardHygiene = 0.85,
                                },
                            }
                        },
                        new IceCream
                        {
                            Id = 4,
                            Name = "Matcha",
                            Properties = new IceCreamProperties
                            {
                                PopularityRank = 42,
                                InStock = false,
                            },
                            PrimarySupplierInformation = JsonDocument.Parse(
                                @"{""Name"": ""Fine Dine"", ""StandardHygiene"": 0.98}"),
                            FoodAdditives = new List<string> {"E102", "E142"},
                            Tags = @"[""bitter"", ""green""]",
                            AllSupplierInformations = new List<IceCreamSupplierInformation>
                            {
                                new IceCreamSupplierInformation
                                {
                                    Name = "Fine Dine",
                                    StandardHygiene = 0.98,
                                },
                            }
                        }
                    );
                });
        }
    }

    internal class Program
    {
        private static void Main()
        {
            using var context = new Context();

            context.Database.EnsureDeleted();
            context.Database.EnsureCreated();

            // Query all ice creams.
            var iceCreams = context.IceCreams
                .OrderBy(i => i.Id)
                .ToList();

            Debug.Assert(iceCreams.Count == 4);
            Debug.Assert(iceCreams[3].PrimarySupplierInformation.RootElement.GetProperty("Name").GetString() == "Fine Dine");
            Debug.Assert(iceCreams[0].AllSupplierInformations.Count == 2);
            Debug.Assert(iceCreams[0].AllSupplierInformations[1].Name == "Fast Fooood");

            // Query ice creams by handling the JSON column as plain text and using a simple LIKE clause. 
            var yellowTaggedIceCreams = context.IceCreams
                .Where(i => EF.Functions.JsonSearchAny(i.Tags, "yellow"))
                .ToList();
            
            Debug.Assert(yellowTaggedIceCreams.Count == 1);

            // Query ice creams by using a sql query and using the MySQL JSON_CONTAINS() function.
            var yellowFoodColoringENumber = "E102";
            var iceCreamsWithYellowFoodColoring = context.IceCreams
                .FromSqlInterpolated($"select * from `IceCreams` where json_contains(`FoodAdditives`, json_quote({yellowFoodColoringENumber}), '$') <> 0")
                .ToList();

            Debug.Assert(iceCreamsWithYellowFoodColoring.Count == 2);

            // Query ice creams by using EF functions.
            var redFoodColoringENumber = "E124";
            var iceCreamsWithRedFoodColoring = context.IceCreams
                .Where(i => EF.Functions.JsonContains(i.FoodAdditives, EF.Functions.JsonQuote(redFoodColoringENumber), "$"))
                .ToList();
            
            Debug.Assert(iceCreamsWithRedFoodColoring.Count == 2);

            var iceCreamsFromFineDine = context.IceCreams
                .Where(i => EF.Functions.JsonSearchAny(i.AllSupplierInformations, @"Fine Dine", "$[*].Name"))
                .OrderBy(i => i.Id)
                .ToList();
            
            Debug.Assert(iceCreamsFromFineDine.Count == 1);

            var iceCreamsPrimarilyFromFineDine = context.IceCreams
                .Where(i => i.PrimarySupplierInformation.RootElement.GetProperty("Name").GetString() == "Fine Dine")
                .OrderBy(i => i.Id)
                .ToList();
            
            Debug.Assert(iceCreamsPrimarilyFromFineDine.Count == 1);
        }
    }
}

[chazt3n]

Ok thank you @lauxjpn - the final puzzle to solve is to accomplish this:

            var results = await _sut.DbContext.Contacts
                .Where(x => x.PhoneNumbers.Any(x => x.PhoneNumber.Contains("808")))
                .ToListAsync();

So that can't be translated to SQL it looks like, so I'm trying different permutations of this unsuccessfully - checking your sample now - thank you!

            results = await _context.Contacts
                .Where(x => EF.Functions.JsonSearchAny(x.PhoneNumbers, "%808%", "$.PhoneNumber"))
                .ToListAsync();

   public class Contact
   {
        public List<Phone> PhoneNumbers { get; set; } = new List<Phone>();

   }

    public class Phone
    {
        public string PhoneNumber { get; set; }
        public string PhoneExtension { get; set; }
        public PhoneType PhoneType { get; set; }
     }

[chazt3n]

This seems to do it! Been a while since I worked with mongo, so the magic operator still alludes me - THANK YOU

            var results = await _sut.DbContext.Contacts
                .AsNoTracking()
                .Where(x => EF.Functions.JsonSearchAny(x.PhoneNumbers, "%8%", "$[*].PhoneNumber"))
                .ToListAsync();

[chazt3n]

is there any documentation on MySqlCommonJsonChangeTrackingOptions?

[lauxjpn]

is there any documentation on MySqlCommonJsonChangeTrackingOptions?

@chazt3n While there is no dedicated docs about them yet, there are the XML comments on the MySqlCommonJsonChangeTrackingOptions class, which I made sure are very detailed, to help make the right choice (they are ordered from fastest/least precise (top) to slowest/most precise (bottom)).

If you got any questions after reading up on them, please go ahead.

[AlphaComposite]

EDIT: Found this thread. Exactly what I was looking for.
#1442

public class Credentials
{
  public JsonObject<string[]> Files { get; set; } // Json storage
}

Getting an error Cannot resolve symbol JsonObject.

What's the new implementation? We're not seeing any direct equivalents in the thread.

[mguinness]

As mentioned in this thread JsonObject is deprecated, see #1442.