create schema test_json_replace;
set current_schema to 'test_json_replace';
-- test for basic functionality of json_replace
SELECT JSON_REPLACE('{"a": 1, "b": 2, "c": 3}', '$.b', 9);
       json_replace       
--------------------------
 {"a": 1, "b": 9, "c": 3}
(1 row)

SELECT JSON_REPLACE('{"Name": "Bart", "Age": 10}', '$.Name', 'Nelson - Ha Ha!');
              json_replace              
----------------------------------------
 {"Age": 10, "Name": "Nelson - Ha Ha!"}
(1 row)

SELECT JSON_REPLACE('{"x": 1}', '$.x', '{"z": 2}');
    json_replace     
---------------------
 {"x": "{\"z\": 2}"}
(1 row)

SELECT JSON_REPLACE('{"x": 1}', '$.x', '{"z": 2}'::json);
  json_replace   
-----------------
 {"x": {"z": 2}}
(1 row)

SELECT JSON_REPLACE('{"x": 1}', '$.x', 'true');
 json_replace  
---------------
 {"x": "true"}
(1 row)

SELECT JSON_REPLACE('{"x": 1}', '$.x', true);
 json_replace 
--------------
 {"x": true}
(1 row)

SELECT JSON_REPLACE('{"x": 1}', '$.x', false);
 json_replace 
--------------
 {"x": false}
(1 row)

SELECT JSON_REPLACE('{"x": 1}', '$.x', NULL);
 json_replace 
--------------
 {"x": null}
(1 row)

SELECT JSON_REPLACE('{"x": 1}', '$.x', 'null');
 json_replace  
---------------
 {"x": "null"}
(1 row)

SELECT JSON_REPLACE('{"x": 1}', '$.x', 'true'::json);
 json_replace 
--------------
 {"x": true}
(1 row)

SELECT JSON_REPLACE('{"x": 1}', '$.x', '{"q":false}'::jsonb);
    json_replace     
---------------------
 {"x": {"q": false}}
(1 row)

SELECT JSON_REPLACE('{"x": 1}', '$.x', '{"q":false}'::cstring);
      json_replace      
------------------------
 {"x": "{\"q\":false}"}
(1 row)

SELECT JSON_REPLACE('{"x": 1}'::text, '$.x', '{"q":false}'::text);
      json_replace      
------------------------
 {"x": "{\"q\":false}"}
(1 row)

SELECT JSON_REPLACE('{"x": 1}'::json, '$.x', '{"q":false}'::text);
      json_replace      
------------------------
 {"x": "{\"q\":false}"}
(1 row)

SELECT JSON_REPLACE('{"x": 1}'::jsonb, '$.x', '{"q":false}'::cstring);
      json_replace      
------------------------
 {"x": "{\"q\":false}"}
(1 row)

SELECT JSON_REPLACE('{"x": 1}'::cstring, '$.x', '{"q":false}');
      json_replace      
------------------------
 {"x": "{\"q\":false}"}
(1 row)

select json_replace('{"x":3}','$',1);
 json_replace 
--------------
 1
(1 row)

select json_replace('{"x":3}','$',true);
 json_replace 
--------------
 true
(1 row)

select json_replace('{"x":3}','$','');
 json_replace 
--------------
 ""
(1 row)

select json_replace('{"x":3}','$','a');
 json_replace 
--------------
 "a"
(1 row)

select json_replace('{"x":3}','$','{"a":2}');
 json_replace 
--------------
 "{\"a\":2}"
(1 row)

select json_replace('{"x":3}','$','{"a":2}','$',2);
 json_replace 
--------------
 2
(1 row)

select json_replace('{"x":3}','$','{"a":2}'::json,'$.a',3);
 json_replace 
--------------
 {"a": 3}
(1 row)

select json_replace('{"x":3}','$','{"a":2}'::json,'$',null);
 json_replace 
--------------
 null
(1 row)

select json_replace('{"x": 1}', '$.x', 341522654875451.12345678901234567890123456789012345678901234567890);
                               json_replace                                
---------------------------------------------------------------------------
 {"x": 341522654875451.12345678901234567890123456789012345678901234567890}
(1 row)

-- test for Non-Existent Path
SELECT JSON_REPLACE('{"Name": "Homer", "Age": 39}', '$.Gender', 'Male');
         json_replace         
------------------------------
 {"Age": 39, "Name": "Homer"}
