MongoDB/Virtual Graph Problem with SPARQL

[DennisThiery]

Hello all,
I have created a mapping for a Mongo DB to virtualise data which is split into messages and observations. The mapping seems to work, but I can't access the data properties of the observations via SPARQL. I only get to the individual and then I don't retrieve anything.

If I now search directly for the appropriate observation and want to retrieve the data property, it gives me the following result

The mapping code can be found below.

PREFIX rdf: http://www.w3.org/1999/02/22-rdf-syntax-ns#

PREFIX rdfs: http://www.w3.org/2000/01/rdf-schema#

PREFIX sosa: http://www.w3.org/ns/sosa/

PREFIX FTOnto: http://iot.uni-trier.de/FTOnto#

PREFIX StreamDataAnnotationOnto: http://iot.uni-trier.de/StreamDataAnnotationOnto#

MAPPING urn:Oven

FROM JSON {

"Oven":{

"_id":"?Oven",

"ID":"?O_ID",

"TIMESTAMP":"?Timestamp",

"I1_POSITION_SWITCH_PRESSED":"?I1_POSITION_SWITCH_PRESSED",

"I2_POSITION_SWITCH_PRESSED":"?I2_POSITION_SWITCH_PRESSED",

"I5_LIGHT_BARRIER_INTERRUPTED":"?I5_LIGHT_BARRIER_INTERRUPTED",

"M1_SPEED":"?M1_SPEED",

"O7_VALVE_OPEN":"?O7_VALVE_OPEN",

"O8_COMPRESSOR_POWER_LEVEL":"?O8_COMPRESSOR_POWER_LEVEL",

"CURRENT_STATE":"?CURRENT_STATE",

"CURRENT_TASK":"?CURRENT_TASK",

"CURRENT_TASK_ELAPSED_SECONDS_SINCE_START":"?CURRENT_TASK_ELAPSED_SECONDS_SINCE_START",

"CURRENT_SUB_TASK":"?CURRENT_SUB_TASK",

"BUSINESS_KEY":"?BUSINESS_KEY",

"PROCESS_DEFINITION_ID":"?PROCESS_DEFINITION_ID" 

}

}

