You can GROUP BY
by the variables that identify the tweet and then use GROUP_CONCAT
to concatenate the hashtags into something like an array, but it will still be a string that you'll need to parse afterward. For instance, given data like
@prefix smo: <http://example.org/> .
@prefix : <http://example.org/> .
:tweet1 smo:tweeted_at "1" ;
smo:has_hashtag "tag1", "tag2", "tag3" ;
smo:tweeted_by "user1" ;
smo:english_tweet true .
:tweet2 smo:tweeted_at "2" ;
smo:has_hashtag "tag2", "tag3", "tag4" ;
smo:tweeted_by "user2" ;
smo:english_tweet true .
you can use a query like
prefix smo: <http://example.org/>
select ?tweet ?date ?account (group_concat(?hashtag) as ?hashtags) where {
?tweet smo:tweeted_at ?date ;
smo:has_hashtag ?hashtag ;
smo:tweeted_by ?account ;
smo:english_tweet true .
}
group by ?tweet ?date ?account
to get results like:
--------------------------------------------------
| tweet | date | account | hashtags |
==================================================
| smo:tweet2 | "2" | "user2" | "tag4 tag3 tag2" |
| smo:tweet1 | "1" | "user1" | "tag3 tag2 tag1" |
--------------------------------------------------
You can specify the delimiter used in the group concatenation, so if there is some character that cannot appear in hashtags, you can use it as a delimiter. For instance, supposing that |
can't appear in hashtags, you can use:
(group_concat(?hashtag;separator="|") as ?hashtags)
instead and get
--------------------------------------------------
| tweet | date | account | hashtags |
==================================================
| smo:tweet2 | "2" | "user2" | "tag4|tag3|tag2" |
| smo:tweet1 | "1" | "user1" | "tag3|tag2|tag1" |
--------------------------------------------------
If you're working in a language that has some literal array syntax, you might even be able to replicate that:
(concat('[',group_concat(?hashtag;separator=","),']') as ?hashtags)
----------------------------------------------------
| tweet | date | account | hashtags |
====================================================
| smo:tweet2 | "2" | "user2" | "[tag4,tag3,tag2]" |
| smo:tweet1 | "1" | "user1" | "[tag3,tag2,tag1]" |
----------------------------------------------------
Now, it doesn't affect the data here, but group_concat
will actually include duplicates in the concatenation if they're present in the data. E.g., from the following (where I'm just providing data with values
for the sake of the example):
prefix : <http://example.org/>
select ?tweet (concat('[',group_concat(?hashtag;separator=','),']') as ?hashtags)
where {
values (?tweet ?hashtag) {
(:tweet1 "tag1") (:tweet1 "tag1") (:tweet1 "tag2") (:tweet1 "tag3")
(:tweet2 "tag2") (:tweet2 "tag3") (:tweet2 "tag4")
}
}
group by ?tweet
we get results including [tag1,tag1,tag2,tag3]
, i.e., the duplicate value of ?hashtag
is included:
-------------------------------------
| tweet | hashtags |
=====================================
| :tweet2 | "[tag2,tag3,tag4]" |
| :tweet1 | "[tag1,tag1,tag2,tag3]" |
-------------------------------------
We can avoid this by using group_concat(distinct ?hashtag;...)
:
prefix : <http://example.org/>
select ?tweet (concat('[',group_concat(distinct ?hashtag;separator=','),']') as ?hashtags)
where {
values (?tweet ?hashtag) {
(:tweet1 "tag1") (:tweet1 "tag1") (:tweet1 "tag2") (:tweet1 "tag3")
(:tweet2 "tag2") (:tweet2 "tag3") (:tweet2 "tag4")
}
}
group by ?tweet
--------------------------------
| tweet | hashtags |
================================
| :tweet2 | "[tag2,tag3,tag4]" |
| :tweet1 | "[tag1,tag2,tag3]" |
--------------------------------