PostgreSQL JSON - String_agg in json data with multiple objects

Question

I have create table with Json datatype field in PostgreSQL.

Also i have inserted data with multiple object in json data

like this

[
    {
        "Type": "1",
        "Amount": "1000",
        "Occurrence": 2,
        "StartDate": "1990-01-19",
        "EndDate": "1999-04-03"
    },
    {
        "Type": "2",
        "Amount": "2000",
        "Occurrence": 2,
        "StartDate": "1984-11-19",
        "EndDate": "1997-09-29"
    }
]

Now i have to retrieve my data as per below formate in single row like string_agg() function output.

Type    Amount
1--2    1000-2000

also i have checked inbuilt function for json in PostgreSQL (https://www.postgresqltutorial.com/postgresql-json/) but not find any solutions for the same.

Which Postgres version are you using?

user330315
– user330315

2021-04-07 05:55:36 +00:00
Commented Apr 7, 2021 at 5:55 — user330315
– user330315, Commented Apr 7, 2021 at 5:55
i am using Postgres 10.4

Ravi Oza
– Ravi Oza

2021-04-07 05:58:21 +00:00
Commented Apr 7, 2021 at 5:58 — Ravi Oza
– Ravi Oza, Commented Apr 7, 2021 at 5:58

user330315 · Accepted Answer · 2021-04-07 06:10:44Z

4

You will have to unnest the array and aggregate the individual keys:

The following assumes you have some kind of primary key column on the table (in addition to your JSON column):

select t.id, 
       string_agg(x.element ->> 'Type', '-') as types,
       string_agg(x.element ->> 'Amount', '-') as amounts
from the_table t
  cross join jsonb_array_elements(t.data) as x(element)
group by t.id;

jsonb_array_elements() extracts each array element and the main query then aggregates that back per ID using string_agg().

The above assumes your column is defined with the type jsonb (which it should be). If it is not, you need to use json_array_elements() instead.

Online example

answered Apr 7, 2021 at 6:10

user330315

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

3 Comments

Ravi Oza Over a year ago

Thanks @a_horse_with_no_name, perfect answer as what i need, Also one quick question should i use json or jsonb for this type of data?

user330315 Over a year ago

Quote from the manual "In general, most applications should prefer to store JSON data as jsonb"

Ravi Oza Over a year ago

@a_horse_with_no_name, what if two json columns in same table, currently its duplicate data with use of cross join, i have updated in code here - dbfiddle.uk/… for this scenario

Collectives™ on Stack Overflow

PostgreSQL JSON - String_agg in json data with multiple objects

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related