SQL - case with common logic for multiple conditions, nested when-CodePudding

I have a query where 2 of my cases share the most of the logic for their conditions for a jsonb column. The column can be null, empty text, or an empty json object. I would like to check if field is neither of these and then check which property does jsonb have to check the value in it. So, in this case for the column called data, I can have this values:

   |  data
_________
1. | null
2. | ''
3. | {}
4. | {"deliveryDate": "2022-06-11"}
5. | {"deliveryTimeLatest": {"to": "2022-06-08T23:59", "from": "2022-06-08T23:59"}, "deliveryTimeEarliest": {"to": "2022-06-08T15:00", "from": "2022-06-08T12:00"}}

How can I write a case where I check if the column is not empty, or null or an empty json object and then check if it has either deliveryDate or deliveryTimeEarliest -> to?

So, something like this:

case
  when type = '${EventType.TIME_ORDERED.code}' and
       data is not null and
       data <> '{}'
  then 
     when data -> 'deliveryDate' <> ''
     else data -> 'deliveryTimeEarliest' ->> 'to'
end as changed_delivery_date,

If I try to do that I get:

org.postgresql.util.PSQLException: ERROR: syntax error at or near "when"

I also tried writing it like this:

case
      when type = '${EventType.TIME_ORDERED.code}' and data is not null and data <> '{}'
          then
            case
              when data -> 'deliveryDate' <> ''
              then data -> 'deliveryDate'
              else data -> 'deliveryTimeEarliest' ->> 'to'
    end as changed_delivery_date

But, that also throws a syntax error:

org.postgresql.util.PSQLException: ERROR: syntax error at or near "as"

How can I achieve such logic?

CodePudding user response：

case
      when type = '${EventType.TIME_ORDERED.code}' and data is not null and data <> '{}'
          then
            case
              when data -> 'deliveryDate' <> ''
              then data -> 'deliveryDate'
              else data -> 'deliveryTimeEarliest' ->> 'to'
            end #end of second case
   end #end of first case
 as changed_delivery_date

Maybe before second end you should add else clausule for first case, but it depends on the business logic. Still case with only one when is strange syntax

CodePudding user response：

It looks like your field is either not jsonb, or you have typo in your jsonb samples?

Probably you mean this:

case
  when data is not null and
       data <> '{}'::jsonb
  then 
     case when data ->> 'deliveryDate' is not null then data ->> 'deliveryDate'
     else data -> 'deliveryTimeEarliest' ->> 'to' end
end as changed_delivery_date