From: | Laura Smith <n5d9xq3ti233xiyif2vp(at)protonmail(dot)ch> |
---|---|
To: | Erik Wienhold <ewie(at)ewie(dot)name> |
Cc: | postgre <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: array_to_json/array_agg question |
Date: | 2024-02-20 13:03:07 |
Message-ID: | JiF0sV__vWii0hEuyojbWLXfM98CyFL6cri9wJ0kwxg3LTPxWjCxHlmcC-sYsmBhpEvMF1I_u9GDIx9w-4q1vH481jDrORz2gSt0_Hp77dM=@protonmail.ch |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
> You almost got the subrecord ("value_1" and "value_2") right. You need
> to use json_build_object() (or even the new json_object() function added
> in pg16) instead of row_to_json() to just include "value_1" and
> "value_2". Then GROUP BY "key" and aggregate the subrecords with
> json_agg(). Then build the top-level record ("key" and "values") with
> json_build_object(). And finally one more aggregation with json_agg()
> to get a single array.
>
Interesting ideas, thanks Erik.
Subsequent to my original posting, but prior to your reply and based on an off-list idea from someone else, I came up with the following adaptation:
SELECT json_agg(q) INTO v_res FROM (SELECT array_to_json(array_agg(row_to_json(p))) AS q
FROM (SELECT * FROM test_a)p group by key)s;
But maybe I should be considering json_build_object() instead, or maybe json_object() (although I'm currently on 14.5, so it would require an upgrade to 16 first, which is possible as a longer-term option, but right now I'm developing against a 14.5 backend).
From | Date | Subject | |
---|---|---|---|
Next Message | Patrick FICHE | 2024-02-20 13:10:01 | RE: array_to_json/array_agg question |
Previous Message | Zahir Lalani | 2024-02-20 12:20:10 | RE: Mat Views and Conflicts |