Re: coalesce(json_agg [..] filter where [..], '[]' in left join returning null

Поиск
Список
Период
Сортировка
От Stefan Houtzager
Тема Re: coalesce(json_agg [..] filter where [..], '[]' in left join returning null
Дата
Msg-id CANJtTDkecQ0h1rYrSQc_g=2E6mdAdC8citm-CLA8z4_yhmgvjw@mail.gmail.com
обсуждение исходный текст
Ответ на Re: coalesce(json_agg [..] filter where [..], '[]' in left join returning null  ("David G. Johnston" <david.g.johnston@gmail.com>)
Список pgsql-novice
Дерево обсуждения
On Wed, Jan 6, 2021 at 5:38 PM David G. Johnston <david.g.johnston@gmail.com> wrote:

Please don't top-post here.

The general concept should work.  You may at least wish to experiment in a simple query to get the feel for the mechanics.  With respect to this query I'd probably need to have a working query to play with (and time) to give a more precise answer.

David J.

Thanks David,

I do not get what you suggest working within the left-join. What does work is the following:

select json_agg(_)
from (
    select
        vc.id,
   vc.descr,
   vc.expense,
   vc.version,
        (select coalesce(json_agg(_), '[]')
         from (
             select
                 vp.percentage,
    vp.version,  
    json_build_object(
                     'lower', lower(vp.validity),
                     'upper', upper(vp.validity),
                     'lower_inc', lower_inc(vp.validity),
                     'upper_inc', upper_inc(vp.validity)
                  ) validity
                  from vat_percentage vp
                  where vp.vatcat_id = vc.id
                ) _
         ) as percentages
       from vat_cat vc
    group by vc.id
) _

--
Kind regards,

Stefan Houtzager

Houtzager ICT consultancy & development

www.linkedin.com/in/stefanhoutzager

В списке pgsql-novice по дате отправления:

Предыдущее
От: "David G. Johnston"
Дата:
Сообщение: Re: coalesce(json_agg [..] filter where [..], '[]' in left join returning null
Следующее
От: Santosh Udupi
Дата:
Сообщение: Postgres 12 - Generated Columns - Backup/Restore