TO {

?Message a FTOnto:Kafka_Message_OV1;

FTOnto:timestamp ?Timestamp ;

StreamDataAnnotationOnto:isPublishedToTopic StreamDataAnnotationOnto:Kafka_Topic_OV_1 ;

FTOnto:has_Information ?ID ;

FTOnto:has_Information ?I1 ;

FTOnto:has_Information ?I2 ;

FTOnto:has_Information ?LBO ;

FTOnto:has_Information ?M1 ;

FTOnto:has_Information ?O7 ;

FTOnto:has_Information ?O8 ;

FTOnto:has_Information ?CS ;

FTOnto:has_Information ?CT ;

FTOnto:has_Information ?CTESSS ;

FTOnto:has_Information ?CST ;

FTOnto:has_Information ?BK ;

FTOnto:has_Information ?PDI .

?ID a FTOnto:OV_1_ID;

FTOnto:timestamp ?Timestamp ;

sosa:hasSimpleResult ?O_ID ;

FTOnto:is_Information ?Message .

?I1 a FTOnto:Position_Switch_I1_Observation;

FTOnto:timestamp ?Timestamp; 

sosa:hasSimpleResult ?I1_POSITION_SWITCH_PRESSED ;

FTOnto:is_Information ?Message .

?I2 a FTOnto:Position_Switch_I2_Observation;

FTOnto:timestamp ?Timestamp; 

sosa:hasSimpleResult ?I2_POSITION_SWITCH_PRESSED ;

FTOnto:is_Information ?Message .

?LBO a FTOnto:Light_Barrier_5_Observation;

FTOnto:timestamp ?Timestamp; 

sosa:hasSimpleResult ?I5_LIGHT_BARRIER_INTERRUPTED ;

FTOnto:is_Information ?Message .

?M1 a FTOnto:Motor_Speed_M1_Observation;

FTOnto:timestamp ?Timestamp; 

sosa:hasSimpleResult ?M1_SPEED ;

FTOnto:is_Information ?Message .

?O7 a FTOnto:Valve_7_Observation;

FTOnto:timestamp ?Timestamp; 

sosa:hasSimpleResult ?O7_VALVE_OPEN ;

FTOnto:is_Information ?Message .

?O8 a FTOnto:Compressor_Power_Level_O8_Observation;

FTOnto:timestamp ?Timestamp; 

sosa:hasSimpleResult ?O8_COMPRESSOR_POWER_LEVEL ;

FTOnto:is_Information ?Message .

?CS a FTOnto:OV_1_CURRENT_STATE;

FTOnto:timestamp ?Timestamp; 

sosa:hasSimpleResult ?CURRENT_STATE ;

FTOnto:is_Information ?Message .

?CT a FTOnto:OV_1_CURRENT_TASK;

FTOnto:timestamp ?Timestamp; 

sosa:hasSimpleResult ?CURRENT_TASK ;

FTOnto:is_Information ?Message .

?CTESSS a FTOnto:OV_1_CURRENT_TASK_ELAPSED_SECONDS_SINCE_START;

FTOnto:timestamp ?Timestamp; 

sosa:hasSimpleResult ?CURRENT_TASK_ELAPSED_SECONDS_SINCE_START ;

FTOnto:is_Information ?Message .

?CST a FTOnto:OV_1_CURRENT_SUB_TASK;

FTOnto:timestamp ?Timestamp; 

sosa:hasSimpleResult ?CURRENT_SUB_TASK ;

FTOnto:is_Information ?Message .

?BK a FTOnto:OV_1_BUSINESS_KEY;

FTOnto:timestamp ?Timestamp; 

sosa:hasSimpleResult ?BUSINESS_KEY ;

FTOnto:is_Information ?Message .

?PDI a FTOnto:OV_1_PROCESS_DEFINITION_ID;

FTOnto:timestamp ?Timestamp; 

sosa:hasSimpleResult ?PROCESS_DEFINITION_ID ;

FTOnto:is_Information ?Message .

}

WHERE {

BIND (template("mongodb://localhost:27017/Oven_Message_{Oven}") AS ?Message)

BIND (template("mongodb://localhost:27017/Oven_ID_{Oven}") AS ?ID)

BIND (template("mongodb://localhost:27017/Oven_I1_{Oven}") AS ?I1)

BIND (template("mongodb://localhost:27017/Oven_I2_{Oven}") AS ?I2)

BIND (template("mongodb://localhost:27017/Oven_LBO_{Oven}") AS ?LBO)

BIND (template("mongodb://localhost:27017/Oven_M1_{Oven}") AS ?M1)

BIND (template("mongodb://localhost:27017/Oven_O7_{Oven}") AS ?O7)

BIND (template("mongodb://localhost:27017/Oven_O8_{Oven}") AS ?O8)

BIND (template("mongodb://localhost:27017/Oven_CS_{Oven}") AS ?CS)

BIND (template("mongodb://localhost:27017/Oven_CT_{Oven}") AS ?CT)

BIND (template("mongodb://localhost:27017/Oven_CTESSS_{Oven}") AS ?CTESSS)

BIND (template("mongodb://localhost:27017/Oven_CST_{Oven}") AS ?CST)

BIND (template("mongodb://localhost:27017/Oven_BK_{Oven}") AS ?BK)

BIND (template("mongodb://localhost:27017/Oven_PDI_{Oven}") AS ?PDI)

}

does anyone know why this "triple jump" does not work ?

Thanks in advance for the help

Greetings
Dennis

[PaulJackson]

Hi Dennis,

Can you share a text version of the query that's failing and its query plan in text format?

I speculating but it might have to do with the data types of the MondoDB fields you're joining on.

See Specific Data Source Considerations | Stardog Documentation Latest

-Paul

[DennisThiery]

Hi Paul,
Thanks for the help,
Attached is the query plan and the query

Untitled-7_sparql_explanation.txt (61.4 KB)

The Query is

PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
PREFIX sosa: <http://www.w3.org/ns/sosa/>
PREFIX FTOnto: <http://iot.uni-trier.de/FTOnto#>
PREFIX StreamDataAnnotationOnto: <http://iot.uni-trier.de/StreamDataAnnotationOnto#>
Prefix : <http://api.stardog.com/> 

