current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

If I have a query like

SELECT a.id, a.name, json_agg(b.*) as "item"
  FROM a
  JOIN b ON b.item_id = a.id
 GROUP BY a.id, a.name;

How can I select the columns in b so I don't have b.item_id in the JSON object?

Edit

I'm reading about ROW, but it returns a JSON object like {"f1": "Foo", "f2": "Bar"}.

share|improve this question
    
What's wrong with {"f1": "Foo", "f2": "Bar"}? Isn't that what you've been after? –  Erwin Brandstetter Jul 3 at 15:10
    
I would need to remap the JSON object once it is fetched to match the proper column keys. I'd like to avoid that. –  Yanick Rochon Jul 3 at 15:13

2 Answers 2

up vote 0 down vote

Unfortunately, there is no provision in SQL syntax to say "all columns except this one column".

You can achieve your goal by spelling out the remaining list of columns in row type syntax like this:

SELECT a.id, a.name, json_agg((b.col1, b.col2, b.col3)) as "item"
FROM   a
JOIN   b ON b.item_id = a.id
GROUP  BY a.id, a.name;
share|improve this answer
    
In postgresql 9.3, this does not work. I tried with two columns from b and I get : "function json_agg(character varying, character varying) does not exist" –  Yanick Rochon Jul 3 at 15:12
    
@YanickRochon: Closing parenthesis was missing, sorry. It does work. I tested on pg 9.3 before posting. –  Erwin Brandstetter Jul 3 at 15:22
    
It gives the same result as json_agg(ROW(b.col1, b.col2)), where it results in {"f1": "Foo", "f2": "Bar"}. If I need to remap the JSON object afterwards, then I'll just use json_agg(b.*) and be done with it... neither solution is what I'm looking for, sorry. –  Yanick Rochon Jul 3 at 15:30
    
@Y: ROW is just a noise word. The parentheses alone do the same job. This answers the question as it was asked. If you have another question I suggest you start a new question. –  Erwin Brandstetter Jul 3 at 16:03
    
@Enwin this answers the question as someone who knows the limitations of PGSQL. I don't. And since the query provided returns the proper column names inside the JSON object, having a different resultset is significant enough to not constitute as a valid answer, in my opinion. –  Yanick Rochon Jul 3 at 16:35
up vote 1 down vote

I have found that it's best to create the JSON, then aggregate it. e.g.

with base as (
select a, b, ('{"ecks":"' || x || '","wai":"' || y || '","zee":"' || z || '"}"')::json c
) select (a, b, array_to_json(array_agg(c)) as c)

Note this can be done as a subquery if you don't like CTEs

share|improve this answer
    
Note also, if you're going to be doing this a lot, it may be beneficial to create a function to wrap the key-value pairs for you so the code looks cleaner. You would pass your function (for example) "ecks", x and it would return "ecks":"x" –  M.Mugge Aug 7 at 17:16

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.