Skip to main content
rkandasamy-21-Visitor
1-Visitor
July 6, 2017
Question

QueryDataTableEntries with detailed query parameters

  • July 6, 2017
  • 2 replies
  • 3630 views

Hi Team,

we have a scenario as below where we have a number of fields as follows,

OrderNumber,Customer,BoardsArray,Status.

We have the following values in that,

('Order1','Client1','{"SN1", "SN2"}','ACTIVE');

('Order2','Client2','{"SN2", "SN3","SN4"}','ACTIVE');

('Order3','Client3','{"SN2", "SN3","SN4","SN5","SN6","SN7","SN8","SN9","SN10"}','ACTIVE');

('Order4','Client4','{"SN1", "SN7"}','ACTIVE');

in postgreSQL, it is possible to query even based on the array items as follows,

SELECT *from TableName   where  'SN1' = ANY (BoardsArray::text[]);  (which returns records of Order1 and Order4)

Present option:

1. QueryDataTableEntries of that table without any condition

2. Loop through that result and find out each BoardsArray in that records has this value

But present way is costly as it brings all the records into the memory and performs the operation on it. Please let me know if there any better way to do the same.

Is there any way the same can be done in Thingworx with what thingworx filters are given?

2 replies

posipova20-Turquoise
20-Turquoise
July 6, 2017

You may specify a query in json format, arbitrary example for a stream

query = {"filters":{"fieldName":"a","type":"EQ","value":"<<<valueto match>>>"}}

(specify valueto match as one of the values for 'a' from the add entries)

wposner-21-Visitor
1-Visitor
July 6, 2017

In your situation--needing to query on values contained in an infotable within a datatable-- I would recommend attempting to normalize your data and remove the infotable from your datatable.  You would use ID values in place of your infotable and once you've found the rows within your primary datatable, a quick loop on those rows to perform a query against your new datatable based on the contained ID value, followed by an intersect operation will give you the same result.

Terms & ConditionsAccessibility statement