Table: twitter_user_tweet
Tweets published by the specified user ID (author).
Note: The user_id
field must be set in the where
clause.
Examples
Last 5 tweets by the author
select id, textfrom twitter_user_tweetwhere user_id = '1318177503995985921' -- @steampipeioorder by created_at desclimit 5
Find all tweets in a user timeline related to open source
select id, ca -> 'entity' ->> 'name' as context, textfrom twitter_user_tweet, jsonb_array_elements(context_annotations) as cawhere user_id = '8092452' -- @turbothq and ca -> 'entity' ->> 'name' = 'Open source'
Get tweet timeline by username
Via subselect:
select id, textfrom twitter_user_tweet as twhere t.user_id in ( select id from twitter_user where username = 'steampipeio' )
Via join:
select t.id, t.textfrom twitter_user_tweet as t, twitter_user as uwhere t.user_id = u.id and u.username = 'steampipeio'
.inspect twitter_user_tweet
The user Tweet timeline endpoints provides access to Tweets published by a specific Twitter account.
Name | Type | Description |
---|---|---|
_ctx | jsonb | Steampipe context in JSON form, e.g. connection_name. |
attachment_polls | jsonb | Polls attached to the Tweet. |
attachments | jsonb | Specifies the type of attachments (if any) present in this Tweet. |
author | jsonb | Author of the Tweet. |
author_id | text | Unique identifier of the author of the Tweet. |
cashtags | jsonb | List of cashtags (e.g. $TWTR) mentioned in the Tweet. |
context_annotations | jsonb | Contains context annotations for the Tweet. |
conversation_id | text | The Tweet ID of the original Tweet of the conversation (which includes direct replies, replies of replies). |
created_at | timestamp with time zone | Creation time of the Tweet. |
entities | jsonb | Contains details about text that has a special meaning in a Tweet. |
geo | jsonb | Contains details about the location tagged by the user in this Tweet, if they specified one. |
hashtags | jsonb | List of hashtags (e.g. #sql) mentioned in the Tweet. |
id | text | Unique identifier of this Tweet. |
in_reply_to_user_id | text | If this Tweet is a Reply, indicates the user ID of the parent Tweet's author. |
in_reply_user | jsonb | User the Tweet was in reply to. |
lang | text | Language of the Tweet, if detected by Twitter. Returned as a BCP47 language tag. |
mentions | jsonb | List of users (e.g. steampipeio) mentioned in the Tweet. |
mentions_obj | jsonb | Users mentioned in the Tweet. |
place | jsonb | Place where the Tweet was created. |
possibly_sensitive | boolean | Indicates if this Tweet contains URLs marked as sensitive, for example content suitable for mature audiences. |
public_metrics | jsonb | Engagement metrics for the Tweet at the time of the request. |
quoted | text | If this Tweet is a Quote Tweet, indicates the ID of the original Tweet. |
referenced_tweets | jsonb | Tweets referenced in this Tweet. |
replied_to | text | If this Tweet is a Reply, indicates the ID of the Tweet it is a reply to. |
retweeted | text | If this Tweet is a Retweet, indicates the ID of the orginal Tweet. |
source | text | The name of the app the user Tweeted from. |
text | text | The content of the Tweet. |
urls | jsonb | List of URLs (e.g. https://steampipe.io) mentioned in the Tweet. |
user_id | text | ID of the user the tweets are related to. |
withheld | jsonb | Contains withholding details for withheld content. |