Exact Match with column separated with a delimiter and Appian Query

Question

Is there a best practice with regards to finding an exact match from a delimited list inside a MySQL table column using a query rule? 
 Assume that standard, better solutions are out of scope - i.e. mapping tables to avoid the delimited lists inside columns, or using multiple rows for each ID and account number. 
 
 Example: 
 Clients Table

ID 
 Account Numbers

1 
 12345, 123456

2 
 123456, ABCDEF

Query for 1234 should return no results (exact match desired). Query for 123456 should return 1,2. Query for 12345 should return 1, but not 2. (" includes " in Appian would return 1,2) 
 
 My current solution is using MD5, but wondering if there is a better method.

ID 
 Account Numbers 
 Account Numbers MD5

1 
 12345, 123456 
 827CCB0EEA8A706C4C34A16891F84E7B, E10ADC3949BA59ABBE56E057F20F883E

2 
 123456, ABCDEF 
 E10ADC3949BA59ABBE56E057F20F883E, 8827A41122A5028B9808C7BF84B9FCF6

Query using " includes " and MD5(parameter) correctly returns expected results. 
 i.e. A user inputs "12345" in a text field, it is passed into md5hash(), and that is passed to the query on the table which then uses "includes". 
 a!queryFilter( 
 field: "accountNumberMD5", 
 operator: "includes", 
 value: md5hash(ri!accountNumber) 
 )

Mike Schmitt · Accepted Answer

For clarification, are you saying that the "Account Numbers" DB column is storing a plaintext array of Account Numbers that's just comma-separated? There's no chance of doing this in a more normalized format such as a link table? You'd likely get quite a bit more flexibility out of that sort of setup, in terms of querying from within Appian (presumably into a View that flattens it somewhat). 
 Assuming the answer to my last question is "No", then do you have any say over how the Account Number data is stored? You'd probably be able to do more exact operations more easily if your data was stored in json arrays instead of plaintext. That is to say, if you want to search on an account number "12345" in a row containing that account number and others, using an "includes" style query without matching a row containing an account number "123456", then if your account numbers were stored in JSON arrays then you would be able to simply query using an "includes" but also sending the literal quote marks through in the query since the account number would also be delineated by quotes in the DB table. Another reason JSON would be helpful when storing hardcoded arrays like this is it takes a bit of guesswork out of parsing the arrays when retrieved in the future.

Mike Schmitt · Answer

Grouping and joining does get pretty brain-numbing pretty quickly in SQL, but the sort of thing I was thinking of is a bit more straightforward. Basically you could use GROUP_CONCAT or other aggregators to simulate your column of "list of account numbers per client". IE: 
 To get that I just whipped up my own quick test table CLIENTS and test table ACCOUNTS, and wrote a view that groups by CLIENT_ID and plays around with a few different methods of aggregating all the accounts under that row's client: 
 CREATE TABLE TEMP_TEST_CLIENTS(
 CLIENT_ID int(11) NOT NULL AUTO_INCREMENT,
 NAME varchar(50),
 
 PRIMARY KEY(CLIENT_ID)
);


CREATE TABLE TEMP_TEST_CLIENT_ACCOUNTS(
 ACCOUNT_ID int(11) NOT NULL AUTO_INCREMENT,
 CLIENT_ID int(11) NOT NULL,
 ACCOUNT_NUM varchar(50),
 
 PRIMARY KEY(ACCOUNT_ID)
);


INSERT INTO `TEMP_TEST_CLIENTS` (`CLIENT_ID`, `NAME`) 
 VALUES ('1', 'Client 1'), ('2', 'Client 2')

INSERT INTO `TEMP_TEST_CLIENT_ACCOUNTS` (`ACCOUNT_ID`, `CLIENT_ID`, `ACCOUNT_NUM`) 
 VALUES ('1', '1', 'ABCDE'), ('2', '1', 'ABCDEF'), ('3', '2', '12345'), ('4', '2', 'ABCDE')
 

 
CREATE OR REPLACE VIEW TEMP_TEST_CLIENT_ACCOUNT_VIEW AS

SELECT
 client.CLIENT_ID,
 client.name AS CLIENT_NAME,
 group_concat(account.ACCOUNT_NUM separator ', ') AS CLIENT_ACCOUNT_NUMS,
 JSON_ARRAYAGG(account.ACCOUNT_NUM) AS ACCOUNT_NUMS_JSON,
 CONCAT(
 '[',
 GROUP_CONCAT(
 JSON_OBJECT(
 'aId', account.ACCOUNT_ID,
 'aNum', account.ACCOUNT_NUM
 )
 ),
 ']'
 ) AS ACCOUNT_CDT_INFO_JSON
 
 FROM TEMP_TEST_CLIENTS `client`
 LEFT JOIN TEMP_TEST_CLIENT_ACCOUNTS `account`
 ON account.CLIENT_ID = client.CLIENT_ID
 
 GROUP BY client.CLIENT_ID 
 This way you can keep your client and account tables tidier, you could easily query all accounts for a client in their own data type, and you could also do advanced searches using the view (and the json options would make it easy to break down the account numbers for an individual row for use within individual Paging Grid cells, hopefully you're using the newer style paging grid!)

Exact Match with column separated with a delimiter and Appian Query

Top Replies