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