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}
Sunny
  • 135
  • 1
  • 8
  • 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

1 Answers1

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

sticky bit
  • 4,834
  • 2
  • 13
  • 19
  • 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 07: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 07: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 08: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 09:58
  • In the meantime, you can make your life a little easier by following the suggestion by @ErwinBrandstetter above. Not only is the FILTER syntax he suggested makes for more efficient aggregation, but it's also slightly more compact than the CASE (in this case). – Andriy M Dec 29 '20 at 09:59
  • @AndriyM Thanks for the suggestions. – Sunny Dec 30 '20 at 01:44