Formatting a string column with an array or json in PostgreSQL

Question

I am trying to format a column which is string type with another column which is json or array type.

For example;

String column is like "I am a %s, and I am very good %s" and array like ["student", "at math"].

or

String like "I am a {key1}, and I am very good {key2}" and json like {"key1": "student", "key2": "at math"}

I want to format string column with this second column.

Is it possible to do something like this with PostgreSQL also if it is possible with SQLAlchemy that is better for me.

user330315 · Accepted Answer · 2021-10-05 11:54:10Z

1

I can't think of an easy way to do this in plain SQL, so I would write a function for this:

create or replace function fill_placeholders(p_text text, p_parameters jsonb)
  returns text
as
$$
declare
  l_index int;
  l_result text := p_text;
  l_element record;
begin
  if jsonb_typeof(p_parameters) = 'array' then  
    for l_index in 0..jsonb_array_length(p_parameters) - 1 loop
      raise notice '%', l_result;
      l_result := regexp_replace(l_result, '(\%s)', p_parameters ->> l_index);
    end loop;
  else
    for l_element in select * from jsonb_each_text(p_parameters) as x(key,value) loop
      l_result := replace(l_result, '{'||l_element.key||'}', l_element.value);
    end loop;
  end if;
  return l_result;
end;
$$
language plpgsql;

The the following queries:

select fill_placeholders('I am a %s, and I am very good %s', '["student", "at math"]');

select fill_placeholders('I am a {key1}, and I am very good {key2}', '{"key1": "student", "key2": "at math"}');

will both return: I am a student, and I am very good at math

answered Oct 5, 2021 at 11:54

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

madogan Over a year ago

Firstly, thank you. I am using ORM and using function is not easy for me because I am not familiar with it. Can I do in select statement? Also one option is OK for me. I will write from scratch, which option is better I will use it (Array or Dict).

user330315 Over a year ago

Just create the function, then you can use it in a SELECT statement

Collectives™ on Stack Overflow

Formatting a string column with an array or json in PostgreSQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related