Postgres 9.6

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, r.data
from found_rows f
   cross join unnest(xpath('/table/row/table_row/text()', table_rows)) as r(data)

table_name
data

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

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

SELECT 2

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