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

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');
  • You might find this of interest? – Vérace Sep 2 '20 at 1:29

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.

  • Done the changes and included the Create Table and Insert Into Scripts with dummy data – Jeetendra Pardeshi Sep 2 '20 at 13:08
  • 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/… – a_horse_with_no_name Sep 2 '20 at 13:18
  • Thanks for the reply ...but can we also export it to a file through the same query – Jeetendra Pardeshi Sep 2 '20 at 14:45
  • 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. – a_horse_with_no_name Sep 2 '20 at 14:46
  • 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'; – Jeetendra Pardeshi Sep 2 '20 at 17:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.