2 Replies Latest reply on Mar 5, 2019 11:16 PM by oskarvanrest-Oracle

    Use of WITH keyword in PGQL


      I see in PGQL documentation certain keywords are given but no where its functionality or syntax examples are given.


      PGQL 1.1 Specification - 21 December 2017 | PGQL · Property Graph Query Language


      Want to know if there is any other place where I can get information about these key words. I understand that most of the keywords are straight forward.


      I want to understand what is the purpose of WITH keyword and how can it be used

        • 1. Re: Use of WITH keyword in PGQL
          Gianni Ceresa


          The "WITH" isn't (AFAIK and for now) like in Oracle SQL to have separate blocks in the query (which can be written as subqueries in PGQL) but to me it sounds like a backward compatibility with PGQL 1.0.


          In 1.0 the WITH was used as a WHERE condition:

          PATH close_friend := () -[WITH weight >= 9]-> (:Person)
          SELECT m.name
          WHERE (n:Person) -/:close_friend*/-> (m)
             , n.name = 'Amber'


          The WITH is used to filter the edges based on an attribute of the edge (label or property), the same is valid for edges.

          In PGQL 1.1 you are supposed to use WHERE instead in the following equivalent query:

          PATH close_friend AS () -[e]-> (:Person) WHERE e.weight >= 9
          SELECT m.name
            FROM g MATCH (n:Person) -/:close_friend*/-> (m)
          WHERE n.name = 'Amber'


          There aren't many places with examples of PGQL so far as it's still a quite new thing and mainly (only?) used by Oracle PGX, therefore not much exists online. You find some examples in Oracle documents or some slides of presentations and blog posts which starts coming. But it's clearly most of the time based on the specification your linked.

          • 2. Re: Use of WITH keyword in PGQL

            Gianni explained it already. In PGQL 1.0 the WITH was used for in-lined filters but they were removed in PGQL 1.1.


            In PGQL 1.1, there is still a WITH keyword, but it is used only when casting a value to a TIME WITH TIME ZONE or a TIMESTAMP WITH TIME ZONE, e.g.:


            SELECT CAST('11:00:00+01:00' AS TIME WITH TIME ZONE) FROM ....