Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to concatenate a list of values in sparql?

Suppose I have a uri http://dbpedia.org/page/Manmohan_Singh now he has a list of years in his tag dbpprop:years.

When I write a query like

PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
            PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
            PREFIX dbpedia: <http://dbpedia.org/resource/>PREFIX dcterms: <http://purl.org/dc/terms/>
            PREFIX dbpedia-owl: <http://dbpedia.org/ontology/>PREFIX category: <http://dbpedia.org/resource/Category:>
            PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>PREFIX foaf: <http://xmlns.com/foaf/0.1/>PREFIX dbpprop: <http://dbpedia.org/property/>
            PREFIX dbprop: <http://dbpedia.org/property/>PREFIX grs: <http://www.georss.org/georss/>
            PREFIX category: <http://dbpedia.org/resource/Category:>
            PREFIX owl: <http://www.w3.org/2002/07/owl#>
            PREFIX dbpprop: <http://dbpedia.org/property/>
            PREFIX foaf: <http://xmlns.com/foaf/0.1/>
            SELECT DISTINCT ?x ?name ?abs ?birthDate ?birthplace ?year ?party ?office ?wiki WHERE {
            ?x owl:sameAs? dbpedia:Manmohan_Singh.
            ?x dbpprop:name ?name.
            ?x dbpedia-owl:birthDate ?birthDate.
            ?x dbpedia-owl:birthPlace ?birthplace.
            ?x dbpprop:years ?year.
            ?x dbpprop:party ?party.
            ?x dbpedia-owl:office ?office.
            ?x foaf:isPrimaryTopicOf ?wiki.
            ?x rdfs:comment ?abs.
            FILTER(lang(?abs) = 'en')


            }

I get result of each year in different row .. and hence repeating data for other collumns. Is there a way I can get it as a list in just one collumn like all the years in one collumn comma separated or smthng like that?

Similary for the prop dbpedia-owl:office

like image 202
RamPrasadBismil Avatar asked Nov 27 '13 01:11

RamPrasadBismil


2 Answers

This is similar to Aggregating results from SPARQL query, but the problem is actually a bit more complex, because there are multiple variables that have more than one result. ?name, ?office, and ?birthPlace have the same issue.

You can work around this using group_concat, but you'll need to use distinct as well, to keep from getting, e.g., the same ?year repeated multiple times in your concatenated string. group by reduces the number of rows that you have in a solution, but in each of those rows, you have a set of values for the variables that you didn't group by. E.g., since ?year isn't in the group by, you have a set of values for ?year, and you have to do something with them. You could, e.g., select (sample(?year) as ?aYear) to grab just one from the set, or you could do as we've done here, and select (group_concat(distinct ?year;separator=", ") as ?years) to concatenate the distinct values into a string.

You'll want a query like the following, which produces one row:

SELECT ?x
       (group_concat(distinct ?name;separator="; ") as ?names)
       ?abs
       ?birthDate
       (group_concat(distinct ?birthplace;separator=", ") as ?birthPlaces)
       (group_concat(distinct ?year;separator=", ") as ?years)
       ?party
       (group_concat(distinct ?office;separator=", ") as ?offices)
       ?wiki
WHERE {
  ?x owl:sameAs? dbpedia:Manmohan_Singh.
  ?x dbpprop:name ?name.
  ?x dbpedia-owl:birthDate ?birthDate.
  ?x dbpedia-owl:birthPlace ?birthplace.
  ?x dbpprop:years ?year.
  ?x dbpprop:party ?party.
  ?x dbpedia-owl:office ?office.
  ?x foaf:isPrimaryTopicOf ?wiki.
  ?x rdfs:comment ?abs.
  FILTER(langMatches(lang(?abs),"en"))
}
group by ?x ?abs ?birthDate ?party ?wiki

SPARQL results

like image 177
Joshua Taylor Avatar answered Oct 16 '22 05:10

Joshua Taylor


Look at GROUP_CONCAT but it may be easier to retrieve the data in multiple rows (you can sort to put repeats adjacent to each other) and process in code.

like image 33
AndyS Avatar answered Oct 16 '22 04:10

AndyS