Replace apostrophe ( ' ) in postgresql jsonb array object

Question

Please find the below jsonb object where i need to replace all apostrophe from instruction key.

    [{
        "instruction": "Don't need to book car for M'lore location",
        "reservationNo": "TT00098272"
    },
    {
        "instruction": "Please book a car for mumbai location",
        "reservationNo": "TT00098273"
    }
 ]

Expected Result : values are replace with apostrophe

[{
            "instruction": "Dont need to book car for Mlore location",
            "reservationNo": "TT00098272"
        },
        {
            "instruction": "Please book a car for mumbai location",
            "reservationNo": "TT00098273"
        }
     ]

Just check with replace(jsoncolumn::text,'''','')::json

MAK
– MAK

2019-12-24 08:56:20 +00:00
Commented Dec 24, 2019 at 8:56 — MAK
– MAK, Commented Dec 24, 2019 at 8:56

richyen · Accepted Answer · 2019-12-24 08:56:57Z

You can do a regexp_replace() after casting to text:

postgres=# create table j (field jsonb);
CREATE TABLE
postgres=# insert into j values ('[{
postgres'#         "instruction": "Don''t need to book car for M''lore location",
postgres'#         "reservationNo": "TT00098272"
postgres'#     },
postgres'#     {
postgres'#         "instruction": "Please book a car for mumbai location",
postgres'#         "reservationNo": "TT00098273"
postgres'#     }
postgres'#  ]'::jsonb);
INSERT 0 1
postgres=# select regexp_replace(field::text,'''','','g')::jsonb from j;
                                                                                    regexp_replace                                                                                     
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 [{"instruction": "Dont need to book car for Mlore location", "reservationNo": "TT00098272"}, {"instruction": "Please book a car for mumbai location", "reservationNo": "TT00098273"}]
(1 row)

Collectives™ on Stack Overflow

Replace apostrophe ( ' ) in postgresql jsonb array object

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related