Postgres - Query one to many relationship

Question

I'm trying to make a query to get a report and all of the report_items associated with the report.

reports table structure: id | name | creation_date

report_items table structure: id | report_id | place | producer | serial_number

I tried this:

SELECT "reports".*,
          to_json("report_items".*) as "items"
          FROM "reports" INNER JOIN "report_items" USING ("id")
          WHERE "reports".id = ${req.params.id}

but only the first report_item returns (instead of a list of report_items):

{"id":1,"type":"fd","name":"dsfdsfds","client_name":"fdsfds","website":"dsffds","creation_time":"2019-03-12T22:00:00.000Z","items":{"id":1,"report_id":1,"place":"saddsa","type":"sdadsa","producer":"sdadsa","serial_number":"adsdsa","next_check_date":"2019-03-19","test_result":"saddsa","comments":"saddsa"}}

Expected result:

{"id":1,"type":"fd","name":"dsfdsfds","client_name":"fdsfds","website":"dsffds","creation_time":"2019-03-12T22:00:00.000Z","items": [{"id":1,"report_id":1,"place":"saddsa","type":"sdadsa","producer":"sdadsa","serial_number":"adsdsa","next_check_date":"2019-03-19","test_result":"saddsa","comments":"saddsa"}, {"id":1,"report_id":1,"place":"saddsa","type":"sdadsa","producer":"sdadsa","serial_number":"adsdsa","next_check_date":"2019-03-19","test_result":"saddsa","comments":"saddsa"}]}

Any idea what I'm missing?

a long solution I can do (pretty sure it's not ideal..):

SELECT * FROM reports
        WHERE id = ${req.params.id}

SELECT * FROM report_items
      WHERE report_id = ${req.params.id}

and combine them programmatically.

Add table definition of reports, report_items, sample data and expected result. — Kaushik Nayak
– Kaushik Nayak, Commented Mar 7, 2019 at 14:38
This line of code is taking your id from the input and only returning that value - WHERE "reports".id = ${req.params.id} — jimmy8ball
– jimmy8ball, Commented Mar 7, 2019 at 14:41
You don't aggregate the items into a single JSON array. For a report with multiple "items" you will get more than one row. I interpret the example output you gave as a single full row after external conversion to JSON. Just try wrapping the to_json into an array aggregation, e.g.: array_to_json(array_agg(to_json(report_items.*))) AS items — Ancoron
– Ancoron, Commented Mar 7, 2019 at 15:25
@KaushikNayak I added the table definitions and expected result. — TheUnreal
– TheUnreal, Commented Mar 7, 2019 at 16:33
@Ancoron Got an error - Error: error: column "reports.id" must appear in the GROUP BY clause or be used in an aggregate function — TheUnreal
– TheUnreal, Commented Mar 7, 2019 at 16:34

Ancoron · Accepted Answer · 2019-03-07 19:00:31Z

If I understand the question properly, the desired result is a single row for the report data including all items as a JSON array.

Under that assumption, a query as follows should work:

WITH data (id, items) AS (
    SELECT report_id, array_to_json(array_agg(to_json(*)))
    FROM report_items WHERE report_id = ${req.params.id}
    GROUP BY report_id
)
SELECT reports.*, data.items
FROM reports
INNER JOIN data ON (reports.id = data.report_id)
WHERE reports.id = ${req.params.id}

...or as a sub-select (because PostgreSQL is not yet smart enough to push down the filter into the CTE:

SELECT reports.*, data.items
FROM reports
INNER JOIN (SELECT report_id, array_to_json(array_agg(to_json(report_items.*)))
    FROM report_items
    GROUP BY report_id) AS data (id, items) ON (reports.id = data.id)
WHERE reports.id = ${req.params.id}

In both cases, the output column items will contain a JSON array of all related items.

If you want the complete report entry as JSON, try this (beware this only works for jsonb, not json):

SELECT jsonb_set(to_jsonb(reports.*), '{items}', data.items, true) AS report_data
FROM reports
INNER JOIN (SELECT report_id, array_to_json(array_agg(to_json(report_items.*)))::jsonb
    FROM report_items
    GROUP BY report_id) AS data (id, items) ON (reports.id = data.id)
WHERE reports.id = ${req.params.id}

Collectives™ on Stack Overflow

Postgres - Query one to many relationship

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related