Re: 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
>