Question 89811 in PostgreSQL Question posted in
The official documentation can be found here.

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

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?

Tags:

2

Answers


  1. 0

    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;
    Login or Signup to reply.
  2. 0

    Using a simplified sample data this query provides the first step of the aggregation

    with tab as (
select * from (values
 (1,'a','x'),
 (1,'b','y'),
 (2,'c','z')
) tab(entity_number,property_id,value)
) 
select 
   entity_number,
   json_agg( json_build_object('property_id', property_id, 'value', value)) entity_properties 
from tab
group by 1
;

entity_number|entity_properties                                                           |
-------------+----------------------------------------------------------------------------+
            1|[{"property_id" : "a", "value" : "x"}, {"property_id" : "b", "value" : "y"}]|
            2|[{"property_id" : "c", "value" : "z"}]

    Additional aggregation returns the final json array

    with tab as (
select * from (values
 (1,'a','x'),
 (1,'b','y'),
 (2,'c','z')
) tab(entity_number,property_id,value)
),
tab2 as (
select 
   entity_number,
   json_agg( json_build_object('property_id', property_id, 'value', value)) entity_properties 
from tab
group by 1
)
select 
 json_agg(
  json_build_object(
   'entity_number',
   entity_number,
   'entity_properties',
   entity_properties
  )
 ) 
from tab2

 [
    {
        "entity_number": 1,
        "entity_properties": [
            {
                "value": "x",
                "property_id": "a"
            },
            {
                "value": "y",
                "property_id": "b"
            }
        ]
    },
    {
        "entity_number": 2,
        "entity_properties": [
            {
                "value": "z",
                "property_id": "c"
            }
        ]
    }
]

    Note that I used jsonb_pretty to format the output.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search