How to unnest and order an array

36 views Asked by At

Is there a way to specify the order of unnesting a string from an arrary? I'm building a report in sql that takes a description of a baseline_intake_form for cars and takes the response the customer's response and puts it in a report. I know unnest() doesn't guarantee a specific order to flattening an array, but is there a way to order the second query so that when I join both together the results appear in the same order all the time?

The intake code is as follows:

select 
form_submit_id
, b.form_baselines
from claim_warehouse.aggregate_intake_form_baselines 
left join unnest (form_baselines) b
where form_submit_id=1234 
and key="target"

The returned result is: Collision damage, repair exterior, fresh paint, potential totaled vehicle That is what I am expecting. When I look at the response description of online forms I get a different variation: Code:

SELECT 
form_id 
, form_submit_id 
, m.metadata.answer_description
FROM client_warehouse.client_response_forms forms
left join unnest(form_metrics) m 
where form_submit_id = 1234 and m.key = 'response'

Results: "repair exterior, potential totaled vehicle, Collision damage"

Expected Results:

     form_id |  baseline        | response 
     ----+----------------------+---------------------
     1234    |  collision damage| collision damage|   

For space reasoning I shortened the expected results

Full join example looks like such:

select 
base.form_submit_id
, forms.client_login_acct_num
, b.form_baselines
, m.metadata.answer_description
from claim_warehouse.aggregate_intake_form_baselines base 
left join unnest (form_baselines) b
left join client_warehouse.client_response_forms forms
on base.form_submit_id = forms.form_submit_id
left join unnest(form_metrics) m 
order by
1, 3, 4

0

There are 0 answers