Postgres Json List of Map-CodePudding

I have one sample json as below

{
    "jsonObject":
        [ 
            { "Name" : "XPerson",
                "Age"  : 18},
            { "Name" : "YPerson",
                "Age"  : 18}
        ]
}

I can have this list to N numbers. I want to separate this in different column based on Age like less than 18 in column1, between 18 to 25 in column2 and all other in column3.

How can we achieve in postgres?

CodePudding user response：

It sounds like you are trying to do front end's job at database level.

with data(Age,Name) as
         (select *
          from jsonb_to_recordset(' {
            "jsonObject": [
              {
                "Name": "XPerson",
                "Age": 18
              },
              {
                "Name": "YPerson",
                "Age": 18
              }
            ]
          }'::jsonb -> 'jsonObject') as t("Age" int, "Name" text))
select
        string_agg(case when  age < 18 then name end,',') as Column1,
        string_agg(case when  age >= 18 and age <=25 then name end,',') as Column2,
        string_agg(case when  age > 25 then name end,',') as Column3
from data;