FrankIversen
commented
3 months ago @jeremydmiller thank you for providing the bug fix for the index, it works beautifully. With regards to the select statement, is that something you think we should fix ourselves? If yes could you provide some breadcrumbs?
We have this piece of code in our application:
opts.Schema.For().Index(x => x.ExternalLookUps, y => y.ToGinWithJsonbPathOps());
with ExternalLookUps being an array of json objects.
which results in this sql
CREATE INDEX supplierproductaggregate_idx_external_look_ups ON dev2purchaseprice.mt_doc_supplierproductaggregate USING gin ((data ->> 'ExternalLookUps') jsonb_path_ops);
which results in this error: error: PostgresException: 42804: operator class "jsonb_path_ops" does not accept data type text
The correct sql should be CREATE INDEX supplierproductaggregate_idx_external_look_ups ON dev2purchaseprice.mt_doc_supplierproductaggregate USING gin ((data -> 'ExternalLookUps') jsonb_path_ops);
so -> instead of ->> . The latter treats the value of ExternalLookUps as text, it should be treated as a json object using ->
Using this approach It will create an inverted index for the values within the ExternalLookUps array.
An additional complication is that the select statement for the externalLookups field:
await DocumentSession.Query()
.Where(t => t.ExternalLookUps.Any(el => el.Id == externalLookUpId))
.ToListAsync();
produces this sql:
select d.id, d.data, d.mt_version from price.mt_doc_supplierproductaggregate as d where CAST(d.data ->> 'ExternalLookUps' as jsonb) @> '[{"Id":"120298d2-7ce6-5ed1-b50f-03716896f0a6"}]';
Everything in data is already JSONB so I am thinking that the cast is unnecessary and again the more correct operator would then be the -> operator to treat the object as a json object this way it will also use the index created previously. This would result in this sql:
select * from price.mt_doc_supplierproductaggregate where (data -> 'ExternalLookUps') @> '[{"Id": "120298d2-7ce6-5ed1-b50f-03716896f0a6"}]'
Hope it all makes sense and thank you for all the work. Let me know if something is not clear or if I can contribute in some way.