SelectMany on Json Array column

Question

In my project i have table with Json Array Column

Table A

Id,  Col1, Col2, JsonArrayCol, Col3
123, Val1, Val2, Json*       , Val3

Json*:

[
   {
      "Type" : "StandardType",
      "Amount" : "1"
      // a lot more properties
   },
   {
      "Type" : "StandardType",
      "Amount" : "2"
   },
   {
      "Type" : "OtherType",
      "Amount" : "3"
   },   {
      "Type" : "SuperType",
      "Amount" : "4"
   },
] 

In some standard scenerio we need to join rows from table to object from json array filter by type.

ex. If filter is set to "StandardType" and "SuperType" then result should be:

123, Val1, Val2, JsonValues ("Type" : "StandardType", "Amount" : "1") , Val3
123, Val1, Val2, JsonValues ("Type" : "StandardType", "Amount" : "2") , Val3
123, Val1, Val2, JsonValues ("Type" : "SuperType"   , "Amount" : "4") , Val3

Parse JSON array to model .NET Core causes empty set Based on answer in this topic as first step to achieve my requirements i was trying to use SelectMany (LinqToSQL):

        var searchContacts = context.SearchContacts
            .SelectMany(x => x.Numbers)
            .ToList();

Unfortunately i get error:

The LINQ expression 'x => x.Numbers' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.'

Answer 1

You may need to perform parts of the query on the client side after fetching the data from the database

var filterTypes = new[] { "StandardType", "SuperType" };

var result = context.SearchContacts
    .Where(a => filterTypes.Any(filterType =>
        a.JsonArrayCol.Any(item =>
            item.Type == filterType)))
    .Select(a => new
    {
        a.Id,
        a.Col1,
        a.Col2,
        JsonValues = a.JsonArrayCol
            .Where(item => filterTypes.Contains(item.Type))
            .ToList(),
        a.Col3
    })
    .ToList();