Data format in PostgreSQL when accessed from an external BI system

After connecting the PostgreSQL database to a third-party BI system, a problem was discovered. For example, string-type data in the table is formatted as {"v": "some text"}. In the BI system, these fields are identified as "Unsupported type". Could you suggest how to resolve this data format issue?

Totum uses the jsonb type, which is a structure. Therefore, when querying, you need to access the key v within the field:

select first_field->>'v' as "field 1", second_field->>'v' as "field 2" from totum.tableau where (second_field->>'v')::NUMERIC > 20

If the connected BI system cannot execute such a query, or if it is inconvenient, you need to create a view in PostgreSQL that will convert the data into the required formats.

Creating a view in the database:

create view users__v(id, login, fio, boss_id, roles, add_users, all_connected_users, interface, favorite, is_del) as
SELECT users.id,
       (users.login ->> 'v'::text) AS login,
       (users.fio ->> 'v'::text) AS fio,
       ((users.boss_id ->> 'v'::text))::integer AS boss_id,
       (users.roles ->> 'v'::text) AS roles,
       (users.add_users ->> 'v'::text) AS add_users,
       (users.all_connected_users ->> 'v'::text) AS all_connected_users,
       (users.interface ->> 'v'::text) AS interface,
       (users.favorite ->> 'v'::text) AS favorite,
       users.is_del
FROM users;