Convert JSONB Keys to Columns

Question

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

User Table

id	name	information
1001	Alice	{"1":"Google","2":"1991-02-08"}
1002	Bob	{"1":"StackOverflow","3":"www.google.com"}

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

profilefieldid	Value
1	Company
2	DateOfBirth
3	ProfileLink

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

id	name	Company	DateOfBirth	ProfileLink
1001	Alice	Google	1991-02-08
1002	Bob	StackOverflow		www.google.com

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

Answer 1

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