how can I make result of ARRAY_AGG() function json parsable

Question

I have a query that selects the rows from joined table as an array using ARRAY_AGG() function.

select 
    entity_number, 
    ARRAY_AGG('{"property_id":"'||property_id||'","value":"'||value||'"}') entity_properties from entities
join entity_properties
on entities.id = entity_properties.entity_id
where entities.id in (
    select entity_id from entity_properties 
    where value = '6258006d824a25dabdb39a79.pdf'
)
group by entities.id;

what I get is:

[
    {
        "entity_number":"P1718238009-1",
        "entity_properties":"[
            \"{\"property_id\":\"006109cd-a100-437c-a683-f13413b448e6\",\"value\":\"Rozilik berildi\"}\",
            \"{\"property_id\":\"010f5e23-d66f-4414-b54b-9647afc6762b\",\"value\":\"6258006d824a25dabdb39a79.pdf\"}\",
            \"{\"property_id\":\"0a01904e-1ca0-40ef-bbe1-c90eaddea3fc\",\"value\":\"6260c9e9b06e4c2cc492c470_2634467.pdf\"}\"
        ]"
    }
]

As you can see, it is not json parsable

To parse entity_properties as array of objects I need the data in this format

[
    {
        "entity_number":"P1718238009-1",
        "entity_properties":[
            {"property_id":"006109cd-a100-437c-a683-f13413b448e6","value":"Rozilik berildi"},
            {"property_id":"010f5e23-d66f-4414-b54b-9647afc6762b","value":"6258006d824a25dabdb39a79.pdf"},
            {"property_id":"0a01904e-1ca0-40ef-bbe1-c90eaddea3fc","value":"6260c9e9b06e4c2cc492c470_2634467.pdf"}
        ]
    }
]

Can I achieve what I want with ARRAY_AGG()? How?

If not, what approach should I take?

Answer 1

Try using json_agg and json_build_object function

like this:

select 
    entity_number, 
    json_agg(json_build_object('property_id', property_id, 'value', value)) entity_properties from entities
join entity_properties
on entities.id = entity_properties.entity_id
where entities.id in (
    select entity_id from entity_properties 
    where value = '6258006d824a25dabdb39a79.pdf'
)
group by entities.id;