[GENERAL] array_to_json - dealing with returning no rows

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