postgres jsonb string find and replacement

postgres_jsonb_string_find_replacement.sql

-- Create table
create table t1(doc jsonb);

-- Verify
SELECT * FROM t1;

-- insert some data
INSERT INTO t1
VALUES
('{"param1": 10, "param2": 15}'),
('{"param1": 10, "param2": 5}');

-- Verify
SELECT * FROM t1;
SELECT doc::text FROM t1;


-- Play Jsonb String pattern replace
SELECT replace(replace(doc->>'param1', '0', '**'), '1', '2')
FROM t1;


SELECT replace(replace(doc::text, 'm1": ', 'm1": 9999'), '2": ', '2": 777')
FROM t1;

SELECT replace(replace(doc::text, 'm1": ', 'm1": 9999'), '2": ', '2": 777')::jsonb
FROM t1;

-- adds in column to add a WHERE clause on

ALTER TABLE t1
ADD COLUMN name varchar

UPDATE t1
SET name = '4543534534'
WHERE doc->>'param2'::text = '15'
    

--- Regex with case sensitivity on Last token block meaning [i=insensitive, g=global replacement]
-- More here: https://stackoverflow.com/a/31583547/837005
UPDATE t1
SET doc = regexp_replace(regexp_replace(doc::text, 'M1": ', 'M1": 303', 'g'), 'M2": ', 'M2": 22', 'g')::jsonb
WHERE t1.name like '999%';

--- worked but case insensitive
UPDATE t1
SET doc = replace(replace(doc::text, 'M1": ', 'M1": 44'), '2": ', '2": 444')::jsonb
WHERE t1.name like '999%';