How to use unnest and "with ordinality" to order rows or arrays based on their occurrence in a list

unnest-with-ordinality.sql

create table emojies(
	name text
 );
 
 INSERT INTO emojies VALUES('👻'), ('👺'), ('👹'), ('🤡'), ('🏚️'), ('🛸')
 
 
select * from emojies
JOIN UNNEST(ARRAY['🛸', '👻', '👺', '🏚️', '👹', '🤡']) WITH ORDINALITY my_order_table("name", "ordinality_order") USING("name")
ORDER BY "ordinality_order"
 

create table test(
	name text[],
	id varchar(14),
	email varchar(50)
 );
 
 INSERT INTO test (name, p_id, p_email)
VALUES (ARRAY['😱', '👿'], 'random-id', '[email protected]');


INSERT INTO test (p_name, p_id, p_email)
VALUES (ARRAY['🎃', '🎭', '🥀'], 'DELU-8529-HONA', '[email protected]');



-- get the names from p_name array and order them by their occurrence order in the array

SELECT extracted_name 
FROM test JOIN unnest(name::text[]) 
WITH ORDINALITY my_table("extracted_name", "ordinality_order") on true 
ORDER BY ordinality_order;



SELECT * FROM  UNNEST(ARRAY['9c4e79d874d113a','1e3d0fpfdewa24']::TEXT[])
WITH ORDINALITY my_table("id", "ordinality_order")
ORDER BY "ordinality_order";