I have a table in a Postgres database that contains users, with their attributes stored as JSONB. The attributes can vary per user, and can be strings or lists of strings. Example snippets for two users:
select username, attributes from idp_accounts where username in ('visser', 'peter');
username | attributes
----------+----------------------------------------------------------------------------------------------------------------------------------------------
peter | {"dn": "Peter Vonk", "sHO": "hei.foobar1.zz", "ePSA": ["[email protected]", "[email protected]"], "email": "[email protected]"}
visser | {"cn": "Dick Visser", "sn": "Visser", "uid": ["visser"], "mail": ["[email protected]", "[email protected]"], "displayName": "Dick Visser"}
(2 rows)
I'm looking for a way to select all attributes for a user as columns, so that the results looks like this:
ssp=> select username, ????attributes???? from idp_accounts where username = 'visser';
username | cn | sn | uid | mail | displayName
----------+--------------+-----------+-------------+------------------------------------------------+----------------
visser | Dick Visser | Visser | ["visser"] | ["[email protected]", "[email protected]"] | Dick Visser
(1 row)
I've been over the various JSON functions but can't seem to get the result I'm looking for.
jsonb_to_record(attributes), see the duplicates for examplesjsonb_populate_record()is typically the best (fastest, safest, cleanest) option. Especially with a given target table. I added a link.