PostgreSQL Query output to JSON

Question

I would like to get the output of a particular Query from the postgresql server having joins of a multiple Tables with one to many relations in the json format to a particular file.

The Query output should be an actual json tree like below and not a flat join of master + child :-

Master Record 1 (master field 1, master field 2)
 { array
 Child Record 1 {child field 1, child field 2, ....)
 Child Record 2 { child field 1, child field 2, ....)
 }
Master Record 2 (....)
 Child Record 1
 Child Record 2

Please let me know how to achieve this in the Postgresql 11.

SQL Script

CREATE TABLE public.book
(
 pk_book_id integer NOT NULL,
 name character varying(255) COLLATE pg_catalog."default",
 isbn character varying(255) COLLATE pg_catalog."default",
 CONSTRAINT book_pkey PRIMARY KEY (pk_book_id)
);
CREATE TABLE public.author
(
 pk_author_id integer NOT NULL,
 fullname character varying(255) COLLATE pg_catalog."default",
 mobileno character(10) COLLATE pg_catalog."default",
 fk_book_id integer,
 CONSTRAINT author_pkey PRIMARY KEY (pk_author_id),
 CONSTRAINT author_fk_book_id_fkey FOREIGN KEY (fk_book_id)
 REFERENCES public.book (pk_book_id) MATCH SIMPLE
 ON UPDATE NO ACTION
 ON DELETE CASCADE
);
INSERT INTO public.author(
 pk_author_id, fullname, mobileno, fk_book_id),
 (100, 'John Matthew', '98927828', 1),
 (101, 'Mark Knight', '99875528', 1),
 (103, 'Lara Croft', '99872628', 2),
 (104, 'Brad Show', '567982', 2)
 
INSERT INTO public.book(
 pk_book_id, name, isbn)
 VALUES (1, 'Hot Deals', 'ISBN89644'),
 (2, 'Summer Tales', 'ISBN405987');
 

• Vérace: You might find this of interest?

Answer 1

It's a bit hard to follow your sample, but something along the lines should do it:

select to_jsonb(main)||ch.data
from main_table main 
 join (
 select main_id, jsonb_agg(to_jsonb(child)) as data
 from child_table child
 group by main_id
 ) ch on ch.main_id = main.id

If you want everything as one gigantic JSON array, use select jsonb_agg(to_jsonb(main)||ch.data) in the outer select.

• Jeetendra Pardeshi: Done the changes and included the Create Table and Insert Into Scripts with dummy data
• user1822: Do you actually want the strings Book Record 1 in the output? If not, I don't see why this query doesn't do what you want: dbfiddle.uk/…
• Jeetendra Pardeshi: Thanks for the reply ...but can we also export it to a file through the same query
• user1822: That depends on your SQL client. With psql you can use copy (select ...) to 'somefile.json' or use the \o somefile.json metacommand to spool any output to a file.
• Jeetendra Pardeshi: i m using the command from the pgsql prompt but its not generating any file <br/> COPY (select to_jsonb(b), authors from book b join (select fk_book_id, jsonb_agg(to_jsonb(a)) authors from author a group by fk_book_id) a on a.fk_book_id = b.pk_book_id) TO 'C:\Users\Public\Documents\temp12.json'