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?

The ARRAY_AGG is not the cause of the escaping. You make some additional processing to build the json that introduces the escaping. As the last rescue - simple use replace. — Marmite Bomber
– Marmite Bomber, Commented Feb 9, 2023 at 8:56

ABDULLOKH MUKHAMMADJONOV · Accepted Answer · 2023-02-09 14:39:43Z

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;

edited Feb 9, 2023 at 14:39

ABDULLOKH MUKHAMMADJONOV

5,3946 gold badges28 silver badges51 bronze badges

answered Feb 9, 2023 at 6:36

Ketan Suthar

3955 silver badges14 bronze badges

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

1 Comment

Ketan Suthar Over a year ago

yes, sorry for the typo, double quotes are for case-sensitive names. your edit is already updated.

Marmite Bomber · Accepted Answer · 2023-02-09 08:53:32Z

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.

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related