How to conditionally create JSON array in postgres with group by clause? 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   | {&quot;piece&quot;:20,&quot;weight&quot;:10}    | {&quot;piece&quot;:90,&quot;weight&quot;:35}
  5     |   cat2   | {&quot;piece&quot;:0,&quot;weight&quot;:0}      | {&quot;piece&quot;:30,&quot;weight&quot;:15}
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&lt;&gt;fiddle