sql - How to create a json array in psql containing data which was aggregated using array_agg - Stack Overflow

admin管理员组

文章数量:1122846

Using postgres psql I want to create a json array of reports and the associated comments with the report.

There are two tables 1) REPORT that has a one to many relation on 2) REPORT_COMMENT using key report_id=report.id_code.

This code gives me all the data in the json format I want (including the comments in an array of comments as part of the json variable report) EXCEPT that the sets of reports with comments are not yet in an array.

SELECT row_to_json(i)
 FROM (
    SELECT description, id_code,
    (    
        SELECT array_to_json(array_agg(row_to_json(c)))

            FROM (
                SELECT report_id, comment, author 
                FROM report_comment
                WHERE report_id=report.id_code
                ) c
    ) AS comments
    FROM report
 ) i;

I was considering to change line 1 of the sql to this:

    SELECT array_to_json(array_agg(row_to_json(i))

This query returned empty I assumed I should have been using

    SELECT json_agg(row_to_json(i))

however this also returned an empty query, I asked ChatGTP but it suggested the last answer and the debugging it suggested is a dead end. I feel I am missing something small here but what...

Using postgres psql I want to create a json array of reports and the associated comments with the report.

There are two tables 1) REPORT that has a one to many relation on 2) REPORT_COMMENT using key report_id=report.id_code.

This code gives me all the data in the json format I want (including the comments in an array of comments as part of the json variable report) EXCEPT that the sets of reports with comments are not yet in an array.

SELECT row_to_json(i)
 FROM (
    SELECT description, id_code,
    (    
        SELECT array_to_json(array_agg(row_to_json(c)))

            FROM (
                SELECT report_id, comment, author 
                FROM report_comment
                WHERE report_id=report.id_code
                ) c
    ) AS comments
    FROM report
 ) i;

I was considering to change line 1 of the sql to this:

    SELECT array_to_json(array_agg(row_to_json(i))

This query returned empty I assumed I should have been using

    SELECT json_agg(row_to_json(i))

however this also returned an empty query, I asked ChatGTP but it suggested the last answer and the debugging it suggested is a dead end. I feel I am missing something small here but what...

• sql
• arrays
• json
• postgresql
• psql

Share Improve this question Follow asked Nov 21, 2024 at 15:07 KlodderKlodder 13011 silver badge99 bronze badges 1

• Do you have example data and expected output? – keithwalsh Commented Nov 21, 2024 at 15:42

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

After rereading the documentation of json_agg I realise it does not only create a json array but also transforms the row data in json so row_to_json is not needed and results in the empty query as it was probably expecting a row and got json so the correct line is:

     SELECT json_agg(i)

however is psql in the terminal this results in + signs added according to ChatGTP that is due to long strings and can be prevented with pset:

   \pset format unaligned
   SELECT json_agg(i)

this solved my issue

本文标签： sqlHow to create a json array in psql containing data which was aggregated using arrayaggStack Overflow