PostgreSQL: How can I replace to one column from the json values of another column?

Question

I wasn't able to find a solution to this one.

Say I have a table like this

with tmp_table(json_row, message) as (
    values 
    ('{"key1": 1, "key2":"first"}'::jsonb, 'this ${key2} is a test of ${key1}'),
    ('{"key1": 2, "key2":"second"}', 'this ${key2} is a test'),
    ('{"key1": 3, "key2":"third"}', 'this ${key1} is a test of ${key2}')
)
select * from tmp_table

How can I replace the external parameters from the json values of the other column so that the expected result is

Thanks in advance!

Edit: I want this to be with regex-replace to find whatever is between the ${}

Answer 1

You can combine the regexp_matches and regexp_split_to_table pattern matching functions to create a table of string parts from each message - each row consisting of a literal part and a ${…}-replacement part, the last row not containing a replacement variable. Then do the replacement by choosing the respective key from the json object, then string_aggregate all the parts back together into the result. Do this with a subquery for each row in your table:

SELECT (
  SELECT string_agg(concat(literal, data->>variable[1]), '')
  FROM (
    SELECT
      regexp_split_to_table(message, '\$\{([^}]*)\}') AS literal,
      regexp_matches(message, '\$\{([^}]*)\}', 'g') AS variable
  ) AS parts
) AS result
FROM example

^{(online demo)}