I’m currently building a query to retrieve some data from my db, I need to access the documents stored in an array, this is how the table
crm_company
looks:
+ company_id + documents_avaiable + - 1 - [71788176,98705180,21468287,...] - - 2 - [11218494,12825726,10353038,...] -
I have another table for users by document crm_user
which looks like this:
+ user_document + email + - 71788176 - abc@email.com - - 98705180 - def@email.com - - 11218494 - hef@email.com -
What I want as a final result is:
+ user_document + email + company + - 71788176 - abc@email.com - 1 - - 98705180 - def@email.com - 1 -
I just have tried somehing like the following:
select documents_avaiable[0] from crm_company where crm_company.id = 1
But I’m still unable to retrieve the documents from array.
Thanks in advance for any hint or help.
Advertisement
Answer
If it’s too late to follow the advice in the comment of Laurenz Albe then do this:
Use a query (or create a view) to have the list of company documents normalized
create view company_documents as select t.company_id, l.document_id from crm_company t cross join lateral ( select unnest(t.documents_avaiable) as document_id ) l;
Access documents’ details in other tables by
document_id
usingjoin
select cd.document_id user_document, cd.company_id company, d.email from company_documents cd join crm_user d on cd.document_id = d.user_document;