SELECT Distinct ?I ?S ?P ?O ?Data {
    GRAPH <virtual://Oven> {
?I a FTOnto:Kafka_Message_OV1 .
?I FTOnto:timestamp '2021-11-08 14:12:10.44' .
?I FTOnto:has_Information ?S .
?S ?P ?O .
}

It would be great if the problem was this

Greetings
Dennis

[PaulJackson]

Hi Dennis,

What is the data type of the _id field in the MongoDB collection? By default MongoDB uses ObjectID but that data type is not indicated in your FROM JSON clause (Stardog defaults ALL MongoDB fields to string):

FROM JSON {
    "Oven":{
        "_id":"?Oven",
        "ID":"?O_ID",
        "TIMESTAMP":"?Timestamp",
...

If it is ObjectId you'll want to change that to:

FROM JSON {
    "Oven":{
        "_id":"?Oven:objectId",
        "ID":"?O_ID",
        "TIMESTAMP":"?Timestamp",
...

If I'm wrong about that or if this change doesn't fix the problem, could you try the same query without the ?s ?p ?o and resend the plan? Something like:

SELECT DISTINCT ?I ?S {
    GRAPH <virtual://Oven> {
        ?I a FTOnto:Kafka_Message_OV1 .
        ?I FTOnto:timestamp '2021-11-08 14:12:10.44' .
        ?I FTOnto:has_Information ?S .
    }
}

... or whatever is the simplest query with the simplest plan that still exhibits the problem.

Thanks,
-Paul

[DennisThiery]

Hello Paul,
I think we are on the right track.
The _id is created as ObjectId, when I want to change it, as in your exampleI, I get always an error message

I have tried to bind the variable, but it doesn't really work. I tried to copy the example in your tutorial, but it always complains that only 2 brackets open but 3 brackets close.

I then tried to work with the O_ID and created an individual here, as binding did not work either.
Now, when I run the Sparql, I get the individual back.

The question I have now is what am I doing wrong with the binding?

Greetings and thanks
Dennis

[PaulJackson]

Sorry about the error in the doc. It should be:

BIND (strDt(?size, xsd:double) AS ?doubleSize)

I'm fixing that.

Could you share a text version of the mappings that are causing the Unbound variable Oven error? And also the mappings that are returning the individual in the ?O column.

Thanks,
-Paul

[DennisThiery]

Hello Paul
Thank you very much for the effort,

Attached is the mapping, I have put a comment in line 11 and 136 where I have changed something or where the objectId should go.

The last bind is only for mapping the O?
Mapping FTOnto Stardog.txt (5.0 KB)

Greetings
Dennis

[PaulJackson]

The objectId should work. Support for adding this type was added in version 7.8.0 of Stardog. Are you using an older version?

To understand why you are getting back that highlighted value for ?O, consider this portion of your mapping:

FROM JSON {
  "Oven":{
    "_id":"?Oven:objectId",
    "ID":"?O_ID",
  }
}
TO {
  ?Message FTOnto:has_Information ?ID .
  ?ID sosa:hasSimpleResult ?O_ID .
}
WHERE {
  BIND(template("mongodb://localhost:27017/Oven_Message_{Oven}") AS ?Message)
  BIND(template("mongodb://localhost:27017/Oven_ID_{Oven}") AS ?ID)
  BIND(template("mongodb://localhost:27017/Oven_{Oven}") AS ?O_ID)
}

And this portion of your query:

?I FTOnto:has_Information ?S .
?S ?P ?O

The solution with the highlighted result matched ?I to ?Message, ?S to ?ID, ?P to sosa:hasSimpleResult, and ?O to ?O_ID. (Notice that ?I and ?O are NOT the same IRI - ?I has Oven_Message in the IRI and ?O has only Oven.

-Paul

[DennisThiery]

Hello Paul
thanks for the explanation,
I have now re-installed Stardog with version 7.8.3 and now everything works.

Thanks for the support. Really great of you guys what you are doing

Greetings and thanks
Dennis