combine more than one rows into a single JSON Object based on common column value

Question

I have the following table :

id |   model    |  version   |   status
_________________________________________

.. |  model1    |   1.0      |    old
.. |  model1    |   2.0      |    new
.. |  model2    |   1.1      |    old
.. |  model2    |   1.2      |    new

I need to generate JSON object based on the status for same model(combine two rows into single JSON object) like below :

[
   {
      "model" : "model1",
      "old_version" : "1.0",
      "new_version" : "2.0"
   },
   {
      "model" : "model2",
      "old_version" : "1.1",
      "new_version" : "1.2"
   }
]

What I tried so far :

select json_agg(json_build_object(t."model",
                                  CASE WHEN t."status" == 'new'
                                   THEN 'new_version',t."version",
                                  end 
                                   CASE WHEN t."status" == 'old'
                                   THEN 'old_version',t."version",
                                  end ))
from (// my select query with various conditions goes here) t

I am getting syntax error also. How can I achieve the expected output

Thank you in advance !!

Answer 1

The THEN part of a CASE expression can only contain a single expression, so then value_a, value_b end is not valid. You are also missing an key for the model value.

However, you have a two stage aggregation here: first aggregate the versions per model, then aggregate that result into an array. As aggregate functions can't be nested, you need a derived table for this:

This aggregates the versions for each model:

select jsonb_build_object('model', model)||jsonb_object_agg(status||'_version', version)
from (...)
group by model

If you can't or don't want to use the status name as the prefix for the "version key", you will have to use a CASE expression:

select jsonb_build_object('model', model)|| 
              jsonb_object_agg(case status 
                                 when 'old' then 'old_version' 
                                 when 'new' then 'new_version' 
                               end, 
                               version)
from (...)
group by model

This can then be used in a derived table to get the json array:

select jsonb_agg(model)
from (
  select jsonb_build_object('model', model)||jsonb_object_agg(status||'_version', version) as model
  from (...)
  group by model
) t

Online example