Skip to content
Advertisement

Convert JSONB Keys to Columns

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

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

Advertisement

Answer

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:

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

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement