0

I am using Postgresql 13.0 and have a table category as follows:

 cat_id | cat_name | piece | weight | is_tag_stock
--------------------------------------------
  4     |   cat1   | 20    | 10     | true
  5     |   cat2   | 30    | 15     | false
  4     |   cat1   | 40    | 5      | false
  4     |   cat1   | 50    | 30     | false

How do I group by in such a way that for one category, the query yields one record, and based on is_tag_stock, it should form JSON? Also, the JSON should contain the summation of the piece and weight field grouped by cat_id and cat_name. If is_tag_stock is true, then the piece and weight should be added to the tagged field else in the untagged field.

NOTE: For one cat_id, there would be only one cat_name. cat_name is included in the table just to avoid join.

Expected Output

 cat_id | cat_name | tagged                      | untagged
------------------------------------------------------------------------------
  4     |   cat1   | {"piece":20,"weight":10}    | {"piece":90,"weight":35}
  5     |   cat2   | {"piece":0,"weight":0}      | {"piece":30,"weight":15}
Improve this question
1
  • Please make no mistake: a {...} structure is not a JSON array, it's a JSON object. An array would be a list of such objects enclosed in square brackets: [{...}, {...}, ...]. – Andriy M Dec 29 '20 at 10:01
3

Use conditional aggregation to get the sums depending on is_tag_stock and jsonb_build_object() (or json_build_object()) to create the encapsulating JSON.

SELECT cat_id,
       cat_name,
       jsonb_build_object('piece',
                          sum(CASE
                                WHEN is_tag_stock THEN
                                  piece
                              ELSE
                                0
                              END)) tagged,
       jsonb_build_object('piece',
                          sum(CASE
                                WHEN NOT is_tag_stock THEN
                                  piece
                                ELSE
                                  0
                             END)) untagged
       FROM elbat
       GROUP BY cat_id,
                cat_name;

db<>fiddle

Improve this answer
7
  • 1
    Or use the more efficient aggregate FILTER Like: sum(piece) FILTER (WHERE is_tag_stock) ... – Erwin Brandstetter Dec 27 '20 at 14:31
  • @stickybit Thanks for answering my question. But I have a requirement for one more field i.e. weight in JSON. I have edited my question. So is there any better way to handle this as then I have to write the same CASE query for weight field as well? – Sunny Dec 28 '20 at 7:40
  • @Sunny: Do not edit questions to invalidate answers. Think what you really need before asking the question. And show some effort. You should be able to figure this out yourself, now that I gave you the basics. – sticky bit Dec 28 '20 at 7:48
  • @stickybit Yes. I know how to that for the weight field. But in that case, I have to write the same CASE query two times. one for the piece and the other for weight. That's why I was asking if there is some other way! – Sunny Dec 28 '20 at 8:35
  • 1
    @Sunny: Note that if you generated the aggregated results as regular columns rather than enveloping them in a JSON object, i.e. SUM(...) AS price, SUM(...) AS weight, you would have to repeat each conditional as well. I don't believe there's a simple way to go around that. At the very least, the problem would have nothing to do with JSON. The question would be simply how to avoid repetition of a conditional when aggregating multiple columns using the same conditional. Sounds like a legitimate question but it should be submitted separately from this one. – Andriy M Dec 29 '20 at 9:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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