Postgres JSONB query

ServiceStack.OrmLite
1

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:

image.png952×334 33.5 KB

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'"));
            }

image.png839×238 33.2 KB

How it is possible to execute such query?

Thanks in advance.

2

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.

3

Oh, yeah, sure. Thx!