2

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

1 Answer 1

2

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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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