Query Key Values

Question

I currently have a data table that that uses key/values similar to this: 
 
 param_table{ 
 id, 
 taskId, 
 key, 
 value 
 } 
 row1 { 
 1, 
 123, 
 "string", 
 1 
 } 
 
 row2{ 
 2, 
 123, 
 "string2", 
 2 
 } 
 
 I need to return taskId based on key{string,string2} & value{1,2}. What would be the best way to do this?

jeromew · Answer

Something like this might work if you have an ordered key and value list:

a!queryEntity(
entity: <yourentity>,
query: a!query(
    selection: "taskId",
    logicalExpression: a!queryLogicalExpression(
      logicalExpressions: a!forEach(
        items: <yourkeylist>,
        expression: a!queryLogicalExpression(
          operator: "=",
          filters: a!queryFilter(
            field: "key",
            operator: "=",
            value: <yourvaluelist>[fv!index]
          )
        )
      )
    ),
    pagingInfo: a!pagingInfo(startIndex: 1, batchSize: -1, sort: {})
)
)

Let me tell you why this is a good way to implement this solution.

1. You're limiting the data being retrieved with the selection: "taskId". Loading less data is more efficient.

2. You're setting the filters up front which also limits the data being retrieved. This is a more efficient solution than loading the data in and trying to iterate it with wherecontains() or other functions.

Always try and reduce the dataset up front so you retrieve only the needed data.

Shubham Aware · Answer

Hi Jawara J , 
 
You can use a!queryEntity if you have data is in database By applying queryFilter you can retrieve data based on key. 
You can use index, filter, wherecontains function to filter out data based on particular key. 
 
Simple solution to this is you can create expression rule which contains key as well as value like dictionary and simple calling that rule you can approach this.

santoshd378 · Answer

Hi Jawara, 
 
By using the query entity you can achieve this, use the following code: 
 
a!queryEntity( 
 entity: Your entity constant, 
 query: a!query( 
 selection: a!querySelection( 
 columns: { 
 a!queryColumn( 
 field: "taskId" 
 ) 
 } 
 ), 
 filter: a!queryFilter( 
 field: "Key", 
 operator: "=", 
 value: ri!Key /*create ri variable to pass the key dynamically while calling your expression rule*/ 
 ) 
 ) 
)

Abhay Giri · Answer

Hi, 
 
Use a!queryentity to get taskid, 
 
a!queryEntity( 
 entity: entity constant, 
 query: a!query( 
 pagingInfo: a!pagingInfo( 
 startIndex: 1, 
 batchSize: 1 
 ), 
 filter: a!queryFilter( 
 field: "key", 
 operator: ="=", 
 value: ri!keyName 
 ) 
 ) 
).data.value

or you can use selection as well: 
 
a!queryEntity( 
 entity: entity constant, 
 query: a!query( 
 pagingInfo: a!pagingInfo( 
 startIndex: 1, 
 batchSize: 1 
 ), 
 selection: a!querySelection( 
 columns: a!queryColumn("Value") 
 ) 
 
 filter: a!queryFilter( 
 field: "key", 
 operator: ="=", 
 value: ri!keyName 
 ) 
 ) 
) 
 
Regards 
Abhay