I have a user table users containing id , name and information of type jsonb

User Table

I have another Table having all the profile fields values named ProfileFields

The information jsonb column can only have keys present in the ProfileField Table.

You can expect the data is coming from a real world and the profile field will be updating.

I would like to output export this table in the format of

My Trails :- I was able to map profilefieldid with its respective values

SELECT
id ,
name ,
(SELECT STRING_AGG(CONCAT((SELECT "title" FROM "profile_fields" WHERE CAST("key" AS INTEGER)="id"),':',REPLACE("value",'"','')),',') FROM JSONB_EACH_TEXT("profile_fields")) "information"
FROM "users" ORDER BY "id";

I tried to use json_to record() but since the profilefield can have dynamic keys i was not able to come up with a solution because in the AS block i need to specify the columns in advance.

I sometimes encounter errors in Select Statement as Subquery returning more than 1 column.

Any suggestions and Solutions are greatly appreciated and welcomed.

Let me know if i need to improve my db structure , like its not in 2nd NormalForm or not well structured like that. Thank You

CodePudding user response：

There is no way you can make this dynamic. A fundamental restriction of the SQL language is, that the number, names and data type of all columns of a query must be known before the database starts retrieving data.

What you can do though is to create a stored procedure that generates a view with the needed columns:

create or replace procedure create_user_info_view()
as
$$
declare
  l_columns text;
begin
  
  select string_agg(concat('u.information ->> ', quote_literal(profilefield_id), ' as ', quote_ident(value)), ', ')
    into l_columns
  from profile_fields;
  
  execute 'drop view if exists users_view cascade';
  execute 'create view users_view as select u.id, u.name, '||l_columns||' from users u';
end;
$$
language plpgsql;

After the procedure is executed, you can run select * from users_view and see all profile keys as columns.

If you want, you can create a trigger on the table profile_fields that re-creates the view each time the table is changed.

Online example