Question 89541 in PostgreSQL Question posted in
The official documentation can be found here.

remove object from the array PostgreSQL

I try to remove pixelId":"AW-23423524124" from the array and in case its the only one in the array trackingIds than remove the entire object of pxg. it does not work using PostgreSQL

    UPDATE clicks
SET protection_config =
    CASE
        WHEN protection_config LIKE '%{"pxg":{"trackingIds":[{"pixelId":"AW-23423524124"%' AND jsonb_array_length(replace(replace(protection_config, 'false', '0'), 'true', '1')::jsonb->'pxg'->'trackingIds') = 1
            THEN '{}'
        WHEN protection_config LIKE '%{"pxg":{"trackingIds":[{"pixelId":"AW-23423524124"%' AND jsonb_array_length(replace(replace(protection_config, 'false', '0'), 'true', '1')::jsonb->'pxg'->'trackingIds') > 1
            THEN replace(replace(protection_config, 'false', '0'), 'true', '1')::jsonb #- '{pxg, trackingIds}' || jsonb_build_object('pxg', jsonb_build_object('trackingIds', (replace(replace(protection_config, 'false', '0'), 'true', '1')::jsonb->'pxg'->'trackingIds') #- '{0}' #- '{pixelId}'))::text
        ELSE protection_config
    END
WHERE protection_config LIKE '%{"pxg":{"trackingIds":[{"pixelId":"AW-23423524124"%' and network_id='5766'

column looks like that– please note the column is type -character varying

{"monitoringMode":{"isMonitoring":false,"dates":null},"pxg":{"trackingIds":[{"active":1,"pixelId":"AW-23423524124"}]},"uaTrackingIds":[{"pixelId":"UA-123","dimensionIndex":"dimension123"},{"pixelId":"UA-1233","dimensionIndex":"dimension3"}]}
Tags:

2

Answers


  1. 0

    You can benefit from converting the column in question to a json type. Kind of

    update clicks
  set protection_config = (
     select case when array_agg(e) is null then '{}'
            else jsonb_build_object('pxg',jsonb_build_object('trackingIds', to_jsonb(array_agg(e))))::varchar(2000)
            end
     from jsonb_array_elements(protection_config::jsonb ->'pxg' ->'trackingIds') ids(e)      
     where e->>'pixelId' != 'AW-23423524124'
  )

    db<>fiddle

    Login or Signup to reply.
  2. 0

    I used almost same logic as yours.

    json_array_length to get array length.

    jsonb_path_query_array used to get data as array based on condition.

    jsonb_set to update our json.

    Docs here

     with cte as (
   select network_id, CASE
   WHEN trackingIdsLength = 1 
       THEN jsonb_set(
             protection_config, '{pxg}','{}', false)
       WHEN trackingIdsLength  > 1
         THEN jsonb_set( 
             protection_config, '{pxg,trackingIds}', 
             jsonb_path_query_array(to_jsonb(protection_config::jsonb->'pxg'->'trackingIds'), '$[*] ? (@.pixelId <> "AW-23423524124")')
         )
    ELSE protection_config END as new_protection_config
  from (
    select network_id, protection_config::jsonb, 
    json_array_length(to_json(protection_config::jsonb->'pxg'->'trackingIds')) as trackingIdsLength
    from clicks c
    cross join lateral jsonb_array_elements(protection_config::jsonb->'pxg'->'trackingIds') as e
    where e ->> 'pixelId' like 'AW-23423524124'
  ) as s
)
update clicks c
set protection_config = cte.new_protection_config
from cte
where c.network_id = cte.network_id

    Demo here

    To remove the entire object pxg use this instead :

    set protection_config = REGEXP_REPLACE(new_protection_config::text, '("pxg": {}[,]?)(.*)','2')
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search