Using json_build_object

Question

I'm trying to use json_build_object to return JSON formatted SQL response, using the following query:

SELECT json_build_object(
        'id', p.id,
        'description', p.description,
        'discounted_price', p.discounted_price,
        'items', jsonb_agg((i.id, i.price, i.title))
        )
FROM promotion_stores AS ps
                            INNER JOIN promotions AS p ON p.id = ps.promotion_id
                            INNER JOIN promotion_items AS pi ON p.id = pi.promotion_id
                            INNER JOIN items AS i ON pi.item_code = i.item_code
WHERE ps.site_id = ${site}
                     and ps.external_store_id = ${branch}
GROUP BY p.id
LIMIT ${limit}`;

The issue is, that the result look like this:

Not sure what are those f1/f2/f3 fields I see
The object are wrapped with json_build_object key - I don't need it

Any idea how I can fix it? The ideal response should look like this:

promotions: [{id: 1, description: "some desc", items: [] }, { id: 2... }

select jsonb_agg(('one', 'two')); [{"f1": "one", "f2": "two"}], which uses to_jsonb from here JSON Operators. So it takes the row and turns it into a object in an array where f1,f2,f3 are keys created by the function. — Adrian Klaver
– Adrian Klaver, Commented Feb 21, 2022 at 18:54
The json_build_object you see is the 'column' name not the key. I believe your client's presentation is confusing the issue. If you want to change the column name do json_build_object(...) AS some_alias. Try running the query in psql I believe you will see a better picture of what is going on. — Adrian Klaver
– Adrian Klaver, Commented Feb 21, 2022 at 19:04

Adrian Klaver · Accepted Answer · 2022-02-21 19:19:40Z

2

A rough draft using some dummy data:

select 
    json_build_object('promotions', jsonb_build_object('one', 1, 'two', 2), 'items', ARRAY[1, 2]) AS json_test;

json_test                        
--------------------------------------------------------
 {"promotions" : {"one": 1, "two": 2}, "items" : [1,2]}

This should serve as starting point.

answered Feb 21, 2022 at 19:19

Adrian Klaver

20.3k3 gold badges24 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using json_build_object

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