CODEWITHSUNDEEP
mysqljsonreplacekey-valuemysql-json
B.Balamanigandan
B.Balamanigandan

Reputation: 4875

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

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:

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

Kindly assist me in SELECT statement or by Stored Procedure.

Upvotes: 2

Views: 1053

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521914

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"

Upvotes: 1

Related Questions