Help with passing a list of ID's to a WHERE clause

Help
1

Hi :wave:

I am having difficulty passing a list of IDs as a parameter to a stored procedure and then using the list to filter the results of a query. I have tried a couple of different approaches with no luck, is someone please able to help?

The code below is to show what I am trying to achieve:

DELIMITER //
CREATE OR REPLACE PROCEDURE filter_list_of_ids (input_id JSON NULL) AS
DECLARE
  user_table_filtered QUERY(
                      id_user VARCHAR(36), 
                      id_asset VARCHAR(36)
                      ) = SELECT u.id_user, u.id_asset 
                          FROM user_table as u 
                          WHERE u.id_user IN (SELECT 1 FROM TABLE(input_id));
BEGIN
  ECHO SELECT id_user, id_asset FROM user_table_filtered;
END //
DELIMITER ;

Where

CALL filter_list_of_guids('["user1", "user2", "user3"]');

Should return results for users 1,2 and 3. Please let me know if this approach is wholly wrong or if there are simpler solutions that give the same result. Thanks!

Alternative attempt:

  • Pass JSON array to stored procedure
  • insert JSON array to temporary table
  • Use Join to function as a filter on column?
  • Drop temporary table
DELIMITER //
CREATE OR REPLACE PROCEDURE myProcedure(jsonArray JSON NULL) as
DECLARE 
myArray ARRAY(JSON) NULL;
tst QUERY(col1 varchar(36)) = select * from tst;
BEGIN
    DROP TEMPORARY TABLE my_temp_table;
    CREATE TEMPORARY TABLE my_temp_table (col1 varchar(36));
    INSERT_ALL(my_temp_table, JSON_TO_ARRAY(jsonArray));
    ECHO SELECT * FROM my_temp_table 
                  INNER JOIN tst on my_temp_table.col1 = tst.col1;
    DROP TEMPORARY TABLE my_temp_table;
END //
DELIMITER ;

CALL myProcedure('["value1", "value2", "value3"]');
2

In your first SP, you have

id_user IN (SELECT 1 FROM TABLE(input_id))

That nested select returns a bunch of 1s, not the input_id values.

TABLE outputs a 1-column table with column name table_col:

singlestore> select * from table(json_to_array('[1,2,3]'));
+-----------+
| table_col |
+-----------+
| 1         |
| 2         |
| 3         |
+-----------+

So you should try something like
id_user IN (SELECT table_col FROM TABLE(input_id))

I stopped reading there. Hopefully that helps.

Consider also using JSON_ARRAY_CONTAINS_<type> · SingleStore Documentation

The TABLE function is flexible, but not always the fastest. JSON_ARRAY_CONTAINS might be faster.