Find and Replace a String usng a Json Key Value pair object in MySQL

Question

I'm having a MySQL database table namely ds_message, it contains the template and JSON object. I would like to find the Key which is present in the template string with the JSON Key and replace the key with the JSON Value.

Table:

_____________________________________________________________________________________
id  template                                  key_value
_____________________________________________________________________________________
1  'Dear {a}, the price of {b} is {c}'       '{"a":"John", "b":"bat", "c":"$10"}'
2  'Dear {i}, you selected the product {j}'  '{"i":"Emma", "j":"Jam"}'

I need the SQL Select statement to get the string Dear John, the price of bat is $10 and each template have N number of keys, its not an identical across the table.

Table Structure:

CREATE TABLE `ds_message` (
  `id` int NOT NULL,
  `template` varchar(500) NOT NULL,
  `key_value` varchar(500) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='';

ALTER TABLE `ds_message`
  ADD PRIMARY KEY (`id`);

INSERT INTO `ds_message` (`id`, `template`, `key_value`) VALUES
(1, 'Dear {a}, the price of {b} is {c}', '{"a":"John", "b":"bat", "c":"$10"}');

INSERT INTO `ds_message` (`id`, `template`, `key_value`) VALUES
(2, 'Dear {i}, you selected the product {j}', '{"i":"Emma", "j":"Jam"}');

Expected Result:

Dear John, the price of bat is $10
Dear Emma, you selected the product Jam

Kindly assist me in SELECT statement or by Stored Procedure.

What version of MySQL are you using?

Tim Biegeleisen
– Tim Biegeleisen

2019-08-21 06:30:09 +00:00
Commented Aug 21, 2019 at 6:30 — Tim Biegeleisen
– Tim Biegeleisen, Commented Aug 21, 2019 at 6:30
@TimBiegeleisen - MySQL 5.7 as well as 8

B.Balamanigandan
– B.Balamanigandan

2019-08-21 06:33:38 +00:00
Commented Aug 21, 2019 at 6:33 — B.Balamanigandan
– B.Balamanigandan, Commented Aug 21, 2019 at 6:33

Tim Biegeleisen · Accepted Answer · 2019-08-21 06:49:28Z

1

Here is one approach, using JSON path expressions along with the REPLACE function:

WITH ds_message AS (
    SELECT 1 AS id, 'Dear {a}, the price of {b} is {c}' AS template, '{"a":"John", "b":"bat", "c":"$10"}'AS key_value
)

SELECT
    id,
    template,
    key_value,
    REPLACE(
        REPLACE(
            REPLACE(template, '{c}', key_value->"$.c"),
                '{b}', key_value->"$.b"),
                    '{a}', key_value->"$.a") AS output
FROM ds_message;

This outputs the following for output:

Dear "John", the price of "bat" is "$10"

answered Aug 21, 2019 at 6:49

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

B.Balamanigandan Over a year ago

The problem I'm facing here is, the the key value will be anything its not consistent for all the records, one record may have x,y,z and another may have j,k.... I up-voted for your effort. could you please assist me.

Tim Biegeleisen Over a year ago

Then MySQL is not the best place to be doing these replacements.

B.Balamanigandan Over a year ago

I modified the question in a clear way - Update version.

Tim Biegeleisen Over a year ago

I don't' think you can do what you want without using dynamic SQL (e.g. in a stored procedure).

B.Balamanigandan Over a year ago

No issues... Could you please share your idea through stored procedure, I too trying the same.

|

Collectives™ on Stack Overflow

Find and Replace a String usng a Json Key Value pair object in MySQL

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related