PostgreSQL Virtual Graph - "LIKE" query

Support
1

Hi,

I'm just looking into if it's possible for the Stardog virtual graph feature to generate and run PostgreSQL queries that use LIKE/ILIKE? By default it looks like it always uses direct equality.

Example of current behaviour:
Sparql Query:

SELECT * {
    ?a attr:Client:Name "smith" .
}

SMS2 Mapping:

MAPPING
FROM SQL {
    SELECT "id", "name"
    FROM "database"."public"."clients"
}
TO {
    ?subject rdf:type node:Client ;
        attr:Client:Id ?id ;
        attr:Client:Name ?name .
} WHERE {
    BIND(template("node:Client:{id}") AS ?subject)
}

Generated PostgreSQL query:

SELECT "id", "name"
FROM "database"."public"."clients"
WHERE "name" = 'smith' AND "id" IS NOT NULL
FETCH NEXT 1000 ROWS ONLY



What I would want
PostgreSQL query:

SELECT "id", "name"
FROM "database"."public"."clients"
WHERE "name" LIKE '%smith%' AND "id" IS NOT NULL
FETCH NEXT 1000 ROWS ONLY

Cheers, Tim.

2

Hi Tim,

sure, this is possible. You can use a filter to achieve this. In you example, you can do:

SELECT * {
    ?a attr:Client:Name ?name .
    FILTER(CONTAINS(?name, "smith"))
}

There are additional functions that you can use to filter strings which you can find in the SPARQL spec and also in our docs.

Best regards
Lars

P.S.: Note that not for all of those functions there is an equivalent rewriting in (all dialects of) SQL. In those cases, the filters have to be evaluated at the Stardog server after the results are obtained from the Virtual Graph which can come with some performance penalties.

1 Like
3

The SPARQL functions strstarts, strends, and contains will translate to the LIKE function.

PAUL

1 Like
4

Thanks Lars, that works :+1:

Is there an equivalent function for ILIKE (case insensitive)? I saw there is containsIgnoreCase, but it appears to be one of the cases that is evaluated on the Stardog server, not in the SQL.

Also, is there documentation of which functions have an equivalent in certain dialects of SQL? It would be helpful for optimising my queries.

Cheers, Tim.

5

Hi Tim,

for the case insensitive comparison you can use the LCASE function:

SELECT * {
    ?a attr:Client:Name ?name .
    FILTER(CONTAINS(LCASE(?name), "smith")))
}

At the moment there is no overview on which functions can be evaluated by which SQL dialect. An easy way check it is hitting "Show Plan" in Studio and inspect plan to see whether the filter was pushed into the VirtualGraphSql node.

Best regards
Lars