JsonContains and sub-properties

[DashNY]

Is there a support for querying by a sub-property of an object within a collection?
For example, I have the following classes defined

public class MyEntity
{
    [Column("Info", TypeName = "jsonb")]
    public Info Info { get; set; }
}

public class Info
{
    public List<Tag> Tags { get; set; }
}

public class Tag
{
   public int TagId { get; set; }
   public int OtherData { get; set; }
}

How do I find all the MyEntity rows that have at least one Tag with TagId = 10 ?
Here's what I've tried so far:

// didn't work
var result = db.MyEntities.Where(m => EF.Functions.JsonContains(m.Info.Tags.Select(t => t.TagId), "10") ).ToList();

[roji]

Seems to have been cross-posted on SO: https://stackoverflow.com/questions/65347699/search-for-matching-sub-property-value-in-json-array

[roji]

This isn't supported at the moment. It would probably be doable via jsonpath (see #1045).

[don-flamingo]

Is exists any workaround for it?

I Tried something like it:

if (request.Tags != null && request.Tags.Any())
{
      queryable = queryable.Where(x => EF.Functions.JsonExistAny(x.Tags, request.Tags));
}

But still I only have got an exception:

The LINQ expression 'DbSet<DeviceLibraryItem>()
    .Where(d => d.TenantId == __request_TenantId_0)
    .Where(d => d.FolderId == __request_FolderId_1)
    .Where(d => __Functions_2
        .JsonExistAny(
            json: d.Tags, 
            keys: __request_Tags_3))' could not be translated. Additional information: Translation of member 'Tags' on entity type 'DeviceLibraryItem' failed. This commonly occurs when the specified member is unmapped. 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.

[roji]

I don't think there's a workaround at this point - though you can always use raw SQL.

Note to self: JsonExistsAny translates to the ?| operator, which accepts an array on the right-hand side. #1691 will most probably introduce an expression for ARRAY[], at which point we can embed a subquery inside that.

[don-flamingo]

raw SQL does not help me in that case...
I think about custom IMethodCallTranslator for translate it to SQL part:

SELECT c."Id", c."Name", c."Tags"
	FROM public."DevicesLibrary" AS c
	WHERE (c."Tags" @> '[{"Value": "yellow"}]' OR  
		   c."Tags" @> '[{"Value": "dark-choco"}]')

[roji]

If this SQL is what you're looking for (expand JsonExistAny to a bunch of ORs), it should be pretty simple to write a method that does this yourself, running a visitor over the expression tree and performing the necessary replacing. However, it's not something that I think belongs in the provider - expanding to ORs means that different SQL is generated based on different values, which is problematic for query/plan caching.

[Nordes]

It would be nice to have something like ContainsAny which would translate to c."Tags"->"Value" @> ANY('{"yellow","dark-choco"}'). I was not able to make this using the existing functions since the ANY can't appear without using a database item.

[roji]

Yeah, that should indeed be possible. For completeness, the actual query would be:

SELECT '{"a":1, "b":2}'::jsonb @> ANY(ARRAY['{"b":1}'::jsonb, '{"a":1}'::jsonb]);

[LINDFJ25477-pki]

Wouldn't this work in PostgreSQL syntax?

SELECT c."Id", c."Name", c."Tags"
FROM public."DevicesLibrary" AS c
CROSS JOIN LATERAL jsonb_array_elements(c."Tags") tags(tag)
WHERE (tag->>"Value")::text IN ("yellow", "dark-choco")

[roji]

@LINDFJ25477-pki any particular advantage to that over the terser alternative above?

[grcd]

Is there a support for querying by a sub-property of an object within a collection? For example, I have the following classes defined

public class MyEntity
{
    [Column("Info", TypeName = "jsonb")]
    public Info Info { get; set; }
}

public class Info
{
    public List<Tag> Tags { get; set; }
}

public class Tag
{
   public int TagId { get; set; }
   public int OtherData { get; set; }
}

How do I find all the MyEntity rows that have at least one Tag with TagId = 10 ? Here's what I've tried so far:

// didn't work
var result = db.MyEntities.Where(m => EF.Functions.JsonContains(m.Info.Tags.Select(t => t.TagId), "10") ).ToList();

What if:

var result = db.MyEntities.Where(m => EF.Functions.JsonContains(m.Info, @"{""Tags"": {""TagId"": ""10""}}")).ToList();

?

[roji]

@grcd that checks whether the entire Tags sub-document exists as provided, so it checks whether there's only one tag with ID 10 (any additional tag will cause this to return false).