Question 89445 in PostgreSQL Question posted in
The official documentation can be found here.

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

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

enter image description here

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

enter image description here

Thanks in advance!

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

Tags:

2

Answers


  1. 0

    try 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"}'::jsonb, 'this ${key2} is a test'),
    ('{"key1": 3, "key2":"third"}'::jsonb, 'this ${key1} is a test of ${key2}')
)
select json_row,replace(replace(message,'${key1}', json_row->>'key1'),'${key2}', json_row->>'key2') as message from tmp_table

    db<>fiddle

    the output
    enter image description here

    Login or Signup to reply.
  2. 0

    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)

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search