How to return a single JSON object from multiple tables with PostgreSQL

Question

With the following snippet I am building a valid JSON string:

(SELECT CONCAT('{', array_to_string(array_agg(info),','), '}')
 FROM (
          SELECT CONCAT('"', "displayOrder", '":', 
                        CONCAT('{"milestoneID":', 
                               "milestoneID"::TEXT, 
                               ',"msValue":"',  
                               "msValue", 
                               '","msColor":"', 
                               "msColor", '"}'
                               )
                        ) AS info
          FROM "fileMilestones"
              LEFT JOIN milestones ON "fileMilestones"."milestoneID" = milestones.id
          WHERE "fileMilestones"."fileNumber" = wc_files."fileNumber"
      ) AS msa
) AS ms,

However, it is just a string and is not seen as a JSON object. I am sure I am doing this wrong...

Here is the table structure:

CREATE TABLE public."fileMilestones"
(
  id integer NOT NULL DEFAULT nextval('"fileMilestones_id_seq"'::regclass),
  "fileNumber" integer,
  "milestoneID" smallint,
  "msValue" text,
  "msColor" text,
  CONSTRAINT "fileMilestones-id.constraint" PRIMARY KEY (id),
  CONSTRAINT "fileMilestones-fileNumber-milestoneID.constraint" UNIQUE ("fileNumber", "milestoneID")
)

CREATE TABLE public.milestones
(
  id integer NOT NULL DEFAULT nextval('milestones_id_seq'::regclass),
  "displayOrder" smallint,
  name citext,
  days smallint,
  start smallint,
  "calendarDays" smallint,
  description citext,
  "moduleID" integer,
  CONSTRAINT "milestones-id.constraint" PRIMARY KEY (id),
  CONSTRAINT "milestones-name.constraint" UNIQUE (name)
)

This is what the returned JSON needs to look like:

{"10":{"milestoneID":1, "msValue":"", "msColor":"milestoneColorGreen"}, 
 "20":{"milestoneID":2, "msValue":"", "msColor":"milestoneColorGreen"},
 "30":{"milestoneID":3, "msValue":"", "msColor":"milestoneColorGreen"},
 "40":{"milestoneID":4, "msValue":"", "msColor":"milestoneColorGreen"},
 "50":{"milestoneID":10, "msValue":"", "msColor":"milestoneColorGreen"},
 "60":{"milestoneID":6, "msValue":"", "msColor":"milestoneColorGreen"},
 "70":{"milestoneID":7, "msValue":"", "msColor":"milestoneColorGreen"},
 "80":{"milestoneID":8, "msValue":"76", "msColor":""},
 "90":{"milestoneID":9, "msValue":"", "msColor":"milestoneColorGreen"}}

How can I build a JSON object and return it as JSON?

I think you might need one of these functions <postgresql.org/docs/9.2/static/functions-json.html> array_to_json or row_to_json — atilacamurca
– atilacamurca, Commented Nov 12, 2016 at 21:37
@atilacamurca row_to_json() only works on a single row. Since I am returning related data it does not work correctly (or at least I cannot figure out how). I could not get array_to_json() to create valid output either. I think the problem is I am using data as the first key rather than a field name. — Tim Duncklee
– Tim Duncklee, Commented Nov 12, 2016 at 22:01

Patrick · Accepted Answer · 2016-11-13 04:07:57Z

2

The simplest solution would be to cast the final result to JSON using ::json, but you really want to use the built-in JSON support for these kinds of things. Anything above the most trivial JSON structure will quickly become very tedious to specify in code, prone to error and a nightmare to maintain.

But to answer your question (at the bottom of your post), you can "build a JSON object" with... json_build_object(). You can aggregate key/value pairs with the json_object_agg() function. Both functions also have a jsonb variant.

SELECT json_object_agg("displayOrder"::text, 
                       json_build_object('milestoneID', "milestoneID"::text, 
                                         'msValue', "msValue", 
                                         'msColor', "msColor")) AS info
FROM "fileMilestones"
LEFT JOIN milestones ON "fileMilestones"."milestoneID" = milestones.id
WHERE "fileMilestones"."fileNumber" = wc_files."fileNumber" -- wc_files???
...

answered Nov 13, 2016 at 4:07

Patrick

33k7 gold badges73 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tim Duncklee Over a year ago

This is exactly what I needed! Thanks. :)

Collectives™ on Stack Overflow

How to return a single JSON object from multiple tables with PostgreSQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related