Skip to content
Advertisement

Accessing array elements in query postgres

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:

  1. 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;
    
  2. Access documents’ details in other tables by document_id using join

    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;
    
User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement