Greetings,

I am extremely interested in leveraging the json/jsonb datatype in Postgres.

I need some guidance on how to create this filter/predicate using queryspec / something else?

Here is the SQL Query (PostgreSQL):

SELECT * FROM "Project" WHERE "CustomData"->'finished' = 'false';  

Query Factory

            var qf = new QueryFactory();
            var q = qf.Project
                       .Where(ProjectFields.CustomData ???);

sample JSON:

{"finished":false,"ingredients":["Tortillas","Guacamole"],"name":"Cook lunch","tags":["Cook","Kitchen","Tacos"]}

.Net Core 2.0 LLBLGEN 5.3 (5.3.0 RTM) --> Build Date: 13-oct-2017 Adapter SD.Presets.Adapter.General.Netstandard_DTO (custom with DTO Templates) npgsql version 3.2.5 LLBLGen Pro Runtime Framework

That construct is not included in the filtering system. However you can try with a custom FunctionMapping.

Ok,

So using the fulltext example, I created all of the necessary classes / methods. Verified they were loading /running ok.

The issue is that I am always getting Where (1=1). It does not matter what I set the filter to, the SQL does not change.

The problem I believe is this... There is no DB Function (Postgres) that I can map to / call to produce the output correctly...

[column name] -> [attribute name] = [value]

fulltext example for SQL Server is actually mapping to Contains() which is in the DB.

For SQL Server they have a list of JSON related functions that are available which i am pretty confident I could map to.

IsJSON or JSONValue would be good examples of that.

I really just need to somehow generate that where clause in a standard way, and include all of the LLBLGEN prefetch goodness.

I already created an expression using FetchQuery and was able to project the result to a LLBLGEN entity without issue, but it would be nice to not have to do that for this specific use case (i.e. json / jsonb data types)

using straight up SQL:

            using (var adapter = new DataAccessAdapter())
            {
                try
                {
                    projectList = adapter.FetchQuery<ProjectEntity>("SELECT * FROM \"Project\" WHERE \"CustomData\"->>'finished' = 'false';");
                }
                catch
                {
                    throw;
                }
            }

I have included my code in case I made any glaring typos / mistakes.

    public class DBFunctions
    {

        public static bool SearchJson(string fieldToSearch, string toAttribute, string toFind)
        {
            return true;
        }
    }

    /// Class which defines the custom mapping between DBFunctions.FullTextSearch and CONTAINS()
    public class JsonFunctionMappings : FunctionMappingStore
    {
        public JsonFunctionMappings() : base()
        {
            this.Add(new FunctionMapping(typeof(DBFunctions), "SearchJson", 3, "\"{0}\"->> '{1}' = '{2}';"));
        }
    }
}

            using (DataAccessAdapter adapter = new DataAccessAdapter())
            {
                var _metaData = new LinqMetaData(adapter);
                _metaData.CustomFunctionMappings = new JsonFunctionMappings();

                var result = from e in _metaData.Project
                             where DBFunctions.SearchJson("CustomData", "finished", "true")
                             select e;
            }

Thanks for taking the time to look at this...

Reproduced. Looking into it. Also passing in a field doesn't produce the predicate...

I made a mistake in testing this. The problem with your approach is that the function call isn't seen as a part that runs on the DB (no db element is found, you pass in constants).

So instead do this:

var result = from e in _metaData.Project
                   where DBFunctions.SearchJson(e.CustomData, "finished", "true")
                   select e;

here the function call has binding to the db, as the db located field is passed in.

Furthermore, you've some errors in the function mapping ->

    /// Class which defines the custom mapping between DBFunctions.FullTextSearch and CONTAINS()
    public class JsonFunctionMappings : FunctionMappingStore
    {
        public JsonFunctionMappings() : base()
        {
            this.Add(new FunctionMapping(typeof(DBFunctions), "SearchJson", 3, "{0}->> {1} = {2}"));
        }
    }

This one works, and gives a query like:

SELECT "LPA_L1"."ID"       AS "Id",
       "LPA_L1"."JsonBCol" AS "JsonBcol",
       "LPA_L1"."JsonCol"
FROM   "public"."JsonTab" "LPA_L1"
WHERE  (("LPA_L1"."JsonCol" ->> :p4 = :p6)) 

be aware that any constant is passed as a parameter.

Frans,

Thanks a ton. Working as designed.

Really appreciate the time,

Jon