我有一个MySQL数据库表,即ds_message
,它包含模板和JSON对象。我想用 JSON 密钥找到模板字符串中存在的密钥,并将密钥替换为 JSON 值。
桌子:
_____________________________________________________________________________________
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"}'
我需要 SQL Select 语句来获取字符串Dear John, the price of bat is $10
并且每个模板都有 N 个键,它在整个表中并不相同。
表结构:
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"}');
预期成果:
- 亲爱的约翰,蝙蝠的价格是 10 美元
- 亲爱的艾玛,您选择了产品果酱
请在 SELECT 语句或存储过程中协助我。
下面是一种方法,使用 JSON 路径表达式和REPLACE
函数:
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;
这将输出以下output
:
Dear "John", the price of "bat" is "$10"