Exact Match with column separated with a delimiter and Appian Query

I would try to solve that in DB. Maybe a stored procedure triggered by an insert populating a separate table with account numbers and matching ids.

Thank you for the quick response .

I should add more context:

This is within an imitation of a materialized view (table + triggers on a main table and secondary, key value pair table) and is related to search. We have a number of columns identified as important for search (around 16) so the materialized view imitation draws the first few columns from the main table and the others are populated using triggers and group_concat.

The ask is that with one query (for performance reasons) we can return a usable datasubset within a paging grid. A view is out of the question due to the sheer size of both the main and KVP tables.

OK. Then why not prepare the search table exactly as needed? Each combination of matching values becomes one row.

Thanks again for the quick response.

Just to clarify, would it look something like this and then get marshalled into a datasubset in Appian?

PK ID    accountNumber   | anotherColumnA | anotherColumnB

1    1    12345                    |                             |  LKJH

2    1    123456                  |   DHGJ

3    2    123456                  |

4    2    ABCDEF                | 

Wondering how to best place the data coming into columns A and B (as it's inserted into the KVP table for keys "anotherColumnA" and "anotherColumnB" using a trigger), and how to best return that as one result (ID 1) within a datasubset if a user enters "12345" for accountNumber and "DHGJ" as their second search parameter on column A and "LKJH" as their third for column B.

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.

Yeah, that's tricky. How much data do you have? Asking because of potential performance issues. What I am currently thinking of is something like this.

Table A is just ID + Single search value. Your query is something like where searchValue="12345" or searchValue="DHGJ". Now you want to get only unique IDs (group by). Joining this with your prepared record table should give you the results.

Maybe I am on the wrong path here. Has been a long day ...

This is terrific (thank you good sir and thanks for the frequent responses). 

The answer is "Maybe" but most likely "No" with regards to a better, normalized solution - this might expose some gaps in my Appian knowledge but AFAIK it's not straightforward to marshal a series of rows that might be returned using multiple search parameters (like in the above table with multiple columns) into a datasubset that can be consumed by a paging grid.

"Yes" with regards to not being able to change how the data is stored in the KVP table, I can however influence how the data is stored in the imitation of the materialized view, so I could definitely research and most likely implement a solution where the triggers that populate the materialized view on inserts, updates, and deletes package the KVP table rows into a JSON structure that would be ready for consumption.

The exact quotes would cut down on the calculations required.

The main table should support up to hundreds of thousands of rows, and about 6x that amount for the KVP table....

I am grateful for the response and that is helpful. I got a bit lost on the group by / join after the initial query (apologies)

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!)

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!)