Get only few properties from JSON string in PostgreSQL

Raushan Setthi 0
Viewed 33

I have to select only few properties in PostgreSQL i am looking something like skip_rest_all_properties(value,['age']) where value is

{
   "employee":[
      {
         "name":"Raunak",
         "Age":30,
         "sec":"A"
      },
      {
         "name":"Manoj",
         "Age":35,
         "sec":"N"
      },
      {
         "name":"Naveen",
         "Age":38,
         "sec":"D"
      }
   ]
}

And I want

{"employee":[{"name":"Raunak"},{"name":"Manoj"},{"name":"Naveen"}]}
sql postgresql json
1 Answers

Sample query for you:

with jsontable as (
    select '{
       "employee":[
          {
             "name":"Raunak",
             "Age":30,
             "sec":"A"
          },
          {
             "name":"Manoj",
             "Age":35,
             "sec":"N"
          },
          {
             "name":"Naveen",
             "Age":38,
             "sec":"D"
          }
       ]
    }'::jsonb as jsondata
)
SELECT jsonb_build_object('employee', jsonb_agg(t1.f1)) from 
(
    select jsonb_build_object('name', jsonb_array_elements((jsondata->>'employee')::jsonb)->>'name') as f1 from jsontable
) t1


Result: 
{"employee": [{"name": "Raunak"}, {"name": "Manoj"}, {"name": "Naveen"}]}
Ramin Faracov0
Related