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');

You might find this of interest? – Vérace Sep 02 '20 at 01:29 — Vérace, Sep 02 '20 at 01:29

score 2 · Answer 1 · answered Sep 01 '20 at 14:16

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.

answered Sep 01 '20 at 14:16

Done the changes and included the Create Table and Insert Into Scripts with dummy data – Jeetendra Pardeshi Sep 02 '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: https://dbfiddle.uk/?rdbms=postgres_11&fiddle=3ce1af8ffebec15d6b86303b5ae9d12a – Sep 02 '20 at 13:18
Thanks for the reply ...but can we also export it to a file through the same query – Jeetendra Pardeshi Sep 02 '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. – Sep 02 '20 at 14:46
i m using the command from the pgsql prompt but its not generating any file
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 02 '20 at 17:15

PostgreSQL Query output to JSON

1 Answers1