Postgresql 如何高性能查询JsonDb

• 创建虚拟列和局部索引

--创建虚拟列 
ALTER TABLE prc_workflow_instance ADD COLUMN fm_bind_flow  text GENERATED ALWAYS AS (form_value -> 'sourceListArr') STORED; 

--创建局部索引 
CREATE INDEX IX_prc_workflow_instance_fm_bind_flow ON prc_workflow_instance USING gin (form_value) WHERE process_code in('ExpenseClaimProcess','BorrowingProcess','PaymentProcess');

•  映射虚拟列 (不想实体直接映射字段)

//映射虚拟列

public static void ConfigurePrcWorkflowInstanceEntity(this ModelBuilder modelBuilder)
{ 
     modelBuilder.Entity<PrcWorkflowInstance>(b => { b.Property<string> ("fm_bind_flow");
}

• EF LING C#查询条件写法

//EF LING C#查询条件写法
var fmBindflows = await (await _workflowRepository.GetDbSetAsync()) 
.Where(s => new InstanceStatus[] { InstanceStatus.Completed, InstanceStatus.Running } .Contains(s.State) && EF.Property<string>(s, "fm_bind_flow") != null) 
.Select(s=>  EF.Property<string>(s, "fm_bind_flow") ) .ToListAsync();

var exFlows = fmBindflows.Select(jarr => JArray.Parse(jarr)).SelectMany(j => j.Values<string>()).ToList();