Accessing array elements in query postgres

Question

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    - [email protected] -  
-   98705180    - [email protected] - 
-   11218494    - [email protected] -

What I want as a final result is:

+ user_document +     email     + company +
-   71788176    - [email protected] -    1    -
-   98705180    - [email protected] -    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.

Yes, that is possible, but more complicated than necessary. it is a really bad idea to store "foreign keys" in an array, and now you know why. Normalize your data model! — Laurenz Albe
– Laurenz Albe, Commented Nov 12, 2020 at 7:28
What have you tried ? Show us some data from "another table". Show your desired result from this two tables from the data you presented and will present... — VBoka
– VBoka, Commented Nov 12, 2020 at 7:34
@LaurenzAlbe is not that easy for me to modify the models, is an external datasource, thanks for the advice. — Iván Herrera
– Iván Herrera, Commented Nov 12, 2020 at 7:41

Stefanov.sm · Accepted Answer · 2020-11-12 07:57:28Z

1

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 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;

edited Nov 12, 2020 at 7:57

answered Nov 12, 2020 at 7:32

Stefanov.sm

13.2k2 gold badges24 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user330315 Over a year ago

Note that cross join lateral (select unnest(...) as document_id) l can be simplified to cross join lateral unnest(..) as l(document_id)

Collectives™ on Stack Overflow

Accessing array elements in query postgres

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related