(1 row)

SELECT JSON_REPLACE('{"Name": "Adam", "Age": 88}', '$.Value', 89);
        json_replace         
-----------------------------
 {"Age": 88, "Name": "Adam"}
(1 row)

-- test for Arrays
SELECT JSON_REPLACE('[1, 2, 3]', '$[0]', 9);
 json_replace 
--------------
 [9, 2, 3]
(1 row)

SELECT JSON_REPLACE('[1, 2, [3, 4, 5]]', '$[2][1]', 9);
   json_replace    
-------------------
 [1, 2, [3, 9, 5]]
(1 row)

SELECT JSON_REPLACE('[1, [2, 3]]', '$[0]', 0, '$[2]', 6);
 json_replace 
--------------
 [0, [2, 3]]
(1 row)

-- test for Multiple Paths
SELECT JSON_REPLACE('[1, 2, 3, 4, 5]', '$[1]', 9, '$[3]', 22);
   json_replace   
------------------
 [1, 9, 3, 22, 5]
(1 row)

-- test for A Larger JSON Document
SELECT JSON_REPLACE('{  
    "Person": {    
       "Name": "Homer", 
       "Age": 39,
       "Hobbies": ["Eating", "Sleeping"]  
    }
 }', '$.Person.Name', 'Bart', '$.Person.Age', 10, '$.Person.Hobbies[1]', 'Base Jumping');
                                  json_replace                                  
--------------------------------------------------------------------------------
 {"Person": {"Age": 10, "Name": "Bart", "Hobbies": ["Eating", "Base Jumping"]}}
(1 row)

-- test for invalid path
SELECT JSON_REPLACE('{"x":1}',2,2);
ERROR:  Invalid JSON path expression. The error is around argument 1.
CONTEXT:  referenced column: json_replace
SELECT JSON_REPLACE('{"x":1}','2.as',2);
ERROR:  Invalid JSON path expression. The error is around argument 1.
CONTEXT:  referenced column: json_replace
-- test for invalid json text
SELECT JSON_REPLACE('', '$[1]', 9, '$[3]', 22);
ERROR:  Invalid JSON text in argument 1 to function json_replace.
CONTEXT:  referenced column: json_replace
SELECT JSON_REPLACE(122323, '$.b', 9, '$.a', 22);
ERROR:  Invalid data type for JSON data in argument 1 to function json_replace
CONTEXT:  referenced column: json_replace
-- test for NULL
SELECT JSON_REPLACE(null,2,2);
 json_replace 
--------------
 
(1 row)

SELECT JSON_REPLACE(null,'$.a',true);
 json_replace 
--------------
 
(1 row)

SELECT JSON_REPLACE(null,11);
ERROR:  Incorrect parameter count in the call to native function 'JSON_REPLACE'
CONTEXT:  referenced column: json_replace
-- test for incorrect number
SELECT JSON_REPLACE('x');
ERROR:  Incorrect parameter count in the call to native function 'JSON_REPLACE'
CONTEXT:  referenced column: json_replace
SELECT JSON_REPLACE('x', 2);
ERROR:  Incorrect parameter count in the call to native function 'JSON_REPLACE'
CONTEXT:  referenced column: json_replace
SELECT JSON_REPLACE('x','a',3,true);
ERROR:  Incorrect parameter count in the call to native function 'JSON_REPLACE'
CONTEXT:  referenced column: json_replace
-- test for invalid json document
SELECT JSON_REPLACE('x',2,2);
ERROR:  Invalid JSON text in argument 1 to function json_replace.
CONTEXT:  referenced column: json_replace
-- replace function for json
select replace(cast('{"key1":"values1"}' as json),'"','');
    replace     
----------------
 {key1:values1}
(1 row)

select replace(cast('{"key1"}' as json),'"','');
ERROR:  invalid input syntax for type json
LINE 1: select replace(cast('{"key1"}' as json),'"','');
                            ^
DETAIL:  Expected ":", but found "}".
CONTEXT:  JSON data, line 1: {"key1"}
referenced column: replace
create table test1(attr_json json);
insert into test1 values('{"key1":"values1","description":"value1"}');
select  REPLACE (a.attr_json-> '$.description','"','') from test1 a;
 replace 
---------
 value1
(1 row)

drop table test1;
drop schema test_json_replace cascade;
reset current_schema;