I have this simple query that is joining 2 tables to get me the access token (Note: there is only 1 token in each row) from one table and the respective story id from another table. I am then executing this statement (setting fetch_all = True)

I'm thinking my next step using Python is to loop through each story id (there can be several csv's in 1 cell) and use the respective access token.

So I'm wondering if I can add id: before each story_id and token: before each access_token

Query:

select
story.story_id,
info.access_token
from story_table story
join page_info info
on info.account_info_id = story.account_id
where to_date(story._load_datetime) = '2021-12-23'

The results look like this:

CodePudding user response：

Try this:

WITH x AS (
  select
  story.story_id,
  info.access_token
  from story_table story
  join page_info info
  on info.account_info_id = story.account_id
  where to_date(story._load_datetime) = '2021-12-23'
  )
SELECT y.seq, y.value::string as story_id, x.access_token
FROM x,
LATERAL FLATTEN (input => SPLIT(story_id,',')) y;

The result of this is a line per story_id with the correct access_token. If you wanted to append text to the output and the re-aggregate that back again, let me know and I will update this answer.