bitnine-oss / agensgraph

AgensGraph, a transactional graph database based on PostgreSQL
http://www.agensgraph.org
Other
1.33k stars 148 forks source link

jsonb datatype conversion (cast) support in cypher query #528

Open watery opened 3 years ago

watery commented 3 years ago

I just installed AgensGraph and I'm giving it a try. Consider that I'm pretty new to Postgres too.

I may need to add dates / timestamps as properties in my use case, and it seems from the documentation that they're not supported. But further in the page properties are described as being of type JSONB, and they shouldn't have types (that's not totally clear to me).

Anyway, given:

create (:person {name: 'Robert', date:'2021-03-01'});

This cypher query works, but makes me feel there's a lexicographical comparison, not a temporal one:

postgres=# match(n) where n.date > '2020-01-01' return n;
                          n
-----------------------------------------------------
 person[5.7]{"date": "2021-03-01", "name": "Robert"}
(1 row)

I've seen JSONB fields casted, so I tried the following one, but a syntax error is returned:

postgres=# match(n) where n.date::timestamp > '2020-01-01'::timestamp return n;
ERROR:  syntax error at or near "timestamp"
LINE 1: match(n) where n.date::timestamp > '2020-01-01'::timestamp r...

while this looks somewhat like the same query in SQL, where I can cast the value:

postgres=# select * from agens.person where (properties ->> 'date')::timestamp  > '2020-01-01'::timestamp;
 id  |                properties
-----+------------------------------------------
 5.7 | {"date": "2021-03-01", "name": "Robert"}
(1 row)

Are cast operators unsupported in AG Cypher? Or am I using the wrong syntax?

bioxakep commented 2 years ago

I also want to set the data types in the JSONB properties, specifying the type, and set up the correct search using indexes, taking into account the specified data types. Will there be support for a command like: Create (A:person {“bday”: “11/11/2010”::date,"happy": true::boolean, ...})?