Re: [GENERAL] array_to_json - dealing with returning no rows

I've just found array_remove!

Cheers

- Jong-won

On 01/03/17 12:31, Jong-won Choi wrote:
> Hi all,
>
>
> In my program, I generate SQLs from definitions, an example is:
>
> (define-db-resource Event
>   [{:oid            {:type :bigserial :primary-key true}}
>    {:name        {:type :text :not-null true}}
>    {:tour-oid     {:type :bigint :not-null true :references [Tour :oid]}}
>    {:tour           {:type :join :join-info {:home-key :tour-oid
> :foreign-key :oid :join-resource Tour :foreign-columns [:oid :name]
>                           :singular? true}}}
>    {:campaigns {:type :join :join-info {:home-key :oid :foreign-key
> :event-oid :join-resource Campaign
> :foreign-columns [:oid :type :name]}}}])
>
>
> From definitions my code generate various SQLs and this is a 'select'
> example for the above definition:
>
> SELECT event.oid,event.name,
>              ROW_TO_JSON((SELECT sj_tour FROM (SELECT j_tour.oid,
> j_tour.name) sj_tour)) AS tour,
>              ARRAY_TO_JSON(ARRAY_AGG((SELECT sj_campaigns FROM (SELECT
> j_campaign.oid,j_campaign.name,j_campaign.type WHERE j_campaign.oid IS
> NOT NULL) sj_campaigns)) AS campaigns
> FROM event LEFT OUTER JOIN tour AS j_tour ON tour_oid = j_tour.oid
>                     LEFT OUTER JOIN campaign AS j_campaign ON
> event.oid = j_campaign.event_oid
> GROUP BY event.oid, j_tour.oid;
>
>
> The problem I have is getting '[null]' as ARRAY_TO_JSON result when
> there is no rows.
>
> Ideally, I want to get '[]' or null for no rows or '[{...some JSON
> keys and values ...},{ ... more ... }]' for some rows.
>
>
> Also any suggestions will be great with above example query.
>
>
> Thanks!
>
>
> - Jong-won
>