0

I have the following JSON structure in postgres.

drop table if exists data  cascade;
create table json_test (data json);
insert into json_test
values
('{ 
 "a": "1", 
 "b": "2", 
 "c": "3", 
 "level2": [
     { 
         "a": "1", 
         "b": "2", 
         "c": "3", 
         "level3": [
             { 
                 "a": "1",
                 "b": "2", 
                 "c": "3"
             }, { 
                 "a": "1", 
                 "b": "2", 
                 "c": "3"
             }
         ] 
    }, { 
         "a": "1", 
         "b": "2", 
         "c": "3", 
         "level3": [
             { 
                 "a": "1", 
                 "b": "2", 
                 "c": "3"
             }, { 
                 "a": "1", 
                 "b": "2", 
                 "c": "3"
             }
         ]
     }
 ] 
}');

I'm trying to query all the 'a' attributes to get a structure like the following.

'{ 
 "a": "1", 
 "level2": [
     { 
         "a": "1", 
         "level3": [
             { 
                 "a": "1"
             }, { 
                 "a": "1"
             }
         ] 
    }, { 
         "a": "1",  
         "level3": [
             { 
                 "a": "1" 
             }, { 
                 "a": "1" 
             }
         ]
     }
 ] 
}'

I know how to remove certain attributes, however the problem comes when remove the attributes from each array element.

select data::jsonb -'b' -'c' #-'{level2,0,b}' from json_test

What would be even better is if I could somehow specify the attributes I want. Something like +{a, level2[{a, level3[{a}]}] However, I'm not sure if that possible.

What's the best way to get the desired result above?

Test Data: https://rextester.com/UKJU4149

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.