How to remove an object from json on condition Postgresql

Question

JsonData
{"DestinationLists" : [{"name" : "Slingshot", "destinations" : [{"Id" : 4155}, {"Id" : 191}, {"Id" : 165}, {"Id" : 183}]}]}
{"DestinationLists" : [{"name" : "TVNZ, Mediaworks, Choice", "destinations" : [{"Id" : null}]}, {"name" : "TVNZ, Discovery", "destinations" : [{"Id" : 165}, {"Id" : 183}, {"Id" : 4155}]}]}
{"DestinationLists" : [{"name" : "SecondTest", "destinations" : [{"Id" : 103}, {"Id" : 105}]}, {"name" : "ThirdTest", "destinations" : [{"Id" : 3}, {"Id" : 5}]}]}

I am trying to write a select query on this data which empties the destinations array incase any of the "Id" is null

So the output should be (empty destinations array in second row) :-

JsonData
{"DestinationLists" : [{"name" : "Slingshot", "destinations" : [{"Id" : 4155}, {"Id" : 191}, {"Id" : 165}, {"Id" : 183}]}]}
{"DestinationLists" : [{"name" : "TVNZ, Mediaworks, Choice", "destinations" : []}, {"name" : "TVNZ, Discovery", "destinations" : [{"Id" : 165}, {"Id" : 183}, {"Id" : 4155}]}]}
{"DestinationLists" : [{"name" : "SecondTest", "destinations" : [{"Id" : 103}, {"Id" : 105}]}, {"name" : "ThirdTest", "destinations" : [{"Id" : 3}, {"Id" : 5}]}]}

How can I achieve this?

Does this answer answer your question?

ggordon
– ggordon

2021-08-17 00:05:01 +00:00
Commented Aug 17, 2021 at 0:05 — ggordon
– ggordon, Commented Aug 17, 2021 at 0:05

Ajax1234 · Accepted Answer · 2021-08-17 18:47:50Z

You can use json_array_elements with a subquery to determine if a "destinations" key contains any null Id values in its array:

select json_build_object('DestinationLists', 
    (select array_to_json(
         array_agg(
            case when not exists (select 1 from json_array_elements(v.value -> 'destinations') v1 
                 where (v1.value -> 'Id')::text = 'null')
            then v.value::jsonb
            else jsonb_set(v.value::jsonb, '{destinations}'::text[], '[]'::jsonb) end
        )
     ) 
     from json_array_elements(d1.js -> 'DestinationLists') v)
)
from data d1

Output:

JsonData
{"DestinationLists" : [{"name" : "Slingshot", "destinations" : [{"Id" : 4155}, {"Id" : 191}, {"Id" : 165}, {"Id" : 183}]}]}
{"DestinationLists" : [{"name" : "TVNZ, Mediaworks, Choice", "destinations" : []}, {"name" : "TVNZ, Discovery", "destinations" : [{"Id" : 165}, {"Id" : 183}, {"Id" : 4155}]}]}
{"DestinationLists" : [{"name" : "SecondTest", "destinations" : [{"Id" : 103}, {"Id" : 105}]}, {"name" : "ThirdTest", "destinations" : [{"Id" : 3}, {"Id" : 5}]}]}

Collectives™ on Stack Overflow

How to remove an object from json on condition Postgresql

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related