Maintain order in jsonb_array_elements after filtering elements

Question

Having this query

SELECT id, listing -> 'description' AS description, listing -> 'vin' AS vin
FROM public.listings,
     jsonb_array_elements(data -> 'attributes' -> 'listings') listing
WHERE id = '12070'
  AND listing -> 'vin' ?| array ['0HSDZTZR9LN000000','3HSDZTZR9LN056080']

I get this result

12070,"Pie","3HSDZTZR9LN056080"
12070,"Soda","0HSDZTZR9LN000000"

Is there a way to maintain the same order as the one provided in the array for filtering? array ['0HSDZTZR9LN000000','3HSDZTZR9LN056080']

Answer 1

Not automatically. You can just add an ORDER BY to put it into order, but this will require repeating yourself to some extent as the array would be provided twice:

SELECT id, listing -> 'description' AS description, listing -> 'vin' AS vin
FROM public.listings,
     jsonb_array_elements(data -> 'attributes' -> 'listings') listing
WHERE id = '12070'
  AND listing -> 'vin' ?| array ['0HSDZTZR9LN000000','3HSDZTZR9LN056080']
ORDER BY array_position( array ['0HSDZTZR9LN000000','3HSDZTZR9LN056080'], listing ->> 'vin');