Query join two table but another table need to be in another inside array

Question

Hello just a question from a newbie i just to query this two and i want the second table to be inside the array of the other table.

the table looks like this

post

|d : 1"|
|content: "test"|
|id : 2|
|content: "test2"|

post_images

|id : 1|
|post_id : 1|
|img_name : 1.jpg|
|id : 2|
|post_id : 1|
|img_name : 2.jpg|
|id : 3|
|post_id : 2|
|img_name : 3.jpg|
|id : 4|
|post_id : 2|
|img_name : 2.jpg|

My query looks like this

Select posts.*,posts_images.* 
from posts 
INNER JOIN posts_images on posts_images.post_id = posts.id

is it possible to get this data look like this

array
(
   id:1,
   content:test,
   imgs:array
   (
       1.jpg,
       2.jpg
   )
)

I hope you understand what I want to say. :) looking forward for your help thanks in advance

Are you using postgresql? mysql?

zebnat
– zebnat

2018-09-25 14:23:53 +00:00
Commented Sep 25, 2018 at 14:23 — zebnat
– zebnat, Commented Sep 25, 2018 at 14:23
yes postgresql in node js btw

ampedo
– ampedo

2018-09-25 15:13:57 +00:00
Commented Sep 25, 2018 at 15:13 — ampedo
– ampedo, Commented Sep 25, 2018 at 15:13

zebnat · Accepted Answer · 2018-09-25 15:24:10Z

1

My domain is mysql but i have found this info.

Postgresql GROUP_CONCAT equivalent?

You could try:

SELECT posts.id, posts.content, array_agg(posts_images.img_name)
FROM posts_images 
INNER JOIN posts on posts_images.post_id = posts.id
GROUP BY posts_images.post_id

not sure if aliases work on array_agg but you could try

array_agg(img_name) AS imgs

answered Sep 25, 2018 at 15:24

zebnat

5213 silver badges14 bronze badges

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

Collectives™ on Stack Overflow

Query join two table but another table need to be in another inside array

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related