Postgres 11

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table one (id integer, data text);

CREATE TABLE


create table two (id integer, some_content text);

CREATE TABLE


insert into one values (1, 'some data'), (2, 'foo data');

INSERT 0 2


insert into two values (100, 'value foo'), (200, 'other data');

INSERT 0 2


with found_rows as (
  select table_name,
         query_to_xml(format($$ select to_jsonb(t) as table_row 
                      from %I.%I as t 
                      where t::text like '%%foo%%' $$, 
                      table_schema, table_name), true, false, '') as table_rows
  from information_schema.tables 
  where table_name in ('one', 'two')
)
select table_name, x.table_row
from found_rows f
     left join xmltable('//table/row' 
                         passing table_rows
                          columns
                            table_row text path 'table_row') as x on true


table_name
table_row


one
{"id": 2, "data": "foo data"}

two
{"id": 100, "some_content": "value foo"}



SELECT 2

table_name	table_row
one	{"id": 2, "data": "foo data"}
two	{"id": 100, "some_content": "value foo"}