Postgres JSONB query

Hi, I do not know how to fulfill the request and get the result…

I have such a request, it works and shows the result:

I’m trying to execute such a query in SS but get an error:

public class CatalogItem {        
    public string Id { get; set; }
    [PgSqlJsonB]
    public List<CatalogProperty> Properties { get; set; }
}

public class CatalogProperty {
    public string Name { get; set; }
    public string Value { get; set; }
}

 using (var pg = new OrmLiteConnectionFactory(pgConnectionString, PostgreSqlDialect.Provider).OpenDbConnection()) {               
                var filters = pg
                    .Select<List<Dictionary<string, string>>>(pg
                    .From<PGModel.CatalogItem>(
                        "select prop->'Name' as name, prop->'Value' as value" +
                        " from (SELECT JSONB_ARRAY_ELEMENTS(properties) AS prop FROM public.catalog_item ) sub ORDER BY sub.prop->'Name'"));
            }

How it is possible to execute such query?

Thanks in advance.

The From<T>(expression) is only for specifying a custom FROM expression not a SQL statement.

You’d typically using db.SqlList<T>("SELECT....") for executing custom SELECT SQL statements.

Oh, yeah, sure. Thx!