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

How do concat an array in PostgreSQL with data like this?

I want to concatenate the denominations for all objects items same id:

[
  { id:1, details: { denominations : [ { code: "USD",value :100} ] } },
  { id:1, details: { denominations : [ { code: "USD",value :110} ] } },
  { id:2, details: { denominations : [ { code: "USD",value :100} ] } },
  { id:2, details: { denominations : [ { code: "USD",value :110} ] } }
]

To

[
  { id:1, details: { denominations : [ { code: "USD", value: 100 }, { code: "USD",value :110} ] } },
  { id:2, details: { denominations : [ { code: "USD", value: 100 }, { code: "USD",value :110} ] } }
]
Tags:

2

Answers


  1. 0

    This will do it, but if your json document has more parts that you are not showing, then you will need a different approach:

    with invars as (
  select '[
    { "id":1, "details": { "denominations" : [ { "code": "USD","value" :100} ] } },
    { "id":1, "details": { "denominations" : [ { "code": "USD","value" :110} ] } },
    { "id":2, "details": { "denominations" : [ { "code": "USD","value" :100} ] } },
    { "id":2, "details": { "denominations" : [ { "code": "USD","value" :110} ] } }
  ]'::jsonb as j
)
select jsonb_build_object(
         'id', e->>'id', 
         'details', jsonb_build_object(
                      'denominations', jsonb_agg(d.*)
                    )
       ) as result
  from invars i
       cross join lateral jsonb_array_elements(j) as e(top)
       cross join lateral jsonb_array_elements(e.top->'details'->'denominations') as d(detail)
 group by e->>'id';

    working fiddle

    Login or Signup to reply.
  2. 0

    The best answer depends on exact details.

    To aggregate the first (only?) array element from "denominations" across the whole input, a single unnest will do:

    SELECT jsonb_build_object('id', j.val -> 'id'
                        , 'details', jsonb_agg(j.val #> '{details,denominations,0}'))
FROM   tbl t, jsonb_array_elements(t.js) j(val)
GROUP  BY j.val -> 'id'
ORDER  BY j.val -> 'id';  -- optional

    fiddle

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

Back To Top
Search