SQL Query to replace multiple values in template from many-to-many table

Question

I want to translate a template in an sql query. Lets assume there are the following fourtables: state, stateProperty, state_stateproperty and translation:

state_stateproperty

|---------------------|--------------------|
|      state_id       | stateproperties_id |
|---------------------|--------------------|
|          1          |        2           |
|---------------------|--------------------|
|          1          |        3           |
|---------------------|--------------------|

stateproperty

|---------------------|------------------|
|  id  |     key      |      value       |
|------|--------------|------------------|
|  2   | ${firstName} |      John        |
|------|--------------|------------------|
|  3   | ${lastName}  |      Doe         |
|------|--------------|------------------|

state

|---------------------|
|  id  |  template    |
|------|--------------|
|  1   |  template    |
|------|--------------|

translation

|------------|--------------|---------------------------------|
|  language  |  messageId   |             value               |
|------------|--------------|---------------------------------|
|  en        |  template    | ${lastName}, ${firstName} alarm |
|------------|--------------|---------------------------------|

The aim is to get a new entity named translatedstate that includes the translated template of the state. In this example the translated template would look like: "Doe, John alarm". How can you join a many to many table in native sql and translate the template of the state with the values of its related state properties?

Answer 1

To be honest I would create a little function where I would loop through your state_property and cumulative replace the found wildcard string with its text.

---

But I had some fun to solve it in a query. I am not sure if it matches all special cases but for your example it works:

demo:db<>fiddle

SELECT
    string_agg(                                                           -- 8
        regexp_replace(split_key, '^.*\}', value),                        -- 7
        '' ORDER BY row_number
    )
FROM (
    SELECT
        s.id,
        sp.value,
        substring(key, 3) as s_key,                                       -- 5
        split_table.*
    FROM translation t
    JOIN statechange sc ON t.messageid = sc.completemessagetemplateid     -- 1
    JOIN state s ON s.id = sc.state_id
    JOIN state_stateproperty ssp ON s.id = ssp.state_id
    JOIN stateproperty sp ON ssp.stateproperties_id = sp.id
    JOIN translation stnme ON s.nameid = stnme.messageid
    CROSS JOIN                                        
        regexp_split_to_table(                                            -- 3   
            -- 2
            replace(t.messagetranslation, '${state}', stnme.messagetranslation),
            '\$\{'
        ) WITH ORDINALITY as split_table(split_key, row_number)           -- 4
    WHERE t.language = 'en'
) s
WHERE position(s_key in split_key) = 0 and split_key != ''                -- 6
GROUP BY id                                                               -- 8

1. Simple join the tables together (for next time you could simplify your example a little bit so that we don't have to create these different table. I am sure you know how to join)
2. Hardly replace the ${state} variable with the state nameid
3. This splits the template string every time a ${ string is found. So it creates a new row which begins a certain wildcard. Note that ${firstName} would become firstName} because the string delimiter is being deleted.
4. Adding a row count to get a criteria how the rows are ordered when I aggregate them later (8). WITH ORDINALITY only works as part of the FROM clause so the whole function it has been added here with a join.
5. Because of (3) I strip the ${ part from the keys as well. So it can be better parsed and compared later (in 6)
6. Because (3) creates too much rows (cross join) I want only these where the key is the first wildcard of my split string. All others are wrong.
7. Now I replace the wildcard with this key
8. Because we have only one wildcard per row we need to merge them together into one string again (grouped by state_id). The achieve the right order, we are using the row number from (5)