How to get an associative array of rows from a subquery with postgres

Question

I'm new to postgres and trying out some things before I take the leap over from mySQL.

What I'm trying to do is get an array of associative arrays into a single query. It has to do with users that can select multiple contact types like phone, email and Facebook and I would like to retrieve those into the column 'contact'.

For a visualisation:

{
"first_name": "This",
"last_name": "is me",
"likes": [],
"city": null

}

And I would like to get something like this:

{
"first_name": "This",
"last_name": "Is me",
"likes": [],
"city": null,
"contact": 
    [
      {"type":1, "value":"[email protected]", "privacy_rule":1},
      {"type":4, "value":"myfacebook", "privacy_rule":1},
      {"type":9, "value":"mylinkedin", "privacy_rule":1}
    ]
}

So the main query would be:

SELECT u.first_name, u.last_name, u.about, ARRAY(SELECT like_id FROM users_likes l WHERE l.user_id = u.user_id), u.city FROM users u WHERE user_id = {id}

The subquery would be:

SELECT c.type, c.value, c.privacy_rule FROM users_contact c WHERE c.user_id = u.user_id

But how do I integrate it in the main query to return the array of result rows? Is it even possible?

Thanks in advance!

Ron

Ron · Accepted Answer · 2015-11-18 23:49:07Z

5

Ah, after some more filling about, here is the answer. use json_build_object:

SELECT u.first_name, u.last_name, 
ARRAY(SELECT like_id FROM users_likes l WHERE l.user_id = u.user_id) as likes, 
ARRAY(SELECT json_build_object("contact_id", c.contact_id, 
"value", c.value, "privacy",c.privacy) 
FROM users_contact c WHERE c.user_id = u.user_id) as contact 
FROM users_basic u WHERE user_id = {id}

This gives:

"first_name": "This",
"last_name": "Is Me",
"about": null,
"likes": [],
"city": null,
"contact": [
  {
    "contact_id": 1,
    "value": "bbla",
    "privacy": 2,
    "type": "Phone"
  },
  {
    "contact_id": 3,
    "value": "blabla",
    "privacy": 2,
    "type": "Company Email"
  },
  {
    "contact_id": 4,
    "value": "blablabla",
    "privacy": 2,
    "type": "Telegram Id"
  }
]

Hope it helps someone

answered Nov 18, 2015 at 23:49

Ron

1,04713 silver badges19 bronze badges

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

Collectives™ on Stack Overflow

How to get an associative array of rows from a subquery with postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related