I am using the json_recordset syntax in my query, that looks like this

jsonb_to_recordset(t.field_42) as field_42(message text, messageid numeric,tablerowid numeric, addedon timestamp)

I'm trying to see if there is a way that I can alias the fields referenced here? For example, the "addedon" field in the recordset...is there any way to alias this to a different name like "startaddeon" for example?

I've tried to put "as" in the syntax like this:

jsonb_to_recordset(t.field_42) as field_42(message text, messageid numeric,tablerowid numeric, addedon timestamp as 'startaddedon')

but it doesn't like that syntaxt.

CodePudding user response：

Here is an illustration.

with the_table(id, field_42) as 
(
 values
 (1, '[
 {"message":"one", "messageid":1, "tablerowid":101, "addedon":"2022-10-06 22:20"},
 {"message":"two", "messageid":2, "tablerowid":102, "addedon":"2022-10-06 22:21"}
 ]'::jsonb),
 (2, '[
 {"message":"three","messageid":3, "tablerowid":103, "addedon":"2022-10-06 22:22"},
 {"message":"four", "messageid":4, "tablerowid":104, "addedon":"2022-10-06 22:23"}
 ]')
)
select t.id, l.message, l.messageid, l.tablerowid, 
       l.addedon as startaddedon -- here it is
from the_table t
cross join lateral jsonb_to_recordset(t.field_42) 
   as l(message text, messageid numeric,tablerowid numeric, addedon timestamp);