- Newest
- Most votes
- Most comments
Hello,
Greetings of the day!.
Thank you for reaching out with your concern in AWS re:Post.
Please find the below considerations for your use case.
============
- For this I request you to please create a temporary table to store the values you pass in. You may not use json but instead, just request to pass in a string that represents an array of ids you are looking for.
Like:
create temporary table t as select split_to_array('1,2,3', ',') as id_array;
And then unnesting it:
select id from (select id_array from t) as sub, sub.id _array id;
If you do it without a temporary table, you may see this error message: select id from (select split_to_array('1,2,3', ',') as id_array) as sub, sub.id _array id; ERROR: Unnest subquery's result on leader is not supported
Or something like this
CREATE OR REPLACE PROCEDURE "pkg_util_test_passing_json"( v_json_ids varchar, INOUT "result" refcursor) LANGUAGE plpgsql AS $$ DECLARE BEGIN drop table if exists tempIds; create temp table tempIds as SELECT JSON_PARSE(v_json_ids) as ArrId; OPEN result FOR SELECT * FROM mytable WHERE id IN (select e.id from tempIds as t, t.ArrId as e at index); END; $$
- Another workaround, and probably better than a temp table, is to please make use of a dummy table like Oracle has built-in.
create table dual (dummy varchar(1)); insert into dual values ('x');
select id from (select split_to_array('1,2,3', ',') as id_array from dual) as sub, sub.id _array id;
Output: id
"1" "2" "3" (3 rows)
You would replace '1,2,3' with the id list parameter you pass in. This would help to persist with the dual table.
=============
In order to process json it has to be stored in a table/temp table before processing it.
I hope that answers your query.
Thank you and have a great day ahead!
I was hoping to convert json to SUPER using json_parse and then use PartiQL but not matter what I've done I was unable to unflatten the json array without temp tables. So I ended up using a temp table and here's the solution I came up with:
CREATE OR REPLACE PROCEDURE pkg_util_test_passing_json(v_json_ids character varying(256), INOUT "result" refcursor)
LANGUAGE plpgsql
AS $$
DECLARE
valid_json_array BOOLEAN;
array_length INTEGER;
i INTEGER := 0;
BEGIN
CREATE TEMPORARY TABLE t (id INT);
SELECT is_valid_json_array(v_json_ids) INTO valid_json_array;
IF ( valid_json_array ) THEN
SELECT json_array_length(v_json_ids) INTO array_length;
END IF;
IF ( array_length > 0 ) THEN
<<simple_loop_when>>
LOOP
-- RAISE INFO 'i %', i;
INSERT INTO t(id) SELECT json_extract_path_text( json_extract_array_element_text(v_json_ids, i, true ) , 'id')::int;
i := i + 1;
EXIT simple_loop_when WHEN (i >= array_length);
END LOOP;
OPEN result FOR
SELECT * FROM "my_table" WHERE my_id IN (SELECT id FROM t);
END IF;
END;
$$
Relevant content
- Accepted Answerasked 10 months ago
- asked 5 years ago
- Accepted Answerasked 3 months ago
- AWS OFFICIALUpdated 2 years ago
- AWS OFFICIALUpdated 2 years ago
- AWS OFFICIALUpdated a year ago
- AWS OFFICIALUpdated 2 years ago