From: | Jong-won Choi <jongwon(at)ticketsquad(dot)com> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | array_to_json - dealing with returning no rows |
Date: | 2017-03-01 01:31:12 |
Message-ID: | 9fcd1fa6-b0c4-a060-5d41-b952d213a93f@ticketsquad.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Jong-won Choi | 2017-03-01 02:06:28 | Re: array_to_json - dealing with returning no rows |
Previous Message | Adrian Klaver | 2017-03-01 00:15:49 | Re: Shared WAL archive between master and standby: WALs not always identical |